Changing the activation of a sheet that is unactive and undefined
 

Hello, I'm actually a new poster. I did some searching on previous
posts and couldn't find an answer to my problem. The broad scope of
what I'm trying to do is in an excel file, to be able to push a button
that will open the open file dialog box and then once that file is
open, copy the information on a tab and paste it onto a tab from the
original file. I've been able to do most of this. I've used the
sendkeys feature to prompt the open file dialog box. However, the
problem that I'm running into is that once the new file opens, I can't
figure out how to make that the active workbook. It keeps on referring
to the original file, which is the active workbook. Part of the reason
this is a problem is because the filename and path can be different
every time this is used, so I can't delcare the specific file name in
the code and activate it from there. It needs to be flexible and
that's why I am accessing the file through the open file dialog box.

Any help would be greatly appreciated.

Thanks,

Andy

Changing the activation of a sheet that is unactive and undefined
 

Post your code. It's much easier to offer suggestions on how to fix existing
code than to guess how you're currently doing things.

Tim

"Andyjk1" wrote in message oups.com...
Hello, I'm actually a new poster. I did some searching on previous
posts and couldn't find an answer to my problem. The broad scope of
what I'm trying to do is in an excel file, to be able to push a button
that will open the open file dialog box and then once that file is
open, copy the information on a tab and paste it onto a tab from the
original file. I've been able to do most of this. I've used the
sendkeys feature to prompt the open file dialog box. However, the
problem that I'm running into is that once the new file opens, I can't
figure out how to make that the active workbook. It keeps on referring
to the original file, which is the active workbook. Part of the reason
this is a problem is because the filename and path can be different
every time this is used, so I can't delcare the specific file name in
the code and activate it from there. It needs to be flexible and
that's why I am accessing the file through the open file dialog box.

Any help would be greatly appreciated.

Thanks,

Andy

Changing the activation of a sheet that is unactive and undefined
 

Private Sub CommandButton1_Click()
Worksheets("Date").Select
reportname = Range("a28")
SendKeys "^o", True
' Here is where I have the new file opened, but I can't figure out
' how to activate it I can copy from it and paste into the original
file
'Application.WindowState = xlMinimized
'SendKeys "^{tab}", True
'SendKeys "%{tab}", True
Worksheets("data").Select
Worksheets("Data").Range("a2").Select
'gbsname = Range("a2")
Range("a4:ag150").copy
Workbooks(reportname).Worksheets("Data").Select
Range("a4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

Changing the activation of a sheet that is unactive and undefined
 

forget sendkeys and look at

application.getopenfilename

or if you allready have the file name and path

dim wb as workbook
set wb=worbooks.open strFile

wb now is a reference to the opened file.
You should also qualify your range references more explicitly

Eg:

ThisWorkbook.Range()
not just
Range()

unqualified ranges just lead to problems...


--
Tim Williams
Palo Alto, CA


"Andyjk1" wrote in message oups.com...
Private Sub CommandButton1_Click()
Worksheets("Date").Select
reportname = Range("a28")
SendKeys "^o", True
' Here is where I have the new file opened, but I can't figure out
' how to activate it I can copy from it and paste into the original
file
'Application.WindowState = xlMinimized
'SendKeys "^{tab}", True
'SendKeys "%{tab}", True
Worksheets("data").Select
Worksheets("Data").Range("a2").Select
'gbsname = Range("a2")
Range("a4:ag150").copy
Workbooks(reportname).Worksheets("Data").Select
Range("a4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub