+ Reply to Thread
Results 1 to 5 of 5

macro not working as intended

  1. #1
    Michael A
    Guest

    macro not working as intended

    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").End(xlUp).Offset(1,
    0)
    Next oCell


    End Sub


  2. #2
    gocush
    Guest

    RE: macro not working as intended

    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 everywhere: (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").End(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").End(xlUp).Offset(1,
    > 0)
    > Next oCell
    >
    >
    > End Sub
    >


  3. #3
    Michael A
    Guest

    RE: macro not working as intended

    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 everywhere: (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").End(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").End(xlUp).Offset(1,
    > > 0)
    > > Next oCell
    > >
    > >
    > > End Sub
    > >


  4. #4
    Michael A
    Guest

    RE: macro not working as intended

    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 everywhere: (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").End(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").End(xlUp).Offset(1,
    > > > 0)
    > > > Next oCell
    > > >
    > > >
    > > > End Sub
    > > >


  5. #5
    Dave Peterson
    Guest

    Re: macro not working as intended

    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").End(xlUp).Offset(1,
    > 0)
    > Next oCell
    >
    >
    > End Sub


    --

    Dave Peterson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1