I am creating a Macro in a MASTER.xls file which contains default data.
This master.xls file is then copied into a weekly file where daily financial
information is entered and sent to me at the home office from many field
locations.
I am creating this macro in the master.xls file, which will be copied into
the weekly file and then used to calculate bonus information, which will be
written into a static second file that is kept at each business location.
What I need is a way to have the macro run and use the data from each weekly
file and it looks like the best way to do this is with the use of the
"thisworkbook" convention.
I am very new to macros and have successfully "recorded" the macro to run
the way I want. But in the macro, I am referring to the specific worksheet
name where the data is coming from. So I need help and maybe an example of
how to CHANGE the VB macro language to use the "thisworkbook" function and
replace the actual name of the workbook I've recorded.
The line is currently Windows("bonus test2").Activate
Thanks in advance for your help.

Thanks,
But each week, they will open the MASTER.xls workbook and then save it with
a new file name reflecting the new week, i.e., BFP-DALLAS-05-27-05.
When they have the BFP file open for that week and finish putting all the
financial data in, I want to run the macro to copy out cells that have
computed bonus totals in them into another workbook. The macro inserts a new
row and uses the same cells each week, but the workbook name changes
everyweek. That is why I want something generic to run from the workbook they
currently have open.
Does this help?

"K Dales" wrote:

ThisWorkbook will always refer to the active workbook. But you can use the
syntax Workbooks("WorkbookName") to refer to any open workbook.
"WorkbookName" would be the title of the workbook as it appears in the
Windows Title Bar; e.g. "Book1", or "MASTER", etc.

"David" wrote:

I am creating a Macro in a MASTER.xls file which contains default data.
This master.xls file is then copied into a weekly file where daily financial
information is entered and sent to me at the home office from many field
locations.
I am creating this macro in the master.xls file, which will be copied into
the weekly file and then used to calculate bonus information, which will be
written into a static second file that is kept at each business location.
What I need is a way to have the macro run and use the data from each weekly
file and it looks like the best way to do this is with the use of the
"thisworkbook" convention.
I am very new to macros and have successfully "recorded" the macro to run
the way I want. But in the macro, I am referring to the specific worksheet
name where the data is coming from. So I need help and maybe an example of
how to CHANGE the VB macro language to use the "thisworkbook" function and
replace the actual name of the workbook I've recorded.
The line is currently Windows("bonus test2").Activate
Thanks in advance for your help.

Here is the macro. The file name I want to make "ThisWorkbook" is Bonus
Test2.xls. The file BonusTotals.xls is fixed.

Sub PostBonus()
'
' PostBonus Macro
' Macro recorded 5/27/2005 by dperkins
'
' Keyboard Shortcut: Ctrl+b
'
Workbooks.Open Filename:= _
"F:\Franchise_GPC\Ben Franklin Info\Ben Big Pictures
2005\BonusTotals.xls"
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Range("A3").Select
Windows("Bonus Test2.xls").Activate
Range("E6").Select
Selection.Copy
Windows("BonusTotals.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("B3").Select
Windows("Bonus Test2.xls").Activate
Sheets("P1").Select
Range("H35").Select
Application.CutCopyMode = False
Selection.Copy
Windows("BonusTotals.xls").Activate
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("Bonus Test2.xls").Activate
Sheets("P2").Select
Range("H35").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Bonus Test2.xls").Activate
Range("C3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("Bonus Test2.xls").Activate
Sheets("P3").Select
Range("H35").Select
Application.CutCopyMode = False
Selection.Copy
Windows("BonusTotals.xls").Activate
Range("D3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("Bonus Test2.xls").Activate
Sheets("P4").Select
ActiveWindow.SmallScroll Down:=16
Range("H35").Select
Application.CutCopyMode = False
Selection.Copy
Windows("BonusTotals.xls").Activate
Range("E3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

I got it!!!
Here is the line in question:
Windows("Bonus Test2.xls").Activate
I replaced with this and it works perfect!
ThisWorkbook.Activate

As simple as that!! Thanks again for the point in the right direction!!
David

"David" wrote:

Here is the macro as it currenly exists...The fixed file is BonusTotals.xls.
The file that will change that I want to make "ThisWorkbook" is Bonus
Test2.xls.

Sub PostBonus()
'
' PostBonus Macro
' Macro recorded 5/27/2005 by dperkins
'
' Keyboard Shortcut: Ctrl+b
'
Workbooks.Open Filename:= _
"F:\Franchise_GPC\Ben Franklin Info\Ben Big Pictures
2005\BonusTotals.xls"
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Range("A3").Select
Windows("Bonus Test2.xls").Activate
Range("E6").Select
Selection.Copy
Windows("BonusTotals.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("B3").Select
Windows("Bonus Test2.xls").Activate
Sheets("P1").Select
Range("H35").Select
Application.CutCopyMode = False
Selection.Copy
Windows("BonusTotals.xls").Activate
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("Bonus Test2.xls").Activate
Sheets("P2").Select
Range("H35").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Bonus Test2.xls").Activate
Range("C3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("Bonus Test2.xls").Activate
Sheets("P3").Select
Range("H35").Select
Application.CutCopyMode = False
Selection.Copy
Windows("BonusTotals.xls").Activate
Range("D3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("Bonus Test2.xls").Activate
Sheets("P4").Select
ActiveWindow.SmallScroll Down:=16
Range("H35").Select
Application.CutCopyMode = False
Selection.Copy
Windows("BonusTotals.xls").Activate
Range("E3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

"K Dales" wrote in message
...
ThisWorkbook will always refer to the active workbook.

Nope. It will always refer to the workbook containing the macro.

--

Vasant

ThisWorkbook will always refer to the active workbook.

This is incorrect. ThisWorkbook always refers to the workbook
containing the code, regardless of what workbook is active.
ActiveWorkbook refers to the active workbook, regardless of what
workbook contains the code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"K Dales" wrote in message
...
ThisWorkbook will always refer to the active workbook. But you
can use the
syntax Workbooks("WorkbookName") to refer to any open workbook.
"WorkbookName" would be the title of the workbook as it appears
in the
Windows Title Bar; e.g. "Book1", or "MASTER", etc.

"David" wrote:

I am creating a Macro in a MASTER.xls file which contains
default data.
This master.xls file is then copied into a weekly file where
daily financial
information is entered and sent to me at the home office from
many field
locations.
I am creating this macro in the master.xls file, which will be
copied into
the weekly file and then used to calculate bonus information,
which will be
written into a static second file that is kept at each
business location.
What I need is a way to have the macro run and use the data
from each weekly
file and it looks like the best way to do this is with the use
of the
"thisworkbook" convention.
I am very new to macros and have successfully "recorded" the
macro to run
the way I want. But in the macro, I am referring to the
specific worksheet
name where the data is coming from. So I need help and maybe
an example of
how to CHANGE the VB macro language to use the "thisworkbook"
function and
replace the actual name of the workbook I've recorded.
The line is currently Windows("bonus test2").Activate
Thanks in advance for your help.