Excel - VBA - workbook/worksheet navigation

[C Brandt]

I'm having some basic difficulty nagivating through books and sheets.
I am in a workbook and intending on copying and pasting a couple of pages
form another wookbook into this workbook.
Using "GetOpenFilename" I identify and subsequently open a workbook. Copy
all cells from a sheet and paste values into my calling workbook. This works
just fine.
Question #1: Is there a way to identify the name and path of the calling
workbook?
I currently, have it hard coded, but since the name changes as I rev the
sheet, it is labor intensive.

I then want to go back to the called workbook, open another sheet and
copy/paste it back into the calling wookbook.
When I activate the workbook containing the data, it says the Subscript is
out-of-range.
Question # 2: Why am I getting the subscript out of range?

Thanks for any help,

Craig


' Request current TradeSheet filename
TSFileName = Application.GetOpenFilename
Workbooks.Open (TSFileName)
Sheets("Funds").Select
' Copy the sheet
Cells.Copy
' Bring back original sheet and paste values into this sheet
Workbooks("Account Review-Rev c.xls").Activate ' SEE QUESTION # 1
Sheets("Funds_DL").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues

' Message the sheet (deleted)


' Copy over Buy Sheet

Workbooks(TSFileName).Activate ' SEE QUESTION # 2
Sheets("Buy").Select
Cells.Copy
Workbooks("Account Review-Rev c.xls").Activate
Sheets("Buy").Select
Selection.PasteSpecial Paste:=xlPasteValues

 

[Dave Peterson]

Sometimes, it's just easier to drop the .selects and .activates and use object
variables to represent what you want.

Untested:

Dim TSFileName as variant
Dim TSFundsWks as worksheet
Dim TSBuyWks as worksheet
Dim DestFundwks as worksheet
Dim DestBuyWks as worksheet

with workbooks("Account Review-Rev c.xls")
'or maybe...
'with activeworkbook 'if that workbook is active when you start the macro
set destfundwks = .worksheets("Funds_DL")
set destBuyWks = .worksheets("Buy")
end with

TSFileName = Application.GetOpenFilename
if tsfilename = false then
exit sub 'user hit cancel
end if
set tsfundwks = Workbooks.Open(filename:=TSFileName).worksheets("Funds")
set tsbuywks = tsfundwks.parent.worksheets("Buy")

tsfundwks.cells.copy
destfundwks.range("A1").pastespecial paste:=xlpastevalues

tsbuywks.cells.copy
destbuywks.range("A1").pastespecial paste:=xlpastevalues