Macro to copy specific columns from one workbook to another workbook

[whoosh]

Hi,

Hope someone can help me out on this. I'm trying to copy several columns from 1 workbook (Source File.xls - "sheet1") to another workbook (Target File.xlsm - worksheet ("final").

I need to run a macro to copy specific columns (A, B, C, F, G) from "Source" (but omitting the last 1 row "Not req") to (a, b, c, f, g) on "Target" without overwriting the existing data.

Source File
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1111[/TD]
[TD]2222[/TD]
[TD]3333[/TD]
[TD]4444[/TD]
[TD]5555[/TD]
[TD]6666[/TD]
[TD]7777[/TD]
[/TR]
[TR]
[TD]fd[/TD]
[TD]sdfd[/TD]
[TD]dacd[/TD]
[TD]gdd[/TD]
[TD]sfafsd[/TD]
[TD]hgfgh[/TD]
[TD]xdvs[/TD]
[/TR]
[TR]
[TD]qe[/TD]
[TD]zccz[/TD]
[TD]gdfv[/TD]
[TD]xbbdbf[/TD]
[TD]vdsv[/TD]
[TD]sdvsd[/TD]
[TD]adsa[/TD]
[/TR]
[TR]
[TD]Not req[/TD]
[TD]Not req[/TD]
[TD]Not req[/TD]
[TD]Not req[/TD]
[TD]Not req[/TD]
[TD]Not req[/TD]
[TD]Not req[/TD]
[/TR]
</tbody>[/TABLE]

Target File
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]a[/TD]
[TD]a1[/TD]
[TD]a2[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]c1[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[/TR]
[TR]
[TD]existing data[/TD]
[TD]existing data[/TD]
[TD]existing data[/TD]
[TD]existing data[/TD]
[TD]existing data[/TD]
[TD]existing data[/TD]
[TD]existing data[/TD]
[TD]existing data[/TD]
[TD]existing data[/TD]
[TD]existing data[/TD]
[/TR]
[TR]
[TD]existing data[/TD]
[TD]existing data[/TD]
[TD]existing data[/TD]
[TD]existing data[/TD]
[TD]existing data[/TD]
[TD]existing data[/TD]
[TD]existing data[/TD]
[TD]existing data[/TD]
[TD]existing data[/TD]
[TD]existing data[/TD]
[/TR]
[TR]
[TD]existing data[/TD]
[TD]existing data[/TD]
[TD]existing data[/TD]
[TD]existing data[/TD]
[TD]existing data[/TD]
[TD]existing data[/TD]
[TD]existing data[/TD]
[TD]existing data[/TD]
[TD]existing data[/TD]
[TD]existing data[/TD]
[/TR]
[TR]
[TD]1111[/TD]
[TD][/TD]
[TD][/TD]
[TD]2222[/TD]
[TD]3333[/TD]
[TD][/TD]
[TD]4444[/TD]
[TD]5555[/TD]
[TD]6666[/TD]
[TD]7777[/TD]
[/TR]
[TR]
[TD]fd[/TD]
[TD][/TD]
[TD][/TD]
[TD]sdfd[/TD]
[TD]dacd[/TD]
[TD][/TD]
[TD]gdd[/TD]
[TD]sfafsd[/TD]
[TD]hgfgh[/TD]
[TD]xdvs[/TD]
[/TR]
[TR]
[TD]qe[/TD]
[TD][/TD]
[TD][/TD]
[TD]zccz[/TD]
[TD]gdfv[/TD]
[TD][/TD]
[TD]xbbdbf[/TD]
[TD]vdsv[/TD]
[TD]sdvsd[/TD]
[TD]adsa[/TD]
[/TR]
</tbody>[/TABLE]

I have tried some codes but it doesn't deliver what I want. My apology, don't know how to attach files.

Sub CopySource()


    Dim SourceWB As Workbook: Set SourceWB = Workbooks("Source File.xlsx")
    Dim TargetWB As Workbook: Set TargetWB = Workbooks("Target File.xlsm")
    Dim lr As Long: lr = SourceWB.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    Dim lastr As Long: lastr = TargetWB.Sheets("Final").Cells(Rows.Count, "A").End(xlUp).Row
    
    SourceWB.Sheets("Sheet1").Range("A2:H5000").Copy Destination:=TargetWB.Sheets("Final").Range("A" & lr)


End Sub

Thanks in advance

 

[whoosh]

That is what I meant. Ok. 241 rows. Not sure why the error occurred.

It WORKS! But not sure what was the reason LOL.
Could it be that the last 2 rows were merged???

Anyway. Thank you for your patience and help!

 

[whoosh]

the code could be modified to
either
- allow user to navigate to correct file
or
- let VBA determine the folder based on week number provided by user
(add input box or get from cell in worksheet, requires "rule" to generate folder path)
or
- use your present method with both files open at the start

Thanks Yongle.

Still learning.. and consider a noob when coding is concern.
How I wish i can code like you guys.