Ok, I have been at this for awhile, with no knowledge of VB at all, what I am
trying to do is copy all the rows on a sheet to the appropriate named sheet
using the B column as the reference to the sheet name. I have the code
working to where i dont get errors now, but its not working as intended. i
dont get any errors.. but its not moving all the data, only some of it, the
names are correct, it just dosn't move them. For example, it skips everything
that goes to the "BOB" sheet, even though there is plenty of things that
match that criteria, but the "RES" sheet has everythign moved over to it just
fine.. its being selective..any help would be great here is the code i have
that produces no errors

Sub CopyToSheets()

Dim rng As Range
Dim oCell As Range

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


End Sub

Michael,
This looks like the code I sent you earlier. A couple of comments.

1. This code only copies but does not delete the old. Usually when we say
we intend to MOVE data it means we also want to delete the old data. Do you
want to just copy or also delete the old data?

2. Since the code works for some sheet names but not others, it leads me to
the conclusion that the code is good but the Sheet name is not the same as
what you have in Col B. My guess is that it is a case-sensitive issue:
something may be capitalizes but not everywhe (BOB vs Bob) or maybe a
space at the end ("BOB" vs "BOB ") which cannot be seen but is still there.

You might try adding the following error trapping:

Sub CopyToSheets()

Dim rng As Range
Dim oCell As Range
Dim Sh As Worksheet

On Error GoTo ErrHandler
Set rng = Range("B4:B" & Cells(Rows.Count, "B").End(xlUp).Row)
For Each oCell In rng
oCell.EntireRow.Copy _
Destination:=Sheets(oCell.Value).Range("A65536").E nd(xlUp).Offset(1,
0)
Sheets(oCell.Value).Select
Sheets("Sheet1").Select
Next oCell
Exit Sub
ErrHandler:
MsgBox "Cannot find the Worksheet: " & oCell.Value
For Each Sh In Worksheets
If UCase(Sh.Name) Like UCase(Trim(oCell.Value)) Then
MsgBox "However, the Sheet named: " & Sh.Name & " was
found."
End If
Next Sh
End Sub

"Michael A" wrote:

Ok, I have been at this for awhile, with no knowledge of VB at all, what I am
trying to do is copy all the rows on a sheet to the appropriate named sheet
using the B column as the reference to the sheet name. I have the code
working to where i dont get errors now, but its not working as intended. i
dont get any errors.. but its not moving all the data, only some of it, the
names are correct, it just dosn't move them. For example, it skips everything
that goes to the "BOB" sheet, even though there is plenty of things that
match that criteria, but the "RES" sheet has everythign moved over to it just
fine.. its being selective..any help would be great here is the code i have
that produces no errors

Sub CopyToSheets()

Dim rng As Range
Dim oCell As Range

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


End Sub

hey gocush, im glad you responded, it is indeed the code you sent me. I was
mistaken when i posted this.. the sheets that I thought it wasn't pasting
them to, it is, however, it is pasting them to row 20345 and below. So some
are working just fine but others arent posting until after row 20k, but they
are blank sheets below line 4.. any ideas?

"gocush" wrote:

Michael,
This looks like the code I sent you earlier. A couple of comments.

1. This code only copies but does not delete the old. Usually when we say
we intend to MOVE data it means we also want to delete the old data. Do you
want to just copy or also delete the old data?

2. Since the code works for some sheet names but not others, it leads me to
the conclusion that the code is good but the Sheet name is not the same as
what you have in Col B. My guess is that it is a case-sensitive issue:
something may be capitalizes but not everywhe (BOB vs Bob) or maybe a
space at the end ("BOB" vs "BOB ") which cannot be seen but is still there.

You might try adding the following error trapping:

Sub CopyToSheets()

Dim rng As Range
Dim oCell As Range
Dim Sh As Worksheet

On Error GoTo ErrHandler
Set rng = Range("B4:B" & Cells(Rows.Count, "B").End(xlUp).Row)
For Each oCell In rng
oCell.EntireRow.Copy _
Destination:=Sheets(oCell.Value).Range("A65536").E nd(xlUp).Offset(1,
0)
Sheets(oCell.Value).Select
Sheets("Sheet1").Select
Next oCell
Exit Sub
ErrHandler:
MsgBox "Cannot find the Worksheet: " & oCell.Value
For Each Sh In Worksheets
If UCase(Sh.Name) Like UCase(Trim(oCell.Value)) Then
MsgBox "However, the Sheet named: " & Sh.Name & " was
found."
End If
Next Sh
End Sub

