I change you second declarationof SelectFile in OpenInvoice to a different
variable name so it didn't block you public variable. And added code to
assign the name of the newly opened workbook to the public variable for
future use. I didn't alter your second procedure since it wasn't clear what
you wanted to do there. As written, it does nothing explicitely with the
public variable. I did miss changing one line in the corrected version of
your first routine. Here is a revision
Option Explicit
Public selectedFile As String
Public bk as Workbook
Sub openInvoiceFile()
Dim selectedFileFullName As String
Set Bk = ActiveWorkbook
selectedFileFullName = Application.GetOpenFilename( _
"Files (*.xls),*.xls", , "Select Invoice", , False)
If selectedFileFullName = "False" Then
MsgBox "You choose to interrupt loading files."
Exit For
Else
Workbooks.OpenText Filename:=selectedFileFullName
SelectedFile = ActiveWorkbook.Name
End If
ThisWorkbook.Activate
End Sub
Here is an example of how to use the variable.
Sub CopyDATAtoMaster()
workbooks(SelectedFile).Activate
worksheets(1).Activate
Range("B2").Select
Selection.Copy
Windows("S2 Master1.xls").Activate
Range("C5").Select
ActiveSheet.Paste
workbooks("SelectedFile).Activate
End Sub
or
Sub CopyDataToMaster1()
Workbooks(Selectedfile).Worksheets(1).Range("B2"). Copy _
Destination:=Workbooks("S2 Master1.xls").Worksheets(1).Range("C5")
End Sub
of course I don't know where your code is located or how many workbooks are
open or which sheet in the workbook you want to use, so this is only a
guess.
Code could contain typos, but should give you a general idea of how to
proceed.
If you want to consolidate information from multiple workbooks and have
little knowledge of coding, perhaps you should look he
http://www.rondebruin.nl/copy3.htm
--
Regards,
Tom Ogilvy
"Baine" wrote in message
...
Thanks for the help Tom. I tried your code and was unable to do any
better on the main question I had. How do I refer to the file that was
opened in the first sub when I use the second sub in the same module?
"Tom Ogilvy" wrote in message
...
Option Explicit
Public selectedFile As String
' Public selectedFile As Workbook
' Brings up dialog to select invoice file to
' copy data to Master file.
Sub openInvoiceFile()
Dim selectedFileFullName As String
Dim wbToOpen As Integer, wsCount As Integer
Dim objWsQBRfreeB As String
For wbToOpen = 1 To 1
'display dialog asking user to select a file
selectedFileFullName = Application.GetOpenFilename("Files (*.xls),*.xls",
, "Select
Invoice", , False)
'check if cancel selected then open
If selectedFile = "False" Then
MsgBox "You choose to interrupt loading files."
Exit For
Else
Workbooks.OpenText Filename:=selectedFileFullName
SelectedFile = ActiveWorkbook.Name
End If
' Workbooks(2).Activate
ThisWorkbook.Activate
Next
End Sub
--
Regards,
Tom Ogilvy
"Baine" wrote in message
...
I have one sub that opens the file selection dialog. Then I want to copy
some cells from the Excel file that was just opened to the MasterData.XLS
file where the Module resides. I don't know what the file name might be
but I need to refer back to that file on several occations to copy all of
the cells. The data needed in not allways in the same row or column. I
know how to do the coding to locate the data and copy it. How do I refer
to the file that was opened in the first sub?
Option Explicit
Public selectedFile As String
' Public selectedFile As Workbook
' Brings up dialog to select invoice file to
' copy data to Master file.
Sub openInvoiceFile()
Dim selectedFile As String
Dim wbToOpen As Integer, wsCount As Integer
Dim objWsQBRfreeB As String
For wbToOpen = 1 To 1
'display dialog asking user to select a file
selectedFile = Application.GetOpenFilename("Files (*.xls),*.xls", ,
"Select Invoice", , False)
'check if cancel selected then open
If selectedFile = "False" Then
MsgBox "You choose to interrupt loading files."
Exit For
Else
Workbooks.OpenText Filename:=selectedFile
End If
' Workbooks(2).Activate
Next
End Sub
--------------------------------------------------------------------------------
Sub CopyDATAtoMaster()
'
' CopyDATAtoMaster Macro
' Macro recorded 10/6/2006 by Baine-64
'
Range("B2").Select
Selection.Copy
Windows("S2 Master1.xls").Activate
Range("C5").Select
ActiveSheet.Paste
' Windows(selectedFile).Activate
' Set selectedFile = ActiveWorkbook
' selectedFile.Activate
End Sub