VBA to copy and paste multiple rows

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

Attachments

  • image001.png
    image001.png
    15 KB · Views: 6

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

ssh99

New Member
Joined
Oct 25, 2020
Messages
9
Office Version
  1. 2010
Platform
  1. Windows
  2. MacOS
Still struggling to figure this one out. Would greatly appreciate any help!
 

Watch MrExcel Video

Forum statistics

Threads
1,126,939
Messages
5,621,722
Members
415,853
Latest member
Newlife72

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