"Michael A" wrote:

Ok, I have been at this for awhile, with no knowledge of VB at all, what I am
trying to do is copy all the rows on a sheet to the appropriate named sheet
using the B column as the reference to the sheet name. I have the code
working to where i dont get errors now, but its not working as intended. i
dont get any errors.. but its not moving all the data, only some of it, the
names are correct, it just dosn't move them. For example, it skips everything
that goes to the "BOB" sheet, even though there is plenty of things that
match that criteria, but the "RES" sheet has everythign moved over to it just
fine.. its being selective..any help would be great here is the code i have
that produces no errors

Sub CopyToSheets()

Dim rng As Range
Dim oCell As Range

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


End Sub

ok, its working great now. Thank you very much for your help.

"Michael A" wrote:

hey gocush, im glad you responded, it is indeed the code you sent me. I was
mistaken when i posted this.. the sheets that I thought it wasn't pasting
them to, it is, however, it is pasting them to row 20345 and below. So some
are working just fine but others arent posting until after row 20k, but they
are blank sheets below line 4.. any ideas?

"gocush" wrote:

Michael,
This looks like the code I sent you earlier. A couple of comments.

1. This code only copies but does not delete the old. Usually when we say
we intend to MOVE data it means we also want to delete the old data. Do you
want to just copy or also delete the old data?

2. Since the code works for some sheet names but not others, it leads me to
the conclusion that the code is good but the Sheet name is not the same as
what you have in Col B. My guess is that it is a case-sensitive issue:
something may be capitalizes but not everywhe (BOB vs Bob) or maybe a
space at the end ("BOB" vs "BOB ") which cannot be seen but is still there.

You might try adding the following error trapping:

Sub CopyToSheets()

Dim rng As Range
Dim oCell As Range
Dim Sh As Worksheet

On Error GoTo ErrHandler
Set rng = Range("B4:B" & Cells(Rows.Count, "B").End(xlUp).Row)
For Each oCell In rng
oCell.EntireRow.Copy _
Destination:=Sheets(oCell.Value).Range("A65536").E nd(xlUp).Offset(1,
0)
Sheets(oCell.Value).Select
Sheets("Sheet1").Select
Next oCell
Exit Sub
ErrHandler:
MsgBox "Cannot find the Worksheet: " & oCell.Value
For Each Sh In Worksheets
If UCase(Sh.Name) Like UCase(Trim(oCell.Value)) Then
MsgBox "However, the Sheet named: " & Sh.Name & " was
found."
End If
Next Sh
End Sub

"Michael A" wrote:

Ok, I have been at this for awhile, with no knowledge of VB at all, what I am
trying to do is copy all the rows on a sheet to the appropriate named sheet
using the B column as the reference to the sheet name. I have the code
working to where i dont get errors now, but its not working as intended. i
dont get any errors.. but its not moving all the data, only some of it, the
names are correct, it just dosn't move them. For example, it skips everything
that goes to the "BOB" sheet, even though there is plenty of things that
match that criteria, but the "RES" sheet has everythign moved over to it just
fine.. its being selective..any help would be great here is the code i have
that produces no errors

Sub CopyToSheets()

Dim rng As Range
Dim oCell As Range

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


End Sub

See one more guess at your other thread.

Michael A wrote:

Ok, I have been at this for awhile, with no knowledge of VB at all, what I am
trying to do is copy all the rows on a sheet to the appropriate named sheet
using the B column as the reference to the sheet name. I have the code
working to where i dont get errors now, but its not working as intended. i
dont get any errors.. but its not moving all the data, only some of it, the
names are correct, it just dosn't move them. For example, it skips everything
that goes to the "BOB" sheet, even though there is plenty of things that
match that criteria, but the "RES" sheet has everythign moved over to it just
fine.. its being selective..any help would be great here is the code i have
that produces no errors

Sub CopyToSheets()

Dim rng As Range
Dim oCell As Range

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


End Sub

--

Dave Peterson