copy macro help

[Michael A]

Anyhelp would be greatly appreciated..

I need a macro that will copy all the rows on a spreadsheet and send them to
the appropriate sheets based on the value in the B column in the row. For
example, B5 has value as "RES" so i want that row sent to the next available
row on the RES spread sheet. If it was "BOB" then i would want it sent to the
bob spreadsheet. Could someone please help me with this? Thank you!

I apolgize if this is a duplicate post. Ive tried to post several times and
hours later it hasn't showed up..

Thanks!

[David]

Michael try this:
Sub Macro1()
ThisSheet = ActiveSheet.Name
Range("B1").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Do Until ActiveCell.Value = ""
ToSheet = ActiveCell.Value
ActiveCell.EntireRow.Copy
Sheets(ToSheet).Select
ActiveSheet.Paste
Sheets(ThisSheet).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
Application.CutCopyMode = False
End Sub

Thanks,

"Michael A" wrote:

> Anyhelp would be greatly appreciated..
>
> I need a macro that will copy all the rows on a spreadsheet and send them to
> the appropriate sheets based on the value in the B column in the row. For
> example, B5 has value as "RES" so i want that row sent to the next available
> row on the RES spread sheet. If it was "BOB" then i would want it sent to the
> bob spreadsheet. Could someone please help me with this? Thank you!
>
> I apolgize if this is a duplicate post. Ive tried to post several times and
> hours later it hasn't showed up..
>
> Thanks!

[David]

Sorry forgot to put it in the right place:
Sub Macro1()
ThisSheet = ActiveSheet.Name
Range("B1").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Do Until ActiveCell.Value = ""
ToSheet = ActiveCell.Value
ActiveCell.EntireRow.Copy
Sheets(ToSheet).Select
Range("A1").Select
Selection.End(xlDown).Select
If ActiveCell.Row = 65536 Then
Range("A2").Select
Else
ActiveCell.Offset(1, 0).Range("A1").Select
End If
ActiveSheet.Paste
Sheets(ThisSheet).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
Application.CutCopyMode = False
End Sub

"David" wrote:

> Michael try this:
> Sub Macro1()
> ThisSheet = ActiveSheet.Name
> Range("B1").Select
> ActiveCell.Offset(1, 0).Range("A1").Select
> Do Until ActiveCell.Value = ""
> ToSheet = ActiveCell.Value
> ActiveCell.EntireRow.Copy
> Sheets(ToSheet).Select
> ActiveSheet.Paste
> Sheets(ThisSheet).Select
> ActiveCell.Offset(1, 0).Range("A1").Select
> Loop
> Application.CutCopyMode = False
> End Sub
>
> Thanks,
>
> "Michael A" wrote:
>
> > Anyhelp would be greatly appreciated..
> >
> > I need a macro that will copy all the rows on a spreadsheet and send them to
> > the appropriate sheets based on the value in the B column in the row. For
> > example, B5 has value as "RES" so i want that row sent to the next available
> > row on the RES spread sheet. If it was "BOB" then i would want it sent to the
> > bob spreadsheet. Could someone please help me with this? Thank you!
> >
> > I apolgize if this is a duplicate post. Ive tried to post several times and
> > hours later it hasn't showed up..
> >
> > Thanks!

[David]

Sorry forgot to put it in the right place.
Sub Macro1()
ThisSheet = ActiveSheet.Name
Range("B1").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Do Until ActiveCell.Value = ""
ToSheet = ActiveCell.Value
ActiveCell.EntireRow.Copy
Sheets(ToSheet).Select
Range("A1").Select
Selection.End(xlDown).Select
If ActiveCell.Row = 65536 Then
Range("A2").Select
Else
ActiveCell.Offset(1, 0).Range("A1").Select
End If
ActiveSheet.Paste
Sheets(ThisSheet).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
Application.CutCopyMode = False
End Sub

"David" wrote:

> Michael try this:
> Sub Macro1()
> ThisSheet = ActiveSheet.Name
> Range("B1").Select
> ActiveCell.Offset(1, 0).Range("A1").Select
> Do Until ActiveCell.Value = ""
> ToSheet = ActiveCell.Value
> ActiveCell.EntireRow.Copy
> Sheets(ToSheet).Select
> ActiveSheet.Paste
> Sheets(ThisSheet).Select
> ActiveCell.Offset(1, 0).Range("A1").Select
> Loop
> Application.CutCopyMode = False
> End Sub
>
> Thanks,
>
> "Michael A" wrote:
>
> > Anyhelp would be greatly appreciated..
> >
> > I need a macro that will copy all the rows on a spreadsheet and send them to
> > the appropriate sheets based on the value in the B column in the row. For
> > example, B5 has value as "RES" so i want that row sent to the next available
> > row on the RES spread sheet. If it was "BOB" then i would want it sent to the
> > bob spreadsheet. Could someone please help me with this? Thank you!
> >
> > I apolgize if this is a duplicate post. Ive tried to post several times and
> > hours later it hasn't showed up..
> >
> > Thanks!

