Hi, I am using the following code to copy paste information from one worksheet to other. It is supposed to filter for non-blanks in a column and copy over a range of other columns including the column that was looked up to another worksheet in certain cells (skipping 1 row and 26 columns or i.e, in AA column).
The problem I am having is in the first case the blank records are being picked and copied over. The second one is working fine (no blank is copied over).
Can't find the issue here. Help please?
Dim ws As Worksheet
Application.ScreenUpdating = False
Set ws = Worksheets("Master Sheet")
Sheets("Prioritization").Activate
Application.DisplayAlerts = False
'structural item#1
Columns("DG:DG").Select
Selection.AutoFilter
ws.Range("$DG$1:$DG$65535").AutoFilter Field:=1, Criteria1:="<>" 'filters for non blanks in DG column
ws.Range("A2:DM6500").SpecialCells(xlCellTypeVisible).Copy 'copies information up to DM column
Sheets("Prioritization").Select
Range("A65536").End(xlUp).Offset(1, 26).PasteSpecial Paste:=xlPasteValues 'skips A to Z rows and pastes first record in in AA row
Application.CutCopyMode = False
ws.AutoFilterMode = False
'structural item #2
ws.Select
Columns("DN:DN").Select
Selection.AutoFilter
ws.Range("$DN$1:$DN$65535").AutoFilter Field:=1, Criteria1:="<>"
ws.Range("A2:DT6500").SpecialCells(xlCellTypeVisible).Copy 'the numbers "6500" in this row need to be adjusted manually
Sheets("Prioritization").Select
Range("AA65536").End(xlUp).Offset(2, 0).PasteSpecial Paste:=xlPasteValues 'leaves a row and appends records in next row onward in AA
Application.CutCopyMode = False
ws.AutoFilterMode = False
End Sub
The problem I am having is in the first case the blank records are being picked and copied over. The second one is working fine (no blank is copied over).
Can't find the issue here. Help please?
Dim ws As Worksheet
Application.ScreenUpdating = False
Set ws = Worksheets("Master Sheet")
Sheets("Prioritization").Activate
Application.DisplayAlerts = False
'structural item#1
Columns("DG:DG").Select
Selection.AutoFilter
ws.Range("$DG$1:$DG$65535").AutoFilter Field:=1, Criteria1:="<>" 'filters for non blanks in DG column
ws.Range("A2:DM6500").SpecialCells(xlCellTypeVisible).Copy 'copies information up to DM column
Sheets("Prioritization").Select
Range("A65536").End(xlUp).Offset(1, 26).PasteSpecial Paste:=xlPasteValues 'skips A to Z rows and pastes first record in in AA row
Application.CutCopyMode = False
ws.AutoFilterMode = False
'structural item #2
ws.Select
Columns("DN:DN").Select
Selection.AutoFilter
ws.Range("$DN$1:$DN$65535").AutoFilter Field:=1, Criteria1:="<>"
ws.Range("A2:DT6500").SpecialCells(xlCellTypeVisible).Copy 'the numbers "6500" in this row need to be adjusted manually
Sheets("Prioritization").Select
Range("AA65536").End(xlUp).Offset(2, 0).PasteSpecial Paste:=xlPasteValues 'leaves a row and appends records in next row onward in AA
Application.CutCopyMode = False
ws.AutoFilterMode = False
End Sub