Copy columns from one sheet to another workbook

Question

I have a workbook with multiple sheets in. I have a sheet called "MergedData", I need to copy columns B & L into a workbook called "Bookings.xls"

Column B is a Word Order number and L is a £value, I need it to copy over values that are over £0.01 and exclude everything with a nil value.

The data will need to be placed into Rows of columns A & B in the Bookings workbook, starting at row 9.

The data will need to erase every month when the code is run.

I will be applying this code to multiple workbook for different Contractors. So the code cannot Hard-Lock to the original file. Needs to be the Active Workbook, the Bookings.xls file is a static workbook in all contractor folders.

Thanks for all the support.

Private Sub CommandButton1_Click() Dim LastDRow As Integer, _ InitWorkSheet As Worksheet, _ DestWorkSheet As Worksheet, _ myData As Workbook, _ LastWRow As Integer Set InitWorkSheet = ActiveWorkbook.Sheets("MergedData") 'Or ActiveWorkbook.Sheets("Sheet1") Set myData = Workbooks.Open(ActiveWorkbook.Path & "\Bookings.xls") DoEvents Set DestWorkSheet = myData.Sheets("Sheet1") 'Or myData.Sheets("Sheet1") With InitWorkSheet LastDRow = .Rows(.Rows.Count).End(xlUp).Row MsgBox LastDRow For i = LastDRow To 1 Step -1 If .Cells(i, "L") < 0 Then Else LastWRow = DestWorkSheet.Cells(DestWorkSheet.Rows.Count, "A").End(xlUp).Row If LastWRow < 9 Then LastWRow = 9 DestWorkSheet.Cells(LastWRow, 1) = .Cells(i, "B") DestWorkSheet.Cells(LastWRow, 2) = .Cells(i, "L") End If Next i End With myData.Save End Sub`

@USFBS Hi, i have update OP to include what i have now. thanks — atame
– atame, Commented May 26, 2015 at 16:11

R3uK · Accepted Answer · 2015-06-02 10:54:38Z

This this and let me know :

Private Sub CommandButton1_Click() Dim LastDRow As Integer, _ InitWorkSheet As Worksheet, _ DestWorkSheet As Worksheet, _ myData As Workbook, _ LastWRow As Integer Set InitWorkSheet = ActiveWorkbook.Sheets("MergedData") Set myData = Workbooks.Open(ActiveWorkbook.Path & "\Bookings.xls") Set DestWorkSheet = myData.Sheets("Sheet1") DestWorkSheet.Cells.ClearContents With InitWorkSheet LastDRow = .Rows(.Rows.Count).End(xlUp).Row For i = 1 To LastDRow 'To 1 Step -1 If .Cells(i, "L") < 0.01 Then Else LastWRow = DestWorkSheet.Cells(DestWorkSheet.Rows.Count, "A").End(xlUp).Row + 1 If LastWRow < 9 Then LastWRow = 9 DestWorkSheet.Cells(LastWRow, 1) = .Cells(i, "B") DestWorkSheet.Cells(LastWRow, 2) = .Cells(i, "L") End If Next i End With myData.Save End Sub

i get a Run-time error '1004': application-defined or object-defined error on 'For i = .Rows(Rows.Count).End(xlUp).Row To 2 Step -1' thanks
I don't know id my original code is correct, i have a feeling that it is not.
Hi mate, still gives me the same error on LastWRow = myData.ActiveSheet.Rows(Rows.Count).End(xlUp).Row
Ok so what is the name of the Sheet you want to copy data to?
hi it needs to go into sheet 1, starting at row 9 in columns A & B

Collectives™ on Stack Overflow

Copy columns from one sheet to another workbook

1 Answer 1

16 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

16 Comments

Related