PE New User
New Member
- Joined
- Aug 4, 2014
- Messages
- 21
Hi,
I am pretty sure that this should be a relatively straight forward macro, but I can't seem to work it out. I am trying to find data in a spreadsheet (data always in column H), once found copy the entire row and paste the row into a different tab.
There are multiple rows within the original (Data Sheet) tab that will contain the specific text (Natural Gas) so I want the macro to loop through and copy all rows with Natural Gas in column H to the new tab:
Original Sheet
<tbody>
</tbody>
New Sheet
<tbody>
</tbody>
I have been playing with script and have come up with the following so far, but it only really works to the point of EntireRow.Copy - I can't quite work out how to get the select tab "Gas" and paste the row in the first available row before the loop:
Would appreciate any assistance that can be provided.
I am pretty sure that this should be a relatively straight forward macro, but I can't seem to work it out. I am trying to find data in a spreadsheet (data always in column H), once found copy the entire row and paste the row into a different tab.
There are multiple rows within the original (Data Sheet) tab that will contain the specific text (Natural Gas) so I want the macro to loop through and copy all rows with Natural Gas in column H to the new tab:
Original Sheet
Client Name | Site Name | ..... | .... | ..... | ..... | ..... | Product |
ABC Intl Ltd | 1 ABC Street | Electricity HH | |||||
ABC Intl Ltd | 2 ABC Street | Natural Gas | |||||
ABC Intl Ltd | 3 ABC Street | Electricity NHH | |||||
ABC Intl Ltd | 3 ABC Street | Natural Gas | |||||
ABC Intl Ltd | 4 ABC Street | Natural Gas | |||||
ABC Intl Ltd | 7 ABC Street | Electricity HH |
<tbody>
</tbody>
New Sheet
Client Name | Site Name | ..... | .... | ..... | ..... | ..... | Product |
ABC Intl Ltd | 2 ABC Street | Natural Gas | |||||
ABC Intl Ltd | 3 ABC Street | Natural Gas | |||||
ABC Intl Ltd | 4 ABC Street | Natural Gas | |||||
<tbody>
</tbody>
I have been playing with script and have come up with the following so far, but it only really works to the point of EntireRow.Copy - I can't quite work out how to get the select tab "Gas" and paste the row in the first available row before the loop:
Code:
Sub GasExtract()
Dim rngH As Range
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Worksheets("DATA SHEET").Activate
Range("H2").Select
'Range(Selection, Selection.End(xlDown)).Select
x = 2
Do Until Range("H" & x) = ""
If Range("H" & x).Value = "Natural Gas" Then Range("H" & x).EntireRow.Copy
Sheets("Gas").Select
' Go to last cell and extend down
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
' Copy formula from cell above
Rows(Selection.Row - 1).Copy
Rows(Selection.Row).PasteSpecial
x = x + 1
Loop
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Would appreciate any assistance that can be provided.