Getting VBA to reference the correct worksheet

[PipBoy808]

As pointed out in this thread, I'm currently trying to write a macro that will pull some data from one sheet to another. This macro will be executed weekly when finished. The problem is that there is a different sheet is active for each week in the source workbook. The worksheet tabs on the workbook are as follows:

"Wk1, Wk2, Wk3, Wk4 ... Wk44"

Each of these weekly worksheets are identical in layout and format. The only difference is the data.

My question is, how do I write into my macro that I want the code to refer to the latest week when extracting data, even when the worksheet for some weeks (e.g. Wk45-Wk52) might not exist within the source workbook yet?

Thanks alot!

 

[NARAYANK991]

Hi ,

The following statement :

Worksheets("Wk"&Application.WorksheetFunction.WeekNum(Date))

will refer to the current week's worksheet.

Excel will generate an error only if the sheet referred to , using the above statement , does not exist.

If there is a possibility that you may enter a new week , and the current week's worksheet has still not been created , then the better method may be to use a fixed cell on a master sheet to refer to the current date ; you can always enter the current date as a static value by pressing CTRL ; ( CTRL key and the semi-colon key ) ; then instead of using the system Date variable in the above statement , you can use this cell reference ; you are assured that till such time you change the date in the cell , the reference will always be to an existing worksheet.

Narayan

 

[Marc L]

Hi,

a function to verify if a sheet exists :​

Function ExistSheet(Ws$, Optional Wb As Workbook) As Boolean
         If Wb Is Nothing Then Set Wb = ActiveWorkbook
         On Error Resume Next
         ExistSheet = IsObject(Wb.Sheets(Ws))
End Function

Like it !
​