View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
PVANS PVANS is offline
external usenet poster
 
Posts: 68
Default Copy and paste rows from one wksht to another based on cell value

Good morning

Was wondering if someone had a suggestion for me with regards to this, I
have the following code that is copying rows from Worksheet1 ("Working") and
adding them to another worksheet in the same workbook called "March":

Set SrcSht = Sheets("Working")
Set DstSht = Sheets("MARCH")
LastrowA = SrcSht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
LastrowB = DstSht.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
SrcSht.Range("A1:A" & LastrowA).EntireRow.Copy DstSht.Cells(LastrowB, 1)

I would like to now copy only specific rows based on their value in column Q
and add them to another worksheet in the workbook. I edited the code as
follows, however, it does not seem to work:

Dim LastrowA As Long
Dim LastrowB As Long
Set SrcSht = Sheets("Working")
Set DstSht = Sheets("Sheet3")
LastrowA = SrcSht.Cells(Cells.Rows.Count, "Q").End(xlUp).Row
LastrowB = DstSht.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1

For q = SrcSht.Range("q65536").End(xlUp).Row To 1 Step -1
If SrcSht.Cells(q, 17) = "cw" Then
SrcSht.Rows(q).Copy DstSht.Cells(LastrowB, 1)
End If
Next

It seems to simply copy the necessary rows onto the same line, thus leaving
me with only one line on Worksheet3. Can someone suggest why this is
happening, and a method to fix it?

Thank you so much

(This is a replica poste as my other one seemed - for completley unknown
reasons - to be in Excel Database programming. apologies for the double
entry, and once again thanks for any help)