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.
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.