Opening Closed Worksheets with VBA
 

Please Help!

It seems like I have tried everything. I have a worksheet with a
module that has the following VBA:

Sub GetFile()
Sheets("Menu").Select
PathName = Range("C1").Value
Filename = Range("C2").Value
TabName = Range("C3").Value
ControlFile = ActiveWorkbook.Name
Workbooks.Open Filename:=PathName & Filename
ActiveSheet.Name = TabName
Sheets(TabName).Copy After:=Workbooks(ControlFile).Sheets(2)
Windows(Filename).Activate
ActiveWorkbook.Close SaveChanges:=False
Windows(ControlFile).Activate
Sheets("Menu").Select
End Sub

When I click the button on the sheet named "Menu" it opens the other
workbook, but grabs the sheet that was last viewed before it was saved
and closed. There are 2 sheets from the workbook I want to copy and
paste when this button is clicked. How do I designate or tell Excel
which exact tab to grab, and can I have 2 of them grabbed at the same
time??

thanks!!!!

Opening Closed Worksheets with VBA
 

Try this where

c1=c:\yourfolder\
c2=aatest.xls
c3=funds
c4=cash


Sub GetFile_Don()
With Sheets("menu")
PathName = .Range("C1").Value
Filename = .Range("C2").Value
tabname1 = .Range("C3").Value
tabname2 = .Range("C4").Value
End With
ControlFile = ActiveWorkbook.Name
Workbooks.Open Filename:=PathName & Filename
With Workbooks(Filename)
.Sheets(tabname1).Copy After:=Workbooks(ControlFile).Sheets(2)
.Sheets(tabname2).Copy After:=Workbooks(ControlFile).Sheets(2)
End With
Windows(Filename).Activate
ActiveWorkbook.Close SaveChanges:=False
Sheets("menu").Select
End Sub
--
Don Guillett
SalesAid Software

wrote in message
oups.com...
Please Help!

It seems like I have tried everything. I have a worksheet with a
module that has the following VBA:

Sub GetFile()
Sheets("Menu").Select
PathName = Range("C1").Value
Filename = Range("C2").Value
TabName = Range("C3").Value
ControlFile = ActiveWorkbook.Name
Workbooks.Open Filename:=PathName & Filename
ActiveSheet.Name = TabName
Sheets(TabName).Copy After:=Workbooks(ControlFile).Sheets(2)
Windows(Filename).Activate
ActiveWorkbook.Close SaveChanges:=False
Windows(ControlFile).Activate
Sheets("Menu").Select
End Sub

When I click the button on the sheet named "Menu" it opens the other
workbook, but grabs the sheet that was last viewed before it was saved
and closed. There are 2 sheets from the workbook I want to copy and
paste when this button is clicked. How do I designate or tell Excel
which exact tab to grab, and can I have 2 of them grabbed at the same
time??

thanks!!!!