VBA to copy and paste multiple rows

[ssh99]

Hello. I'm a novice at VBA and need to update a macro to copy and paste multiple rows of data to individual workbooks.

What I need the macro to do
I have a list of excel workbooks already saved in a folder. The file name of each workbook is in column A of the spreadsheet I am using.
I need the macro to copy all rows of data from columns B, C and D and paste it in the workbook corresponding to the same file name in column A. This data then needs to be pasted in the workbook of the relevant file.

The VBA code I have written so far
The code at the moment only finds the first row of data I need copied to each workbook and pastes it in the destination. I need it to also finds the other rows and paste that as well. The code so far is:

Here is the code:

Sub WLImportReal()

Dim previousAlertsFlag As Boolean

Dim masterWB As Workbook

Dim masterWS As Worksheet

Dim destWB As Workbook

Dim destWS As Worksheet

Dim lastRow As Long

Dim filepath As String

Dim fullpath As String

Dim r As Integer


Set masterWB = ThisWorkbook

Set masterWS = masterWB.Worksheets("Sheet1")

lastRow = masterWS.Cells(masterWS.Rows.Count, "A").End(xlUp).Row

filepath = "G:\Shared\Automated emails\Missing waiting list specialties\Pending emails\"


For r = 2 To lastRow

User = masterWS.Cells(r, 1).Value

fullpath = filepath & User & " - WL WITH MISSING SPECIALTY.xlsx"

Set destWB = Workbooks.Open(fullpath)

Set destWS = destWB.Sheets("Sheet1")

masterWS.Cells(r, 2).Resize(, 3).Copy destWS.Cells(2, 1)

destWB.Close SaveChanges:=True


Next r


End Sub

I have also attached a screenshot of the spreadsheet the data is being copied from.

Thanks for your help.

 

[ssh99]

Still struggling to figure this one out. Would greatly appreciate any help!

 

[ssh99]

Bumping this up.