How Do I Copy Only Select Data?

[Guest]

I'm looking for a macro that will copy data from a "master" sheet to
individual sheets based on the sheet name. What I have is a master sheet
containing 4 columns of data (one of which is a group id) that I manually
sort by group and then manually copy that groups information into their own
sheet. There are approximately 20 sheets (groups). What I need is a macro
that will look at the sheet name (which is the group id) and then go back to
the master sheet and pull all the rows of data that match that sheet name.
Oh, I do have some sheets in the workbook that would be excluded from this.

I know nothing about writing macros so any help will be greatly appreciated!

Thanks!

Randy

 

[Tom Ogilvy]

http://www.rondebruin.nl/copy5.htm

 

[Fred Smith]

You start learning macros by turning on the macro recorder. Then do your copy.
Stop the macro recorder. Now take a look at your code.

No one is going to write the full macro for you, unless you want to hire them.
However, as you flesh our your macro, posting specific questions will get you
quick, accurate answers.

 

[Tim Williams]

Try this. One proviso - your sheets are named exactly as the group id's,
and you may run into problems if these are numbers. For example

Thisworkbook.worksheets("5")

seems to also try

Thisworkbook.worksheets(5)

ie. if there is no sheet named "5" it will instead return the fifth sheet
(if there is one).

Tim


'********************
Option Explicit

Sub Tester()

Const ID_COL As Integer = 2 'col with group id
Dim r As Range
Dim s As Worksheet
Dim v As String

For Each r In ThisWorkbook.Sheets("Master").Range("A2500").Rows

v = r.Cells(ID_COL).Value
If v <> "" Then
On Error Resume Next
Set s = ThisWorkbook.Worksheets(v)
On Error GoTo 0

If Not s Is Nothing Then
r.Copy s.Cells(s.Rows.Count, 1).End(xlUp).Offset(1, 0)
r.Interior.Color = vbGreen
Else
r.Interior.Color = vbRed
End If

End If

Next r

End Sub

 

[Guest]

Tim,

I really appreciate your help on this (unlike the person who told me to
figure it out myself). Most of what I do is very time critical and you just
saved me a lot of time!

There seems to be one minor glitch that I cannot figure out. If I have a
Group ID in the master sheet that does not have it's own corresponding sheet,
then the macro copies that data into the current sheet it's working on. Not
all of the IDs will have a sheet and that's by design. Seems like it's not
going to the error handling or something. Any ideas how to correct this?

Thanks again!

Randy

 

[Tim Williams]

Small addition: set s to Nothing before trying to match a sheet.

Tim.

'**************************
Sub Tester()

Const ID_COL As Integer = 2 'col with group id
Dim r As Range
Dim s As Worksheet
Dim v As String

For Each r In ThisWorkbook.Sheets("Master").Range("A2500").Rows

v = r.Cells(ID_COL).Value
If v <> "" Then

Set s = Nothing 'ensure s is nothing if no sheet match
On Error Resume Next
Set s = ThisWorkbook.Worksheets(v)
On Error GoTo 0

If Not s Is Nothing Then
Debug.Print v & " : " & s.Name
r.Copy s.Cells(s.Rows.Count, 1).End(xlUp).Offset(1, 0)
r.Interior.Color = vbGreen
Else
r.Interior.Color = vbRed
End If

End If

Next r

End Sub

 

[Guest]

Tim,

Works perfectly now. Thanks!

Randy

PS - do you have any tips on resources for learning VBA?

 

[Tim Williams]

Randy,

I prefer books, and "Excel Power Programming" by John Walkenbach got me off to a good start.
Many others since then...

Right here is also where I learn a lot.