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.