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

A	B	C	D	E	F	G
1111	2222	3333	4444	5555	6666	7777
fd	sdfd	dacd	gdd	sfafsd	hgfgh	xdvs
qe	zccz	gdfv	xbbdbf	vdsv	sdvsd	adsa
Not req	Not req	Not req	Not req	Not req	Not req	Not req

<tbody>
</tbody>

Target File

a	a1	a2	b	c	c1	d	e	f	g
existing data	existing data	existing data	existing data	existing data	existing data	existing data	existing data	existing data	existing data
existing data	existing data	existing data	existing data	existing data	existing data	existing data	existing data	existing data	existing data
existing data	existing data	existing data	existing data	existing data	existing data	existing data	existing data	existing data	existing data
1111			2222	3333		4444	5555	6666	7777
fd			sdfd	dacd		gdd	sfafsd	hgfgh	xdvs
qe			zccz	gdfv		xbbdbf	vdsv	sdvsd	adsa

<tbody>
</tbody>

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.