[David]

Sub Macro1()
ThisSheet = ActiveSheet.Name
Range("B1").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Do Until ActiveCell.Value = ""
ToSheet = ActiveCell.Value
ActiveCell.EntireRow.Copy
Sheets(ToSheet).Select
Range("A1").Select
Selection.End(xlDown).Select
If ActiveCell.Row = 65536 Then
Range("A2").Select
Else
ActiveCell.Offset(1, 0).Range("A1").Select
End If
ActiveSheet.Paste
Sheets(ThisSheet).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
Application.CutCopyMode = False
End Sub

"David" wrote:

> Michael try this:
> Sub Macro1()
> ThisSheet = ActiveSheet.Name
> Range("B1").Select
> ActiveCell.Offset(1, 0).Range("A1").Select
> Do Until ActiveCell.Value = ""
> ToSheet = ActiveCell.Value
> ActiveCell.EntireRow.Copy
> Sheets(ToSheet).Select
> ActiveSheet.Paste
> Sheets(ThisSheet).Select
> ActiveCell.Offset(1, 0).Range("A1").Select
> Loop
> Application.CutCopyMode = False
> End Sub
>
> Thanks,
>
> "Michael A" wrote:
>
> > Anyhelp would be greatly appreciated..
> >
> > I need a macro that will copy all the rows on a spreadsheet and send them to
> > the appropriate sheets based on the value in the B column in the row. For
> > example, B5 has value as "RES" so i want that row sent to the next available
> > row on the RES spread sheet. If it was "BOB" then i would want it sent to the
> > bob spreadsheet. Could someone please help me with this? Thank you!
> >
> > I apolgize if this is a duplicate post. Ive tried to post several times and
> > hours later it hasn't showed up..
> >
> > Thanks!

[David]

Sub Macro1()
ThisSheet = ActiveSheet.Name
Range("B1").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Do Until ActiveCell.Value = ""
ToSheet = ActiveCell.Value
ActiveCell.EntireRow.Copy
Sheets(ToSheet).Select
Range("A1").Select
Selection.End(xlDown).Select
If ActiveCell.Row = 65536 Then
Range("A2").Select
Else
ActiveCell.Offset(1, 0).Range("A1").Select
End If
ActiveSheet.Paste
Sheets(ThisSheet).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
Application.CutCopyMode = False
End Sub

"David" wrote:

> Michael try this:
> Sub Macro1()
> ThisSheet = ActiveSheet.Name
> Range("B1").Select
> ActiveCell.Offset(1, 0).Range("A1").Select
> Do Until ActiveCell.Value = ""
> ToSheet = ActiveCell.Value
> ActiveCell.EntireRow.Copy
> Sheets(ToSheet).Select
> ActiveSheet.Paste
> Sheets(ThisSheet).Select
> ActiveCell.Offset(1, 0).Range("A1").Select
> Loop
> Application.CutCopyMode = False
> End Sub
>
> Thanks,
>
> "Michael A" wrote:
>
> > Anyhelp would be greatly appreciated..
> >
> > I need a macro that will copy all the rows on a spreadsheet and send them to
> > the appropriate sheets based on the value in the B column in the row. For
> > example, B5 has value as "RES" so i want that row sent to the next available
> > row on the RES spread sheet. If it was "BOB" then i would want it sent to the
> > bob spreadsheet. Could someone please help me with this? Thank you!
> >
> > I apolgize if this is a duplicate post. Ive tried to post several times and
> > hours later it hasn't showed up..
> >
> > Thanks!

[gocush]

Michael,
Try the following. Note: this should also have some error trapping to make
sure the cells in col B actually have a sheet with the same name ( no blanks
or typos)

Sub CopyToSheets()

Dim Rng As Range
Dim oCell As Range

Set Rng = Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row)
For Each oCell In Rng
oCell.EntireRow.Copy _
Destination:=Sheets(oCell.Value).Range("A65536").End(xlUp).Offset(1,
0)
Next oCell


End Sub

"Michael A" wrote:

