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

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

--
Regards,
Tom Ogilvy

"Randy" <(E-Mail Removed)> wrote in message
news:F9E65A4C-25BB-4AB5-9C70-(E-Mail Removed)...
> 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

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.

--
Regards,
Fred


"Randy" <(E-Mail Removed)> wrote in message
news:F9E65A4C-25BB-4AB5-9C70-(E-Mail Removed)...
> 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

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





"Randy" <(E-Mail Removed)> wrote in message
news:F9E65A4C-25BB-4AB5-9C70-(E-Mail Removed)...
> 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

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" wrote:

> 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
>
>
>
>
>
> "Randy" <(E-Mail Removed)> wrote in message
> news:F9E65A4C-25BB-4AB5-9C70-(E-Mail Removed)...
> > 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
>
>
>

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



"Randy" <(E-Mail Removed)> wrote in message
news:6B1DA0F5-5B4D-402D-B4A5-(E-Mail Removed)...
> 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" wrote:
>
>> 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
>>
>>
>>
>>
>>
>> "Randy" <(E-Mail Removed)> wrote in message
>> news:F9E65A4C-25BB-4AB5-9C70-(E-Mail Removed)...
>> > 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
>>
>>
>>

Tim,

Works perfectly now. Thanks!

Randy

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

"Tim Williams" wrote:

> 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
>
>
>
> "Randy" <(E-Mail Removed)> wrote in message
> news:6B1DA0F5-5B4D-402D-B4A5-(E-Mail Removed)...
> > 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" wrote:
> >
> >> 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
> >>
> >>
> >>
> >>
> >>
> >> "Randy" <(E-Mail Removed)> wrote in message
> >> news:F9E65A4C-25BB-4AB5-9C70-(E-Mail Removed)...
> >> > 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
> >>
> >>
> >>
>
>
>

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.

--
Tim Williams
Palo Alto, CA


"Randy" <(E-Mail Removed)> wrote in message news:FD900440-B74F-4137-BB3A-(E-Mail Removed)...
> Tim,
>
> Works perfectly now. Thanks!
>
> Randy
>
> PS - do you have any tips on resources for learning VBA?
>
> "Tim Williams" wrote:
>
> > 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
> >
> >
> >
> > "Randy" <(E-Mail Removed)> wrote in message
> > news:6B1DA0F5-5B4D-402D-B4A5-(E-Mail Removed)...
> > > 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" wrote:
> > >
> > >> 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
> > >>
> > >>
> > >>
> > >>
> > >>
> > >> "Randy" <(E-Mail Removed)> wrote in message
> > >> news:F9E65A4C-25BB-4AB5-9C70-(E-Mail Removed)...
> > >> > 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
> > >>
> > >>
> > >>
> >
> >
> >