Run time error 1004 Applicatin-defined or object-defined error (Copy from src to dest

[ramcp14]

Hi Guys,
I am new to Excel VBA. I have got a situation where i need your help.
I have a source file and a destination file. Basically what i have to do is copy some cols from source file to destination file.
Say for example if i have 10 cols in Source file and 20 cols in destination file. I have to copy those 10 cols and paste
them across the destination file where the cols match. the cols are in fixed order so i do not want to search a string to find the corresponding col in the
destination file as i know they are fixed. i want to just match the cols. say A to B or C to C etc..
For example if there was to be a col in source file called Cust_no in col C, I know Cust_no is destination file is Col A. So i have to copy the col c from source and find the last line of col A in destination and paste it. I want to do this for all 10 cols
But basic criteria is i want to check the last row of the Col A in source file and store it in a dummy variable and then use that as a reference to copy
the rest of cols in source file as some of the cols might have blank values (so cant use End(xldown)) and Col A does not have a blank value.
Then go into destination file find the last available line of Col A as they are always populated and cannot be blank and store it in a dummy variable and
then use that reference for copying the rest of the cols.

For example
My first sheet has 10 cols. Last line of the first col A is 15. So i store it in a dummy variable and use 15 as a reference and copy that 15 lines across all cols in source.
Then go to destination find the last line of the first col A. Lets say 30. Then paste each col from source to destination on 30th line for all the cols in destination file.


I have written a a VB script for doing that. But it may not be very efficient one as i am a newbiew. but i am getting an run time error 1004.
Can you guys help me to rectify it and also suggest me is there a better way to do the above proccess?

My code :
Sub GenerateReport3()
Application.ScreenUpdating = False

Dim LastRow_s As Long
Dim LastRow_t As Long

Dim sBook_t As String
Dim sBook_s As String

Dim sSheet_t As String
Dim sSheet_s As String


Dim sRange_s As String
Dim sRange_t As String


sBook_t = "test1.xls"
sBook_s = "test.xls"

sSheet_t = "Sheet1"
sSheet_s = "Sheet1"

'With ActiveSheet
'LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
'End With

LastRow_t = Workbooks(sBook_t).Sheets(sSheet_t).Cells(Rows.Count, "A").End(xlUp).Row
LastRow_s = Workbooks(sBook_s).Sheets(sSheet_s).Cells(Rows.Count, "A").End(xlUp).Row


'Column match for "A"

Workbooks(sBook_s).Activate
Sheets(sSheet_s).Select
sRange_s = "A2" & ":" & "A" & LastRow_s
Range(sRange_s).Select
'Range(Selection, Selection.End(xlDown)).Select
'Range(A2:sRange_s).Offset(1, 0).Select
Selection.Copy
Workbooks(sBook_t).Activate
Sheets(sSheet_t).Select
sRange_t = "A" & LastRow_t

Range(sRange_t).Offset(1, 0).Select
'Range(sRange_t).Select
Selection.PasteSpecial Paste:=xlPasteValues

ActiveWorkbook.SaveAs "c:\Opensource\test1.xls"

'Workbooks("test.xls").Activate
'Sheets("sheet1").Select
'Range("A1").Select

'Column match for "B"

Workbooks(sBook_s).Activate
Sheets(sSheet_s).Select
sRange_s = "B2" & ":" & "B" & LastRow_s
Range(sRange_s).Select
'Range(Selection, Selection.End(xlDown)).Select
'Range(B2:sRange_s).Offset(1, 0).Select
Selection.Copy
Workbooks(sBook_t).Activate
Sheets(sSheet_t).Select
sRange_t = "B" & LastRow_t

Range(sRange_t).Offset(1, 0).Select
'Range(sRange_t).Select
Selection.PasteSpecial Paste:=xlPasteValues

ActiveWorkbook.SaveAs "c:\Opensource\test1.xls"

'Workbooks("test.xls").Activate
'Sheets("sheet1").Select
'Range("B1").Select

etc.... (for all the cols you wanna copy)


'Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub


Please help me guys.

[Teeroy]

Hi ramcp14,

Welcome to the forum. To get the error you got one of the files you are referencing was not open. With both files open your code works, which is a very good place to start. I would guess that you've built this around a Macro recording which is fine to get the basics but includes a lot of irrelevant code. The following is a tidy up of your starting code and introduces a few more concepts (workbook and range objects) that make excel VBA more powerful.

Sub GenerateReport4()
Application.ScreenUpdating = False

Dim LastRow_s As Long
Dim rLastRow_t As Range

Dim sBook_t As Workbook
Dim sBook_s As Workbook

Dim sSheet_t As String
Dim sSheet_s As String

Set sBook_t = Workbooks("test1.xls")
Set sBook_s = Workbooks("test.xls")

sSheet_t = "Sheet1"
sSheet_s = "Sheet1"

Set rLastRow_t = sBook_t.Sheets(sSheet_t).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
LastRow_s = sBook_s.Sheets(sSheet_s).Cells(Rows.Count, "A").End(xlUp).Row

'Column match for "A"

sBook_t.Activate
sBook_s.Sheets(sSheet_s).Range("A2:A" & LastRow_s).Copy
rLastRow_t.PasteSpecial Paste:=xlPasteValues

'Column match for "B"

sBook_s.Sheets(sSheet_s).Range("B2:B" & LastRow_s).Copy
rLastRow_t.Offset(0, 1).PasteSpecial Paste:=xlPasteValues

' save workbook after all processing
ActiveWorkbook.SaveAs "c:\Opensource\test1.xls"

'Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Please use the VBA tags to enclose your code as it makes it much more readable.

[ramcp14]

thank you for your promt response. I'll try and let you know :)