Afternoon all,
I have been having some problems with some VBA.
I am trying to filter and then copy and paste the visible cells. However not all areas have data every time the macro is run so i need to find a way to skip past a request when it returns run time error 1004.
Below is my code
Thanks,
Code
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 03/02/2012 by salman1
'
'
Dim lastrow As Long, lastcolumn As Long
Sheets("Data Dump Tab").Select
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
lastcolumn = Cells(1, Columns.Count).End(xlToLeft).Column
Sheets("Data Dump Tab").Select
Range("AP8").Select
ActiveCell.FormulaR1C1 = "=RC[-40]&RC[-41]"
Selection.AutoFill Destination:=Range("AP8:AP" & lastrow), Type:=xlFillDefault
Sheets("Week 1").Select
Range("A4:O52").Select
Application.CutCopyMode = False
Selection.ClearContents
Sheets("Data Dump Tab").Select
With Sheet1
.Range("AP1:AP" & lastrow).AutoFilter Field:=1, Criteria1:=Sheets("Week 1").Range("K2")
.Range("A8", Cells(lastrow, lastcolumn)).SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheet3.Range("A4")
End With
Sheets("Week 1").Select
Range("F4:G52").Copy
Range("D4").Select
ActiveSheet.Paste
Range("F4:O52").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A4:O52").Select
Selection.Interior.ColorIndex = 35
Range("F4").Select
ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-1],'Data Dump Tab'!C[1]:C[2],2,FALSE)),"""",VLOOKUP(RC[-1],'Data Dump Tab'!C[1]:C[2],2,FALSE))"
Selection.AutoFill Destination:=Range("F4:F52"), Type:=xlFillDefault
Range("G4").Select
ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-2],'Data Dump Tab'!C[0]:C[2],3,FALSE)),"""",VLOOKUP(RC[-2],'Data Dump Tab'!C[0]:C[2],3,FALSE))"
Selection.AutoFill Destination:=Range("G4:G52"), Type:=xlFillDefault
Range("I4").Select
ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-4],'Data Dump Tab'!C[-2]:C[3],6,FALSE)),"""",VLOOKUP(RC[-4],'Data Dump Tab'!C[-2]:C[3],6,FALSE))"
Selection.AutoFill Destination:=Range("I4:I52"), Type:=xlFillDefault
Range("J4").Select
ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-5],'Data Dump Tab'!C[-3]:C[3],7,FALSE)),"""",VLOOKUP(RC[-5],'Data Dump Tab'!C[-3]:C[3],7,FALSE))"
Selection.AutoFill Destination:=Range("J4:J52"), Type:=xlFillDefault
Range("K4").Select
ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-6],'Data Dump Tab'!C[-4]:C[-1],4,FALSE)),"""",VLOOKUP(RC[-6],'Data Dump Tab'!C[-4]:C[-1],4,FALSE))"
Selection.AutoFill Destination:=Range("K4:K52"), Type:=xlFillDefault
Range("L4").Select
ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-7],'Data Dump Tab'!C[-5]:C[-1],5,FALSE)),"""",VLOOKUP(RC[-7],'Data Dump Tab'!C[-5]:C[-1],5,FALSE))"
Selection.AutoFill Destination:=Range("L4:L52"), Type:=xlFillDefault
Cells.Select
Cells.EntireColumn.AutoFit
'ANGLIA END
'-----------------------------------------------------------------------------------------------------------------------------------------
'LNE
Sheets("Week 1").Select
Range("A58:O106").Select
Application.CutCopyMode = False
Selection.ClearContents
Sheets("Data Dump Tab").Select
With Sheet1
.Range("AP1:AP" & lastrow).AutoFilter Field:=1, Criteria1:=Sheets("Week 1").Range("K56")
.Range("A8", Cells(lastrow, lastcolumn)).SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheet3.Range("A58")
End With
Sheets("Week 1").Select
Range("F58:G106").Copy
Range("D58").Select
ActiveSheet.Paste
Range("F58:O106").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A58:O106").Select
Selection.Interior.ColorIndex = 40
Range("F58").Select
ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-1],'Data Dump Tab'!C[1]:C[2],2,FALSE)),"""",VLOOKUP(RC[-1],'Data Dump Tab'!C[1]:C[2],2,FALSE))"
Selection.AutoFill Destination:=Range("F58:F106"), Type:=xlFillDefault
Range("G58").Select
ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-2],'Data Dump Tab'!C[0]:C[2],3,FALSE)),"""",VLOOKUP(RC[-2],'Data Dump Tab'!C[0]:C[2],3,FALSE))"
Selection.AutoFill Destination:=Range("G58:G106"), Type:=xlFillDefault
Range("I58").Select
ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-4],'Data Dump Tab'!C[-2]:C[3],6,FALSE)),"""",VLOOKUP(RC[-4],'Data Dump Tab'!C[-2]:C[3],6,FALSE))"
Selection.AutoFill Destination:=Range("I58:I106"), Type:=xlFillDefault
Range("J58").Select
ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-5],'Data Dump Tab'!C[-3]:C[3],7,FALSE)),"""",VLOOKUP(RC[-5],'Data Dump Tab'!C[-3]:C[3],7,FALSE))"
Selection.AutoFill Destination:=Range("J58:J106"), Type:=xlFillDefault
Range("K58").Select
ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-6],'Data Dump Tab'!C[-4]:C[-1],4,FALSE)),"""",VLOOKUP(RC[-6],'Data Dump Tab'!C[-4]:C[-1],4,FALSE))"
Selection.AutoFill Destination:=Range("K58:K106"), Type:=xlFillDefault
Range("L58").Select
ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-7],'Data Dump Tab'!C[-5]:C[-1],5,FALSE)),"""",VLOOKUP(RC[-7],'Data Dump Tab'!C[-5]:C[-1],5,FALSE))"
Selection.AutoFill Destination:=Range("L58:L106"), Type:=xlFillDefault
Cells.Select
Cells.EntireColumn.AutoFit
'LNE END
'------------------------------------------------------------------------------------------------------------------------------------------
'LNW START
Sheets("Week 1").Select
Range("A112:O160").Select
Application.CutCopyMode = False
Selection.ClearContents
Sheets("Data Dump Tab").Select
With Sheet1
.Range("AP1:AP" & lastrow).AutoFilter Field:=1, Criteria1:=Sheets("Week 1").Range("K110")
.Range("A8", Cells(lastrow, lastcolumn)).SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheet3.Range("A112")
End With
Sheets("Week 1").Select
Range("F112:G160").Copy
Range("D112").Select
ActiveSheet.Paste
Range("F112:O160").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A112:O160").Select
Selection.Interior.ColorIndex = 36
Range("F112").Select
ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-1],'Data Dump Tab'!C[1]:C[2],2,FALSE)),"""",VLOOKUP(RC[-1],'Data Dump Tab'!C[1]:C[2],2,FALSE))"
Selection.AutoFill Destination:=Range("F112:F160"), Type:=xlFillDefault
Range("G112").Select
ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-2],'Data Dump Tab'!C[0]:C[2],3,FALSE)),"""",VLOOKUP(RC[-2],'Data Dump Tab'!C[0]:C[2],3,FALSE))"
Selection.AutoFill Destination:=Range("G112:G160"), Type:=xlFillDefault
Range("I112").Select
ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-4],'Data Dump Tab'!C[-2]:C[3],6,FALSE)),"""",VLOOKUP(RC[-4],'Data Dump Tab'!C[-2]:C[3],6,FALSE))"
Selection.AutoFill Destination:=Range("I112:I160"), Type:=xlFillDefault
Range("J112").Select
ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-5],'Data Dump Tab'!C[-3]:C[3],7,FALSE)),"""",VLOOKUP(RC[-5],'Data Dump Tab'!C[-3]:C[3],7,FALSE))"
Selection.AutoFill Destination:=Range("J112:J160"), Type:=xlFillDefault
Range("K112").Select
ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-6],'Data Dump Tab'!C[-4]:C[-1],4,FALSE)),"""",VLOOKUP(RC[-6],'Data Dump Tab'!C[-4]:C[-1],4,FALSE))"
Selection.AutoFill Destination:=Range("K112:K160"), Type:=xlFillDefault
Range("L112").Select
ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-7],'Data Dump Tab'!C[-5]:C[-1],5,FALSE)),"""",VLOOKUP(RC[-7],'Data Dump Tab'!C[-5]:C[-1],5,FALSE))"
Selection.AutoFill Destination:=Range("L112:L160"), Type:=xlFillDefault
Cells.Select
Cells.EntireColumn.AutoFit
'LNW END
'-----------------------------------------------------------------------------------------------------------------------------------
End Sub
Display More