Variable spreadsheet name in recorded macro

[UHsoccer]

I record a macro and the active file name is "Report". The spreadsheet is retained in a check-in check-out system and in version 9 of Excel it includes the checkout vesion in the name, such as "Report (16.1)". While in my Excel version 10 it does not and displays "Report"

The macro works fime for me because the file name is always "Report"

When a user who is on Excel version 9, it checks out as "Report (16.1)" and the macro fails

How can I generalize the macro to make sure that the name is the actual file name?

 

[Barrie Davidson]

Can you post your code (someone will then be able to help you out)?

 

[TommyGun]

Lots of different methods to do this...however the easiest to use is to have your code located within the report workbook, then you can access the workbooks name like this...

ThisWorkbook.Name

As an addition to this, you can also use this object instead of the typical Workbooks(filename) convention to do your stuff.

ThisWorkbook.Sheets(1).Range("A1") = "See it works!"

 

[UHsoccer]

Here is the code

Windows("Reporting.xls").Activate
Sheets("Sheet1").Select


Windows("revenue.xls").Activate
Range("H2,N2,T2").Select
Range("T2").Activate
Selection.Copy

This works for Excel V10

In Excel V9, the name "Reporting.xls" changes every time the file is checked out. Thus it will need to be like:

Windows("Reporting (10.1).xls").Activate

after that

Windows("Reporting (11.1).xls").Activate

etc

 

[Barrie Davidson]

What are you doing with your code? It appears to be only copying H2, N2, and T2 from your Revenue workbook (in the active sheet of that workbook).

 

[UHsoccer]

I only posted a small segment of the code, the rest of the code copies various cells (from two different spreasdsheets) to the "Report.xls" file.

I need to store the name of that file, regardless of what it is. Thus, at the beginning I want to save the active file name and be able to refer to it at a later time via a variable name

 

[Barrie Davidson]

Well, if you want to set a variable equal to your active workbook, you could use something like this:

Dim ReportingWorkbook As Workbook

Set ReportingWorkbook = ActiveWorkbook
ReportingWorkbook.Sheets("Sheet1").Select
'etc.

Does this help?