> Anyhelp would be greatly appreciated..
>
> I need a macro that will copy all the rows on a spreadsheet and send them to
> the appropriate sheets based on the value in the B column in the row. For
> example, B5 has value as "RES" so i want that row sent to the next available
> row on the RES spread sheet. If it was "BOB" then i would want it sent to the
> bob spreadsheet. Could someone please help me with this? Thank you!
>
> I apolgize if this is a duplicate post. Ive tried to post several times and
> hours later it hasn't showed up..
>
> Thanks!

[J_J]

David,
When I tried your code (WinXP+XL2003) received a
"Select method of range class failed error" on line
Range("A1").Select
J_J

"David" <[email protected]> wrote in message
news:[email protected]...
> Sub Macro1()
> ThisSheet = ActiveSheet.Name
> Range("B1").Select
> ActiveCell.Offset(1, 0).Range("A1").Select
> Do Until ActiveCell.Value = ""
> ToSheet = ActiveCell.Value
> ActiveCell.EntireRow.Copy
> Sheets(ToSheet).Select
> Range("A1").Select
> Selection.End(xlDown).Select
> If ActiveCell.Row = 65536 Then
> Range("A2").Select
> Else
> ActiveCell.Offset(1, 0).Range("A1").Select
> End If
> ActiveSheet.Paste
> Sheets(ThisSheet).Select
> ActiveCell.Offset(1, 0).Range("A1").Select
> Loop
> Application.CutCopyMode = False
> End Sub
>
> "David" wrote:
>
>> Michael try this:
>> Sub Macro1()
>> ThisSheet = ActiveSheet.Name
>> Range("B1").Select
>> ActiveCell.Offset(1, 0).Range("A1").Select
>> Do Until ActiveCell.Value = ""
>> ToSheet = ActiveCell.Value
>> ActiveCell.EntireRow.Copy
>> Sheets(ToSheet).Select
>> ActiveSheet.Paste
>> Sheets(ThisSheet).Select
>> ActiveCell.Offset(1, 0).Range("A1").Select
>> Loop
>> Application.CutCopyMode = False
>> End Sub
>>
>> Thanks,
>>
>> "Michael A" wrote:
>>
>> > Anyhelp would be greatly appreciated..
>> >
>> > I need a macro that will copy all the rows on a spreadsheet and send
>> > them to
>> > the appropriate sheets based on the value in the B column in the row.
>> > For
>> > example, B5 has value as "RES" so i want that row sent to the next
>> > available
>> > row on the RES spread sheet. If it was "BOB" then i would want it sent
>> > to the
>> > bob spreadsheet. Could someone please help me with this? Thank you!
>> >
>> > I apolgize if this is a duplicate post. Ive tried to post several times
>> > and
>> > hours later it hasn't showed up..
>> >
>> > Thanks!

[Tom Ogilvy]

You must be running it from a sheet module.

run it from a general module.

--
Regards,
Tom Ogilvy


"J_J" <[email protected]> wrote in message
news:%[email protected]...
> David,
> When I tried your code (WinXP+XL2003) received a
> "Select method of range class failed error" on line
> Range("A1").Select
> J_J
>
> "David" <[email protected]> wrote in message
> news:[email protected]...
> > Sub Macro1()
> > ThisSheet = ActiveSheet.Name
> > Range("B1").Select
> > ActiveCell.Offset(1, 0).Range("A1").Select
> > Do Until ActiveCell.Value = ""
> > ToSheet = ActiveCell.Value
> > ActiveCell.EntireRow.Copy
> > Sheets(ToSheet).Select
> > Range("A1").Select
> > Selection.End(xlDown).Select
> > If ActiveCell.Row = 65536 Then
> > Range("A2").Select
> > Else
> > ActiveCell.Offset(1, 0).Range("A1").Select
> > End If
> > ActiveSheet.Paste
> > Sheets(ThisSheet).Select
> > ActiveCell.Offset(1, 0).Range("A1").Select
> > Loop
> > Application.CutCopyMode = False
> > End Sub
> >
> > "David" wrote:
> >
> >> Michael try this:
> >> Sub Macro1()
> >> ThisSheet = ActiveSheet.Name
> >> Range("B1").Select
> >> ActiveCell.Offset(1, 0).Range("A1").Select
> >> Do Until ActiveCell.Value = ""
> >> ToSheet = ActiveCell.Value
> >> ActiveCell.EntireRow.Copy
> >> Sheets(ToSheet).Select
> >> ActiveSheet.Paste
> >> Sheets(ThisSheet).Select
> >> ActiveCell.Offset(1, 0).Range("A1").Select
> >> Loop
> >> Application.CutCopyMode = False
> >> End Sub
> >>
> >> Thanks,
> >>
> >> "Michael A" wrote:
> >>
> >> > Anyhelp would be greatly appreciated..
> >> >
> >> > I need a macro that will copy all the rows on a spreadsheet and send
> >> > them to
> >> > the appropriate sheets based on the value in the B column in the row.
> >> > For
> >> > example, B5 has value as "RES" so i want that row sent to the next
> >> > available
> >> > row on the RES spread sheet. If it was "BOB" then i would want it
sent
> >> > to the
> >> > bob spreadsheet. Could someone please help me with this? Thank you!
> >> >
> >> > I apolgize if this is a duplicate post. Ive tried to post several
times
> >> > and
> >> > hours later it hasn't showed up..
> >> >
> >> > Thanks!
>
>

