Hello. I'm a novice at VBA and am struggling to get a macro set up. Any help would be greatly appreciated.
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 1 row 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
It seems to do what I need it to apart from pasting the row of data in the different workbooks. I suspect there is a problem with the 'WaitingListData' part of the code.
Here is the code:
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 1 row 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
It seems to do what I need it to apart from pasting the row of data in the different workbooks. I suspect there is a problem with the 'WaitingListData' part of the code.
Here is the code:
VBA Code:
Sub Macro1()
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 country As String
Dim countryData As Variant
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\"
For r = 2 To lastRow
User = masterWS.Cells(r, 1).Value
WaitingListData = masterWS.Cells(r, 2)(r, 3)(r, 4).Value
Selection.Copy
fullpath = filepath & User & ".xlsx"
Set destWB = Workbooks.Open(fullpath)
Set destWS = destWB.Sheets("Sheet1")
destWS.Cells(1, 2).Value = WaitingListData
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.