I'd like to copy multiple rows on five separate worksheets and copy them onto a 6th worksheet at a specific starting cell range. The code i'm working with now is below. It currently copies multiple rows and columns from one sheet based on the variables requested, from s1 to a blank s2. I really need it to paste to a different sheet at a specific starting range. I would also like to add the exact same variable request to s3, s4, s5, s6, then paste the results of all 5 on to a 7th sheet labeled "Proposal" but paste them beginning at a specific cell range of A310 then fill downward from there. The reason I'm pasting them at a specific starting range is because I have other data on the same "Proposal" sheet that I don't want to interfere with or overwrite. The variable for the copy selection is (copy columns C,D & E in any rows with a value of more than 0 in column E). Below is the current macro that copies from sheet 1 and pastes to a blank sheet s2:
Sub TomK58()
Dim s1 As Worksheet, s2 As Worksheet
Set s1 = Sheets("Commercial Video")
Set s2 = Sheets("Sheet2")
Dim i As Long, lr As Long
Dim lr2 As Long
lr = s1.Range("E" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = 1 To lr
If s1.Range("E" & i) > 0 Then
s1.Range("C" & i & ":E" & i).Copy
lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
s2.Range("A" & lr2 + 1).PasteSpecial xlPasteValues
End If
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Action Completed"
End Sub
I'm on a VBA learning fast track, but have a long way to go. Thanks if advance for any help!
Thank you advance for your help and guidance!
Sub TomK58()
Dim s1 As Worksheet, s2 As Worksheet
Set s1 = Sheets("Commercial Video")
Set s2 = Sheets("Sheet2")
Dim i As Long, lr As Long
Dim lr2 As Long
lr = s1.Range("E" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = 1 To lr
If s1.Range("E" & i) > 0 Then
s1.Range("C" & i & ":E" & i).Copy
lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
s2.Range("A" & lr2 + 1).PasteSpecial xlPasteValues
End If
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Action Completed"
End Sub
I'm on a VBA learning fast track, but have a long way to go. Thanks if advance for any help!
Thank you advance for your help and guidance!