Variable in filename not increasing incrementally

[kestefon]

So, I have a macro that copies sheets to new files and saves the new files with incrementally increasing names. So, the first file would be "LocalizedData1", then second would be "...2", etc.

I'm able to successfully save the first file, but later in the program, when the "pageCounter" variable has increased, the FName variable remains unchanged.

    Dim FName           As String
    Dim FPath           As String
  
 
    FPath = "C:\LocalizationOutput"
    FName = "LocalizedData" & pageCounter & ".xls"
 
     
    ThisWorkbook.Sheets("Localized" & pageCounter).Move Before:=Workbooks.Add.Sheets(1)

    Application.DisplayAlerts = False
    ActiveWorkbook.Sheets("Sheet1").Delete
    Application.DisplayAlerts = True
    
    If Dir(FPath & "\" & FName) <> "" Then
        MsgBox "File " & FPath & "\" & FName & " already exists. Exiting macro."
    Else
        ActiveWorkbook.SaveAs fileName:=FPath & "\" & FName
    End If

[judgeh59]

I guess I don't see a loop or where you are incrementing pagecounter....

[kestefon]

That code is in a different section (it's a really long macro). I ended up putting this in again after the pageCounter increase: FName = "LocalizedData" & pageCounter & ".xls"

[judgeh59]

Have you stepped (F8) through the code and opened the locals window....this will allow you to see the variable incrementing or not.....

[mc84excel]

Does changing the pageCounter to Static help?

[judgeh59]

I'm wondering if the variable is global vs local....

@mc84excel - is that what you mean by static?

[mc84excel]

Declaring variables as static retains the variable value after the procedure containing the variable ends. See http://msdn.microsoft.com/en-us/libr...v=vs.110).aspx

How and where is the variable pageCounter being declared?

[mc84excel]

Re your comment about global vs local. I learnt a lot from Chip Pearsons explanation of scope of variables - highly recommend it as a refresher. See http://www.cpearson.com/excel/scope.aspx

(Resources I've read always identify 3 scope levels. Chip identifies 4 )

[judgeh59]

@mc84excel - makes sense.....I learn them as Global vs local....either way I agree with your idea....

[Solus Rankin]

@mc84excel

Isn't that for VB and not VBA?

Global and local is at what level you declare them. As in if they can be used by multple procedures, functions etc.

[mc84excel]

@mc84excel

Isn't that for VB and not VBA?

Global and local is at what level you declare them. As in if they can be used by multple procedures, functions etc.

No, it's for VBA. Most books etc. describe three levels of scopes (local, module, global). Chip adds a 4th level 'Project'. (Project is like Global except that it can only be referenced within the same project. Whereas Global can be referenced from another open project).

[judgeh59]

great idea - thanks

[Solus Rankin]

Those aren't static. Static (and the link to MS you posted) are used in VB. I don't believe it functions the same way in VBA. At least I haven't found anything to show me it has.

[mc84excel]

Those aren't static. Static (and the link to MS you posted) are used in VB. I don't believe it functions the same way in VBA. At least I haven't found anything to show me it has.

Oh. I thought you were questioning the levels of scope - so I was answering that.

Well I haven't used Static Variables myself in VBA but I have read about them in at least one * book on VBA ('Excel 2010 Power Programming with VBA' author John Walkenbach; See page 206)

We are slightly getting off the OPs question.



* at least one = I have a vague recollection of reading them elsewhere. Just can't remember at the moment.

[Solus Rankin]

I don't see where pageCounter is declared. To trouble shoot we'd really need to whole code, or better yet an example workbook with code in place.