Hi,
I am trying to write a macro to open up 4 different workbooks (1 at a time), filter the data based on a given criteria, and then select a range of visible cells only, and then copy and past them into a master workbook.
The issue is with the selection of visible cells. If there is no data in the worksheet then the selection will consist of visible cells only and it generates an error:
Run-time error '1004':
no cells were found.
I used a On Error Goto 10 statement and made it past the first occurence of this error, tried using a On Error Goto 11 on the next file but it still stops.
At the risk of looking like a putz with my first attempt at VB, I have attached the code that I wrote for this macro:
Sub FILEIMPORT()
'
' FILEIMPORT Macro
' Macro recorded 31/01/2008 by Sean Bell
'
Dim pricingdir As String
Dim categorydir As String
Dim skudir As String
Dim reportingdir As String
Dim pricingfile As String
Dim categoryfile As String
Dim skufile As String
Dim reportingfile As String
Dim demandgroup As String
Dim mastername As String
Sheets("Update Tab").Activate
pricingdir = Worksheets("Update Tab").Range("PRICING_DIR")
categorydir = Worksheets("Update Tab").Range("CATEGORY_DIR")
skudir = Worksheets("Update Tab").Range("SKU_DIR")
reportingdir = Worksheets("Update Tab").Range("REPORTING_DIR")
demandgroup = Worksheets("Update Tab").Range("DEMAND_GROUP")
pricingfile = Worksheets("Update Tab").Range("PRICING_FILE")
categoryfile = Worksheets("Update Tab").Range("CATEGORY_FILE")
skufile = Worksheets("Update Tab").Range("SKU_FILE")
reportingfile = Worksheets("Update Tab").Range("REPORTING_FILE")
demandgroup = Worksheets("Update Tab").Range("DEMAND_GROUP")
mastername = Worksheets("Update Tab").Range("MASTER_NAME")
Sheets("Food_Category").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A2:BE1921").Select
Selection.ClearContents
Sheets("Food_SKU").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A2:BG129").Select
Selection.ClearContents
Sheets("Food_Reporting").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A2:BD1921").Select
Selection.ClearContents
Sheets("Food_Pricing").Select
Range("A2:E2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Sheets("Update Tab").Select
Windows(categoryfile).Activate
Range("C1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:=demandgroup
Range("A1").Select
ActiveWindow.LargeScroll Down:=-1
Range(Selection, Selection.End(xlDown)).Select
Range("A4:BE1892").Select
On Error GoTo 10
Selection.SpecialCells(xlCellTypeVisible).Select
Application.CutCopyMode = False
Selection.Copy
Windows(mastername).Activate
Sheets("FOOD_CATEGORY").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
10 Windows(pricingfile).Activate
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A2:E44").Select
Application.CutCopyMode = False
Selection.Copy
Windows(mastername).Activate
Sheets("Food_Pricing").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(reportingfile).Activate
Range("C1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:=demandgroup
Range("A1").Select
ActiveWindow.LargeScroll Down:=-1
Range(Selection, Selection.End(xlDown)).Select
Range("A4:BD1892").Select
On Error GoTo 11
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Windows(mastername).Activate
Sheets("Food_Reporting").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
11 Windows(skufile).Activate
Range("E1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:=demandgroup
Range("A1").Select
ActiveWindow.LargeScroll Down:=-1
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range("A4:BG100").Select
On Error GoTo 12
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Windows(mastername).Activate
Sheets("Food_SKU").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
12 Windows(mastername).Activate
Sheets("Update Tab").Select
End Sub
Thanks in advance for your assistance.
I am trying to write a macro to open up 4 different workbooks (1 at a time), filter the data based on a given criteria, and then select a range of visible cells only, and then copy and past them into a master workbook.
The issue is with the selection of visible cells. If there is no data in the worksheet then the selection will consist of visible cells only and it generates an error:
Run-time error '1004':
no cells were found.
I used a On Error Goto 10 statement and made it past the first occurence of this error, tried using a On Error Goto 11 on the next file but it still stops.
At the risk of looking like a putz with my first attempt at VB, I have attached the code that I wrote for this macro:
Sub FILEIMPORT()
'
' FILEIMPORT Macro
' Macro recorded 31/01/2008 by Sean Bell
'
Dim pricingdir As String
Dim categorydir As String
Dim skudir As String
Dim reportingdir As String
Dim pricingfile As String
Dim categoryfile As String
Dim skufile As String
Dim reportingfile As String
Dim demandgroup As String
Dim mastername As String
Sheets("Update Tab").Activate
pricingdir = Worksheets("Update Tab").Range("PRICING_DIR")
categorydir = Worksheets("Update Tab").Range("CATEGORY_DIR")
skudir = Worksheets("Update Tab").Range("SKU_DIR")
reportingdir = Worksheets("Update Tab").Range("REPORTING_DIR")
demandgroup = Worksheets("Update Tab").Range("DEMAND_GROUP")
pricingfile = Worksheets("Update Tab").Range("PRICING_FILE")
categoryfile = Worksheets("Update Tab").Range("CATEGORY_FILE")
skufile = Worksheets("Update Tab").Range("SKU_FILE")
reportingfile = Worksheets("Update Tab").Range("REPORTING_FILE")
demandgroup = Worksheets("Update Tab").Range("DEMAND_GROUP")
mastername = Worksheets("Update Tab").Range("MASTER_NAME")
Sheets("Food_Category").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A2:BE1921").Select
Selection.ClearContents
Sheets("Food_SKU").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A2:BG129").Select
Selection.ClearContents
Sheets("Food_Reporting").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A2:BD1921").Select
Selection.ClearContents
Sheets("Food_Pricing").Select
Range("A2:E2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Sheets("Update Tab").Select
Windows(categoryfile).Activate
Range("C1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:=demandgroup
Range("A1").Select
ActiveWindow.LargeScroll Down:=-1
Range(Selection, Selection.End(xlDown)).Select
Range("A4:BE1892").Select
On Error GoTo 10
Selection.SpecialCells(xlCellTypeVisible).Select
Application.CutCopyMode = False
Selection.Copy
Windows(mastername).Activate
Sheets("FOOD_CATEGORY").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
10 Windows(pricingfile).Activate
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A2:E44").Select
Application.CutCopyMode = False
Selection.Copy
Windows(mastername).Activate
Sheets("Food_Pricing").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(reportingfile).Activate
Range("C1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:=demandgroup
Range("A1").Select
ActiveWindow.LargeScroll Down:=-1
Range(Selection, Selection.End(xlDown)).Select
Range("A4:BD1892").Select
On Error GoTo 11
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Windows(mastername).Activate
Sheets("Food_Reporting").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
11 Windows(skufile).Activate
Range("E1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:=demandgroup
Range("A1").Select
ActiveWindow.LargeScroll Down:=-1
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range("A4:BG100").Select
On Error GoTo 12
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Windows(mastername).Activate
Sheets("Food_SKU").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
12 Windows(mastername).Activate
Sheets("Update Tab").Select
End Sub
Thanks in advance for your assistance.