Copy over a Worksheet from a Closed File

[User Name]

I'd like to open up a closed file and copy over the info into a new worksheet. I've tried recording a Macro to do this but I'm getting hung up on the range select.

The closed file has only one worksheet named identically to the file. i.e. The file is 694564 and so is the worksheet. The destination for the copied worksheet should be Sheet2(Data).

Private Sub CommandButton1_Click()

    Dim FileNameCell As String

    FileNameCell = Sheet9.Range("A" & 2).Value                     

    Const ChooseStationCell = "B17"                  'Validation list cell address
    Const FileNameExt = "CSV"                         'External data file extention
    Const FileFolder = "C:\Data\DSF2"               'Folder with external data files
        
    'Find file
    FileName = FileFolder & "\" & FileNameCell & "." & FileNameExt
    Open FileName 

             'Copy Sheet1 FileNameCell then paste to Data sheet
   
    Close FileName

???

 

[User Name]

Here's where I'm trying to go with this. I'm getting hung up with switching from the workbook I'm copying from to the one I want to paste in.

    'Variables
    Dim FileName As String
    Dim FileName2 As String
    Dim FileNameCell As String

    'Do While Count1 < 856     '1017
    FileNameCell = Sheet9.Range("A" & 2).Value                     'Vlookup formula cell address

    Const ChooseStationCell = "B17"                 'Validation list cell address
    Const FileNameExt = "CSV"                       'External data file extention
    Const FileFolder = "C:\Data\DSF2"               'Folder with external data files
        
    'Find file
    FileName = FileFolder & "\" & FileNameCell & "." & FileNameExt
    FileName2 = "C:\Data\Converters\Converter100.xlsm"
 
    
Dim wbk As Workbook

Set wbk = Workbooks.Open(FileName)
With wbk.Sheets(FileNameCell)
    .Range("A3:X15").Copy
End With

'Set wbk = Workbooks(FileName2)
Windows(FileName2).Activate
With Sheets("Data")
    .Range("A3:X15").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
End With
    
Worbooks.Close FileName:=FileName

 

[cstimart]

Here's where I'm trying to go with this. I'm getting hung up with switching from the workbook I'm copying from to the one I want to paste in.

    'Variables
    Dim FileName As String
    Dim FileName2 As String
    Dim FileNameCell As String

    'Do While Count1 < 856     '1017
    FileNameCell = Sheet9.Range("A" & 2).Value                     'Vlookup formula cell address

    Const ChooseStationCell = "B17"                 'Validation list cell address
    Const FileNameExt = "CSV"                       'External data file extention
    Const FileFolder = "C:\Data\DSF2"               'Folder with external data files
        
    'Find file
    FileName = FileFolder & "\" & FileNameCell & "." & FileNameExt
    FileName2 = "C:\Data\Converters\Converter100.xlsm"
 
    
Dim wbk As Workbook

Set wbk = Workbooks.Open(FileName)
With wbk.Sheets(FileNameCell)
    .Range("A3:X15").Copy
End With

'Set wbk = Workbooks(FileName2)
Windows(FileName2).Activate
With Sheets("Data")
    .Range("A3:X15").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
End With
    
Worbooks.Close FileName:=FileName

Are you wanting to use...?

Workbooks(FileName).Close

 

[User Name]

My problem was that I was trying to use the full file address where I should have just been giving the file's name. There goes several hours. Oh well... now I know.

This worked.

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
    .DisplayAlerts = False
End With

'Timer is used to monitor speed/bugginess of the simulation
StartTime = Timer
    
    'Variables
    Dim FileName As String
    Dim FileNameCell As String

    'Do While Count1 < 856     '1017
    FileNameCell = Sheet9.Range("A" & 2).Value                     'Vlookup formula cell address

    Const ChooseStationCell = "B17"                 'Validation list cell address
    Const FileNameExt = "CSV"                       'External data file extention
    Const FileFolder = "C:\Data\DSF2"               'Folder with external data files
        
    'Find file
    FileName = FileFolder & "\" & FileNameCell & "." & FileNameExt
    
Dim wbk As Workbook

Set wbk = Workbooks.Open(FileName)
With wbk.Sheets(FileNameCell)
    .Range("A3:X15").Copy
End With

Workbooks("Converter100.xlsm").Activate
With Sheets("Data")
    .Range("A1:X13").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
End With
  
Workbooks(FileNameCell).Activate
ActiveWorkbook.Close False

 

[User Name]

I needed to use: Workbooks(FileNameCell).Close
Rather than: Workbooks(FileName).Close

One is a full address. The other isn't.