tonywatsonhelp
Well-known Member
- Joined
- Feb 24, 2014
- Messages
- 3,204
- Office Version
- 365
- 2019
- 2016
- Platform
- Windows
Hi Everyone,
I have a macro that copys and paste data into another sheet,
It works great but I only have 30 rows avaliable to paste into,
is there a way i can say "If rows to copy are greater than 30 only copy first 30?
i've shown my current formula
my code:
I have a macro that copys and paste data into another sheet,
It works great but I only have 30 rows avaliable to paste into,
is there a way i can say "If rows to copy are greater than 30 only copy first 30?
i've shown my current formula
my code:
VBA Code:
Sub filter1()
Subms.AutoFilterMode = False
LR1 = Subms.Cells(Rows.Count, "A").End(xlUp).Row
Subms.Range("$H$10:$BP$" & LR1).AutoFilter Field:=1, Criteria1:="Signed"
Subms.Range("$H$10:$BP$" & LR1).AutoFilter Field:=61, Criteria1:="Pacific"
Subms.Range("$H$10:$BP$" & LR1).AutoFilter Field:=42, Criteria1:=">0"
'''Count if the filter shows any results
lr2 = Subms.Cells(Rows.Count, "A").End(xlUp).Row
If lr2 < 11 Then
lr2 = 11
End If
xCount = 0
For Each xCell In Subms.Range("A11:A" & lr2)
If (Not xCell.EntireRow.Hidden) Then
xCount = xCount + 1
End If
Next
If xCount > 0 Then
Subms.Range("M11:M" & lr2).SpecialCells(xlCellTypeVisible).Copy
PDFR.Range("Ac864").PasteSpecial xlPasteValues
Subms.Range("AH11:AH" & lr2).SpecialCells(xlCellTypeVisible).Copy
PDFR.Range("AE864").PasteSpecial xlPasteValues
Subms.Range("AW11:AW" & lr2).SpecialCells(xlCellTypeVisible).Copy
PDFR.Range("AG864").PasteSpecial xlPasteValues
End If
End Sub