Copy and paste a range based ona requirement

GR8TRTHANU · #1 (**permalink**) 01-29-08, 14:59

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

rockingred · #2 (**permalink**) 01-30-08, 09:19

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.

GR8TRTHANU · #3 (**permalink**) 01-30-08, 15:05

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

MikeTheBike · #4 (**permalink**) 01-31-08, 08:14

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

GR8TRTHANU · #5 (**permalink**) 01-31-08, 11:50

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??

GR8TRTHANU · #6 (**permalink**) 01-31-08, 12:53

If I post the workbook would that be going against any rules of the site?

shades · #7 (**permalink**) 01-31-08, 17:01

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).

GR8TRTHANU · #8 (**permalink**) 01-31-08, 18:13

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.

shades · #9 (**permalink**) 01-31-08, 22:41

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?

GR8TRTHANU · #10 (**permalink**) 02-05-08, 10:56

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

shades · #11 (**permalink**) 02-05-08, 15:03

I am working on it. But every time I get started, work interferes.

shades · #12 (**permalink**) 02-05-08, 16:08

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech