Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2008
    Posts
    6

    Red face Unanswered: Copy and paste a range based ona requirement

    Thanks for looking at ths to begin with. before I throw my computer out the window can someone please help me out? I will try to explain what should happen. I have two sheets in my workbook (data is sheet 1 and ontario is sheet 2) There are 1200 rows and 13 columns in Data (sheet 1). I tried coding but was unsuccessful!! I need to go row by row searching for Ontario. If ontario is present in column M the entire range needs to copied and then pasted in worksheet 2 "Ontario") there will be numerous rows with Ontario in it so lots of copy pasting. Your help will greatly be appreciated.

    Thank you

  2. #2
    Join Date
    Aug 2003
    Location
    Toronto, Ontario, Canada
    Posts
    203
    Can you resort Sheet 1 by Column M and then copy the necessary data to Sheet 2? That would probably be the simplest and quickest method.
    When it rains, it pours.

  3. #3
    Join Date
    Jan 2008
    Posts
    6

    Angry

    The problem is not every line has Ontario. So it need to go line by line using a do until perhaps. If it says Ontario then the entire row needs to be copied and pasted to a different spreadsheet

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    This does something like what you want, but not sure what the destinaion should be ??
    Code:
    Sub CopyThisRow()
        Dim i As Long
        
        i = 1
        Do Until Cells(i, 13) = ""
            If Cells(i, 13) = "Ontario" Then
                Worksheets("Sheet1").Rows(i).Copy Destination:=Worksheets("Sheet2").Rows(i)
            End If
            i = i + 1
        Loop
    End Sub

    MTB

  5. #5
    Join Date
    Jan 2008
    Posts
    6

    Angry

    Perfect.....thats what I thought the code should look like!! However, its not working!! I have put this as code for a button. I have reviewed the code and can't get it to work!! Any ideas??

  6. #6
    Join Date
    Jan 2008
    Posts
    6
    If I post the workbook would that be going against any rules of the site?

  7. #7
    Join Date
    Oct 2003
    Posts
    1,091
    No, you can post the workbook. I think you might have to save as zip file first. But it does help to post workbook samples (as long as they are not huge).
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  8. #8
    Join Date
    Jan 2008
    Posts
    6

    Here is the sample

    I have included the workbook to this post. There are four sheets with the first sheet holding all the information. based on column L value the entire row should be copied and pasted on the proper worksheet. At the top of the first sheet is a command button when you select the button it should go line by line looking at column L. If the value is VA0000 then the entire row should be copied and pasted on the sheet entitled VA0000. this would be the same as VC0000, VE0000, and VG0000. There could be hundreds of lines in the first sheet entiled Source Data.Each line would fall after each other.

    ANY HELP TRYING TO GET THIS CODE TO WORK WOULD BE GREATLY APPRECIATED.
    Attached Files Attached Files

  9. #9
    Join Date
    Oct 2003
    Posts
    1,091
    Just to clarify: for each unique value in column L, you want a worksheet made with that name and you want the entire row copied to that specific worksheet?
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  10. #10
    Join Date
    Jan 2008
    Posts
    6
    That is correct.The entire row should be copied then pasted on a specific sheet that has already been created depending on the unique value in column "L" If the cell value is VA0000 then it would be copied and pasted into sheet VA0000. There will be multiple lines to each sheet but will be random throughout the data source sheet

  11. #11
    Join Date
    Oct 2003
    Posts
    1,091
    I am working on it. But every time I get started, work interferes.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  12. #12
    Join Date
    Oct 2003
    Posts
    1,091
    This code will do what you want. You can fine tune it as needed. I used Select Case for each worksheet. You can add more as needed, just copy the three lines of code for each Case.

    Code:
    Sub test2()
        Dim lngLastRowSD As Long
        lngLastRowSD = Worksheets("SourceData").Cells(Rows.Count, 1).End(xlUp).Row
        Dim MyLocation As String
        Dim lngI As Long
        Application.ScreenUpdating = False
        For lngI = 3 To lngLastRowSD
            MyLocation = Worksheets("SourceData").Cells(lngI, 12).Value
            Select Case MyLocation
                Case Is = "VA0000"
                   lngNextRowWS = Worksheets(MyLocation).Cells(Rows.Count, 1).End(xlUp).Row + 1
                    Worksheets("SourceData").Rows(lngI).Copy
                    Worksheets(MyLocation).Rows(lngNextRowWS).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                Case Is = "VC0000"
                   lngNextRowWS = Worksheets(MyLocation).Cells(Rows.Count, 1).End(xlUp).Row + 1
                    Worksheets("SourceData").Rows(lngI).Copy
                    Worksheets(MyLocation).Rows(lngNextRowWS).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                Case Is = "VE0000"
                   lngNextRowWS = Worksheets(MyLocation).Cells(Rows.Count, 1).End(xlUp).Row + 1
                    Worksheets("SourceData").Rows(lngI).Copy
                    Worksheets(MyLocation).Rows(lngNextRowWS).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                Case Is = "VG0000"
                   lngNextRowWS = Worksheets(MyLocation).Cells(Rows.Count, 1).End(xlUp).Row + 1
                    Worksheets("SourceData").Rows(lngI).Copy
                    Worksheets(MyLocation).Rows(lngNextRowWS).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            End Select
        Next lngI
        Application.ScreenUpdating = True
    End Sub
    If you find that it works for you, then attach it to the button on the SourceData worksheet. BTW I removed the space in the worksheet name for easier reference.
    Attached Files Attached Files
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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