Hi Guys,
I need some help creating a Macro.
I'm trying to copy and paste rows of data that contain my criteria column A but not if column B is blank.
I want to be able to enter a team code in cell B2 in the "Summary" tab and have the Macro pull all the non-blank rows of data and paste into the "Summary" tab.
If that doesn't make sense, please let me know.
Here is my code:
Option Explicit Private Sub Worksheet_Activate() Dim ws As Worksheet, LR As Long Application.ScreenUpdating = False If MsgBox("Update this sheet?", vbYesNo) = vbNo Then Exit Sub Range("D3:G" & Rows.Count).Clear For Each ws In Worksheets If ws.Name <> Me.Name Then ws.AutoFilterMode = False ws.Rows.AutoFilter Field:=11, Criteria1:=Range("B2").Value LR = ws.Range("B" & ws.Rows.Count).End(xlUp).Row If LR > 2 Then ws.Range("A3:K" & LR).SpecialCells(xlCellTypeVisible).Copy Range("D3:G" & Rows.Count).End(xlUp).Offset(1, 1).PasteSpecial xlPasteValuesAndNumberFormats End If ws.AutoFilterMode = False End If Next ws Application.ScreenUpdating = True Range("A3").Select End Sub
Bookmarks