Macro Help - Using dynamic IF statement to copy rows from master worksheet

ClaireJL

New Member
Joined
May 17, 2015
Messages
20
Hi All,

I'm still fairly new to Macros and I have tried searching for the answer to my question but haven't been able to find anything that does quite what I want..

I have a workbook with multiple worksheets - "Instructions" and "Master" followed by a number of other worksheets.

I am trying to create a macro that will scan through Column A of the "Master" sheet to find cells containing the name of all other worksheet and then copy and paste that entire row onto the other worksheet starting in row 6 (after my headers). For example in the table below I would like the Macro to match cells A2 and A3 with the worksheet named "Law" and copy the whole of both rows to that worksheet, then do the same with row 4 to the "Documentation" worksheet and so on...

A
B
C
D
E
Law
xx
xx
xx
xx
Law
xx
xx
xx
xx
Documentation
xx
xx
xx
xx

<tbody>
</tbody>

Any help or guidance is much appreciated!

Thanks!
 

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.

Juanca1080

New Member
Joined
May 28, 2015
Messages
17
You could try this
Please note that this procedure assumes data to process in master worksheet starts at A2, if not, just change the seventh line of the code to the cell from which it should start copying.

Code:
Option Explicit
Sub CopyRows()
Application.ScreenUpdating = False
Dim CurrCell As Range
Dim ws As Worksheet
Set ws = Sheets("Master")
Set CurrCell = ws.Range("A2")
Do While CurrCell <> vbNullString
CurrCell.EntireRow.Copy Destination:=Sheets(CurrCell.Value).Range("A" & Sheets(CurrCell.Value).Range("A65636").End(xlUp).Row + 1)
Set CurrCell = CurrCell.Offset(1, 0)
Loop
Application.ScreenUpdating = True
End Sub

Hope it helps.
 
Last edited:

ClaireJL

New Member
Joined
May 17, 2015
Messages
20
It is giving me the error "Subscript out of range" and when I click debug it highlights the 9th line of the code..
 

ClaireJL

New Member
Joined
May 17, 2015
Messages
20
Wait - It worked!!!!!!! I think the error was that I was trying to run the macro when I wasn't on the Master Sheet!

Thank you so so so much!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,489
Messages
5,596,455
Members
414,069
Latest member
StudExcel

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