[Myrna Larson]

The problem lies in which sheet is the active sheet. It's probably not the
"ToSheet".

Use the other macro you were given. AIR, it was only a couple of lines. It
demonstrates that you don't need to select and activate cells and worksheets
to work with them, and doing so just slows things down.

On Sat, 5 Mar 2005 13:22:59 +0200, "J_J" <[email protected]> wrote:

>David,
>When I tried your code (WinXP+XL2003) received a
>"Select method of range class failed error" on line
>Range("A1").Select
>J_J
>
>"David" <[email protected]> wrote in message
>news:[email protected]...
>> Sub Macro1()
>> ThisSheet = ActiveSheet.Name
>> Range("B1").Select
>> ActiveCell.Offset(1, 0).Range("A1").Select
>> Do Until ActiveCell.Value = ""
>> ToSheet = ActiveCell.Value
>> ActiveCell.EntireRow.Copy
>> Sheets(ToSheet).Select
>> Range("A1").Select
>> Selection.End(xlDown).Select
>> If ActiveCell.Row = 65536 Then
>> Range("A2").Select
>> Else
>> ActiveCell.Offset(1, 0).Range("A1").Select
>> End If
>> ActiveSheet.Paste
>> Sheets(ThisSheet).Select
>> ActiveCell.Offset(1, 0).Range("A1").Select
>> Loop
>> Application.CutCopyMode = False
>> End Sub
>>
>> "David" wrote:
>>
>>> Michael try this:
>>> Sub Macro1()
>>> ThisSheet = ActiveSheet.Name
>>> Range("B1").Select
>>> ActiveCell.Offset(1, 0).Range("A1").Select
>>> Do Until ActiveCell.Value = ""
>>> ToSheet = ActiveCell.Value
>>> ActiveCell.EntireRow.Copy
>>> Sheets(ToSheet).Select
>>> ActiveSheet.Paste
>>> Sheets(ThisSheet).Select
>>> ActiveCell.Offset(1, 0).Range("A1").Select
>>> Loop
>>> Application.CutCopyMode = False
>>> End Sub
>>>
>>> Thanks,
>>>
>>> "Michael A" wrote:
>>>
>>> > Anyhelp would be greatly appreciated..
>>> >
>>> > I need a macro that will copy all the rows on a spreadsheet and send
>>> > them to
>>> > the appropriate sheets based on the value in the B column in the row.
>>> > For
>>> > example, B5 has value as "RES" so i want that row sent to the next
>>> > available
>>> > row on the RES spread sheet. If it was "BOB" then i would want it sent
>>> > to the
>>> > bob spreadsheet. Could someone please help me with this? Thank you!
>>> >
>>> > I apolgize if this is a duplicate post. Ive tried to post several times
>>> > and
>>> > hours later it hasn't showed up..
>>> >
>>> > Thanks!
>

[Michael A]

Thank you everyone so much for your replies. I will try it now and report back.

"gocush" wrote:

> Michael,
> Try the following. Note: this should also have some error trapping to make
> sure the cells in col B actually have a sheet with the same name ( no blanks
> or typos)
>
> Sub CopyToSheets()
>
> Dim Rng As Range
> Dim oCell As Range
>
> Set Rng = Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row)
> For Each oCell In Rng
> oCell.EntireRow.Copy _
> Destination:=Sheets(oCell.Value).Range("A65536").End(xlUp).Offset(1,
> 0)
> Next oCell
>
>
> End Sub
>
> "Michael A" wrote:
>
> > Anyhelp would be greatly appreciated..
> >
> > I need a macro that will copy all the rows on a spreadsheet and send them to
> > the appropriate sheets based on the value in the B column in the row. For
> > example, B5 has value as "RES" so i want that row sent to the next available
> > row on the RES spread sheet. If it was "BOB" then i would want it sent to the
> > bob spreadsheet. Could someone please help me with this? Thank you!
> >
> > I apolgize if this is a duplicate post. Ive tried to post several times and
> > hours later it hasn't showed up..
> >
> > Thanks!