Copy certain rows of data to new spreadsheet problem

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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!

Reply | Quote