Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Feb 2011
    Posts
    27
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Copy certain rows of data to new spreadsheet problem

    I have a spreadsheet with rows of data where I want to copy only the rows where the sample name begins with S or s. The following code does this but copies each "S" or "s" row on top of the previously copied data instead of locating the first open row. Important for you to know that there is no data in Column A so all the copied rows go into row 2. I realized this and when I change the column "A" to "B" where I have some header data which should permit the macro to determine the proper open row to put the data, , the macro crashes saying there is a copy/paste size mismatch (Runtime error 1004). I have worked and experimented and googled on this for several hours and would appreciate any help!
    Thanks
    Arjay



    Sub CopyStandardsRows()
    Dim Oldsheet As String
    Dim newstring As String


    'use to copy all rows with Standards to New worksheet

    'add a sheet named "Standards" & return to the original sheet
    '
    Oldsheet$ = ActiveSheet.Name
    Worksheets.Add(After:=Worksheets(Worksheets.Count) ).Name = "Standards"
    Range("D1").Activate
    Worksheets(Oldsheet$).Activate

    'Copy rows ("2:4") to "Standards" sheet

    Rows("2:4").Copy Destination:=Sheets("Standards").Rows("2:4")


    Range("C5").Activate

    Do While Not IsEmpty(ActiveCell)

    newstring = Left(ActiveCell.Value, 1)

    If newstring = "S" Or newstring = "s" Then
    ActiveCell.EntireRow.Copy Destination:=Sheets("Standards").Range _
    ("a" & Rows.Count).End(xlUp).Offset(1)

    End If

    ActiveCell.Offset(1, 0).Select

    Loop
    End Sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You get a size mismatch if you change the "a" to a "b" since you copied an entire row and are trying to paste it into a partial row (from B to the end). try the line:
    ActiveCell.EntireRow.Copy Destination:=Sheets("Standards").Range _
    ("b" & Rows.Count).End(xlUp).Offset(1, -1)


    Steve

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts
    Hi

    Is there a reason you stopping you using the built in command Data>.Advanced Filter>

    In this way you would not need any code at all.
    The Criteria Region would have separate rows for each of "S' & "s"

    You would only need to specify to copy to another location as opposed to filtering the list in place.

    Cheers
    Geof

  4. #4
    Lounger
    Join Date
    Feb 2011
    Posts
    27
    Thanks
    4
    Thanked 0 Times in 0 Posts
    sdckapr
    as a newbie to VBA programming, I did not fully appreciate all the nuances of the code. I just started reading books last week. Thank you!
    Last edited by arjay13; 2011-04-15 at 23:13.

  5. #5
    Lounger
    Join Date
    Feb 2011
    Posts
    27
    Thanks
    4
    Thanked 0 Times in 0 Posts
    geof
    The only reason stopping me is I am learning a new programming language - so i start with simple steps. In this case, S and s would signify the same thing to me, so they actually sould be in the same range or region. I will try again on Monday. Thank you for your insight. Btw, I was in Aukland 10 years ago. Wonderful place. Got down to Rota Rua to the geothermal springs. Didn't get down to south island though. Have a college buddy down in Christchurch. He luckily missed the earthquake by a couple of miles. thanks for your input and be well.
    Arjay13

Posting Permissions

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