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\Timesh eet.xls").RunAutoMacros _
Which:=xlAutoOpen


Sheets("input").Select
Range("C5:D7").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

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("C5:D7").copy
target_wks.paste
end sub


--
Regards
Frank Kabel
Frankfurt, Germany


John Baker wrote:
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\Timesh eet.xls").RunAutoMacr
os
_ Which:=xlAutoOpen


Sheets("input").Select
Range("C5:D7").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

Hi John,

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

Try:

installfile = installpath & "/" & installname

or, perhaps:

installfile = ActiveWorkbook.FullName


---
Regards,
Norman



"John Baker" wrote in message
...
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\Timesh eet.xls").RunAutoMacros _
Which:=xlAutoOpen


Sheets("input").Select
Range("C5:D7").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

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

--
Regards
Frank Kabel
Frankfurt, Germany


Frank Kabel wrote:
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("C5:D7").copy
target_wks.paste
end sub



John Baker wrote:
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\Timesh eet.xls").RunAutoMacr
os
_ Which:=xlAutoOpen


Sheets("input").Select
Range("C5:D7").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;

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 "C5:D7"perhaps) , and I am not certain
how to set them up. You advice would be appreciated.

Thanks

John Baker


"Frank Kabel" wrote:

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("C5:D7").copy
target_wks.paste
end sub

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


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

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

--
Regards
Frank Kabel
Frankfurt, Germany


John Baker wrote:
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 "C5:D7"perhaps) ,
and I am not certain how to set them up. You advice would be
appreciated.

Thanks

John Baker


"Frank Kabel" wrote:

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("C5:D7").copy
target_wks.paste
end sub

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


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

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


--
Regards
Frank Kabel
Frankfurt, Germany


John Baker wrote:
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 "C5:D7"perhaps) ,
and I am not certain how to set them up. You advice would be
appreciated.

Thanks

John Baker


"Frank Kabel" wrote:

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("C5:D7").copy
target_wks.paste
end sub

Frank:

Thanks,

source_wks.range("C5:D7").copy Destination:=target_wks.range("C5:D7")

Worked.

JOhn



"Frank Kabel" wrote:


source_wks.range("C5:D7").copy Destination:=target_wks.range("C5:D7")