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

[ClaireJL]

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!

 

[Juanca1080]

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.

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.

 

[ClaireJL]

It is giving me the error "Subscript out of range" and when I click debug it highlights the 9th line of the code..

 

[ClaireJL]

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!

 

[Juanca1080]

Glad to help.
Thank you for the feedback.