VBA Copy and pasta row based on cell value

ssh99

New Member
Joined
Oct 25, 2020
Messages
9
Office Version
  1. 2010
Platform
  1. Windows
  2. MacOS
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:

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.
 

Attachments

  • image001.png
    image001.png
    8.4 KB · Views: 5

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Replace your For r loop with this"

VBA Code:
For r = 2 To LastRow
    User = masterWS.Cells(r, 1).Value
    fullpath = filepath & User & ".xlsx"
    Set destWB = Workbooks.Open(fullpath)
    Set destWS = destWB.Sheets("Sheet1")
    masterWS.Cells(r, 2).Resize(, 3).Copy destWS.Cells(1, 2)
    destWB.Close SaveChanges:=True
Next r
 
Solution

ssh99

New Member
Joined
Oct 25, 2020
Messages
9
Office Version
  1. 2010
Platform
  1. Windows
  2. MacOS
Replace your For r loop with this"

VBA Code:
For r = 2 To LastRow
    User = masterWS.Cells(r, 1).Value
    fullpath = filepath & User & ".xlsx"
    Set destWB = Workbooks.Open(fullpath)
    Set destWS = destWB.Sheets("Sheet1")
    masterWS.Cells(r, 2).Resize(, 3).Copy destWS.Cells(1, 2)
    destWB.Close SaveChanges:=True
Next r
Thank you, that's amazing! It's working perfectly now.

Do you know what I'd need to add if I needed to copy multiple rows of data for each spreadsheet. For example, if 'John' (taken from column 1) had data randomly placed in rows (say rows 3, 5 and 10 for example), how would I copy all the rows to the spreadsheet called 'John'? I'm guessing I'd need to add an auto filter code somewhere but have no clue how to do this.
 

Attachments

  • image001.png
    image001.png
    8.4 KB · Views: 0

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
You could loop to find "John" in column A and copy on each iteration, or more sophisticated maybe filter on "John" and use the SpecialCells(xlCellTypeVisible) to capture the data to copy. But that is beyond the scope of this thread and you would need to start a new thread for that issue.
 

ssh99

New Member
Joined
Oct 25, 2020
Messages
9
Office Version
  1. 2010
Platform
  1. Windows
  2. MacOS
You could loop to find "John" in column A and copy on each iteration, or more sophisticated maybe filter on "John" and use the SpecialCells(xlCellTypeVisible) to capture the data to copy. But that is beyond the scope of this thread and you would need to start a new thread for that issue.
Thanks, you've been a big help.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,620
Messages
5,625,907
Members
416,141
Latest member
Bartek9q

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top