on error goto issue

Leske

Active Member
Joined
Aug 26, 2008
Messages
297
Hello i have the following code:
Code:
On Error GoTo Fout4
Sheets("Bev - MTD").Select
 Range("A1").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Month2").CurrentPage = CurrentMonth
   
Fout4:
this code sit's in a for "i = 1 to 10"

The first time whenever he doesn't find the sheets("Bev - MTD") he goes to Fout4:
But the next file he opens and there isn't sheets("Bev - MTD") he gots stuck.

Don't really see the problem?
Is it because it can only have once that error and can't go to Fout4 because it is in a "i = 1 to 10"?


Why does it work for one file but not for the others?

Kind regards,

Les
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,368
Office Version
2019, 2016, 2013
Platform
Windows
show the rest of your code, atleast from where the loop starts, or the whole routine
 

Leske

Active Member
Joined
Aug 26, 2008
Messages
297
Code:
Sheets("Marcel").Select
lr2 = Range("A2").End(xlDown).Row
For j = 2 To lr2
Windows("list of sending items.xlsx").Activate
Sheets("Marcel").Select
Klant = Cells(j, 1).Value
KlantOpen = Cells(j, 2).Value

KlantFileName = Cells(j, 3).Value

Workbooks.Open Filename:=BIBOpath
Sheets("Data").Select
ActiveSheet.Range("a:ar").AutoFilter Field:=1, Criteria1:="BELGIUM"

Windows(BIBOwindows).Activate
'Windows("new BI BO OOH 2016.xlsm").Activate
Sheets("Data").Select
Range("A1").Select


ActiveSheet.ShowAllData
ActiveSheet.Range("a:ar").AutoFilter Field:=37, Criteria1:=Klant

Workbooks.Open Filename:=KlantOpen


Sheets("Data").Visible = True
Sheets("Data").Select
Range("A1").Select
Cells.Select
    Selection.Delete
    
Windows(BIBOwindows).Activate
 'Windows("new BI BO OOH 2016.xlsm").Activate


 Cells.Select
      Selection.Copy
      
Windows(KlantFileName).Activate
    Range("a1").Activate
    ActiveSheet.Paste
'Application.CutCopyMode = False

 Cells.Select
  Selection.ClearFormats


CurrentMonth = Month(Date) - 1
CurrentYear = Year(Date)

On Error GoTo Fout3
Sheets("Food - MTD").Select
 Range("A1").Select
    ActiveSheet.PivotTables("PivotTable7").PivotFields("Month2").CurrentPage = CurrentMonth
   
Fout3:
   
On Error GoTo Fout4
Sheets("Bev - MTD").Select
 Range("A1").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Month2").CurrentPage = CurrentMonth
   
Fout4:

ActiveWorkbook.RefreshAll

Sheets("Data").Select
ActiveWindow.SelectedSheets.Visible = False
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,177
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Since it looks like you just want to ignore errors, replace this:
Code:
On Error GoTo Fout3
Sheets("Food - MTD").Select
 Range("A1").Select
    ActiveSheet.PivotTables("PivotTable7").PivotFields("Month2").CurrentPage = CurrentMonth
   
Fout3:
   
On Error GoTo Fout4
Sheets("Bev - MTD").Select
 Range("A1").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Month2").CurrentPage = CurrentMonth
   
Fout4:
with this:
Code:
On Error Resume Next
Sheets("Food - MTD").PivotTables("PivotTable7").PivotFields("Month2").CurrentPage = CurrentMonth
Sheets("Bev - MTD").PivotTables("PivotTable1").PivotFields("Month2").CurrentPage = CurrentMonth
On Error Goto 0
 

Watch MrExcel Video

Forum statistics

Threads
1,102,858
Messages
5,489,306
Members
407,686
Latest member
Chuck1960

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top