Reference to a variable in windows(???).Activate

[John Baker]

Hi:

I am having a minor syntax problem.

I wish to store the name and path of the spreadsheet I am in( which is variable), and copy
some data from a spreadsheet that has a known name and location. I am attempting to save
the file name and path of the sheet I am on, and then switch back and forth between the
two sheets using the windows.activate function. The problem is that i don't know the
correct syntax to refer to a variable in that command. The code is as follows (in part):


Application.ScreenUpdating = False

Sheets("Input").Select
installpath = ActiveWorkbook.Path
installname = ActiveWorkbook.Name
installfile = installpath &/& installname <----VARIABLE NAMED HERE

Workbooks.Open(FileName:="C:\ipt\timesheets\Timesheet.xls").RunAutoMacros _
Which:=xlAutoOpen


Sheets("input").Select
Range("C57").Select
Selection.Copy
Windows(installfile).Activate <<<<----THIS IS WHERE THE PROBLEM IS!
Range("C5").Select


Can someone please tell me the correct way to refer to this variable field?

Incidentally, could I have used "ActiveWorkbook.FullName" and got the whole name and path
in one command?


Thanks a lot

John Baker

 

[Frank Kabel]

Hi John
in general no need for using Select statements or the names of the
workbooks. Use object references. e.g. try something like

sub foo()
dim source_wbk as workbook
dim source_wks as worksheet
dim target_wkb as workbook
dim target_wks as worksheet
dim installfile

installfile = installpath &/& installname 'adapt to your needs
set target_wbk = activeworkbook
set target_wks = target_wbk.worksheets("Input")
workbooks.open installfile
set source_wbk = activeworkbook
set source_wks=source_wbk.worksheets("Input")

source_wks.range("C57").copy
target_wks.paste
end sub

 

[Norman Jones]

Hi John,

installfile = installpath &/& installname <----VARIABLE NAMED HERE

Try:

installfile = installpath & "/" & installname

or, perhaps:

installfile = ActiveWorkbook.FullName


---
Regards,
Norman

Hi:

I am having a minor syntax problem.

I wish to store the name and path of the spreadsheet I am in( which is variable), and copy
some data from a spreadsheet that has a known name and location. I am attempting to save
the file name and path of the sheet I am on, and then switch back and forth between the
two sheets using the windows.activate function. The problem is that i don't know the
correct syntax to refer to a variable in that command. The code is as follows (in part):


Application.ScreenUpdating = False

Sheets("Input").Select
installpath = ActiveWorkbook.Path
installname = ActiveWorkbook.Name
installfile = installpath &/& installname <----VARIABLE NAMED HERE

Workbooks.Open(FileName:="C:\ipt\timesheets\Timesheet.xls").RunAutoMacros _

 

[Frank Kabel]

Hi
and change the line
installfile = installpath & "\" & installname
to
installfile = installpath &/& installname

 

[John Baker]

FranK;

Thank you very much for the useful response. It works finer EXCEPT for the last line:
target_wks.paste

Somehow it appears to want other parameters (range "C57"perhaps) , and I am not certain
how to set them up. You advice would be appreciated.

Thanks

John Baker

 

[Frank Kabel]

Hi
not testedt but try instead of
source_wks.range("C57").copy
target_wks.paste


source_wks.range("C57").copy
target_wks.paste Destination:=target_wks.range("C57")

or try
source_wks.range("C57").copy Destination:=target_wks.range("C57")

 

[Frank Kabel]

Hi
not testedt but try instead of
source_wks.range("C57").copy
target_wks.paste


source_wks.range("C57").copy
target_wks.paste Destination:=target_wks.range("C57")

or try
source_wks.range("C57").copy Destination:=target_wks.range("C57")

 

[John Baker]

Frank:

Thanks,

source_wks.range("C57").copy Destination:=target_wks.range("C57")

Worked.

JOhn