Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste rows from one wksht to another based on cell value
Hi,
maybe this Sub marine() Dim CopyRange As Range 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 Set myrange = srcsht.Range("Q1:Q" & LastrowA) For Each q In myrange If InStr(1, q.Value, "cw", vbTextCompare) 0 Then If CopyRange Is Nothing Then Set CopyRange = q.EntireRow Else Set CopyRange = Union(CopyRange, q.EntireRow) End If End If Next If Not CopyRange Is Nothing Then CopyRange.Copy dstsht.Range("A" & LastrowB) End If End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PVANS" wrote: 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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste rows from one wksht to another based on cell va
Hi Mike,
Thank you so much. Works brilliantly. Really appreciate your constant help to me on this website, it is awesome Cheers Paul "Mike H" wrote: Hi, maybe this Sub marine() Dim CopyRange As Range 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 Set myrange = srcsht.Range("Q1:Q" & LastrowA) For Each q In myrange If InStr(1, q.Value, "cw", vbTextCompare) 0 Then If CopyRange Is Nothing Then Set CopyRange = q.EntireRow Else Set CopyRange = Union(CopyRange, q.EntireRow) End If End If Next If Not CopyRange Is Nothing Then CopyRange.Copy dstsht.Range("A" & LastrowB) End If End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PVANS" wrote: 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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste rows from one wksht to another based on cell va
I'm glad to have helped
-- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PVANS" wrote: Hi Mike, Thank you so much. Works brilliantly. Really appreciate your constant help to me on this website, it is awesome Cheers Paul "Mike H" wrote: Hi, maybe this Sub marine() Dim CopyRange As Range 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 Set myrange = srcsht.Range("Q1:Q" & LastrowA) For Each q In myrange If InStr(1, q.Value, "cw", vbTextCompare) 0 Then If CopyRange Is Nothing Then Set CopyRange = q.EntireRow Else Set CopyRange = Union(CopyRange, q.EntireRow) End If End If Next If Not CopyRange Is Nothing Then CopyRange.Copy dstsht.Range("A" & LastrowB) End If End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PVANS" wrote: 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
1st 2 rows of wksht show up @ top of each page of the wksht | Excel Discussion (Misc queries) | |||
Copy/paste rows to new sheet based on cell value | Excel Programming | |||
Macro to Copy/Paste Data into one wksht, skipp one row before past | Excel Discussion (Misc queries) | |||
Copy and paste row based on cell content | Excel Programming | |||
macro to paste certain cell formats based on wksht name | Excel Programming |