Use File Obtained From GetOpenFileName

[Zab Trader]

Just added the top part of code to select file instead of having it hard coded.
How do I go about adjusting the 2nd part so I can use it instead of the hard coded file.
ChDrive "c:"
NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx", Title:="Please select a file")
If NewFN = False Then
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Workbooks.Open Filename:=NewFN
End If

Dim sPath As String

Dim sFileName As String '
Dim wsSummary As Worksheet
Dim wsData As Worksheet 'sheet with data to copy
Dim wb As Workbook 'workbooks to loop thorugh
Set wsSummary = ThisWorkbook.Worksheets("Sheet1")
sPath = "C:\Workfiles"
sFileName = Dir(sPath & "Valdatacopy.xlsx")
Set wb = Workbooks.Open(Filename:=sPath & sFileName, ReadOnly:=True)
Set wsData = wb.Sheets("Sheet1")
LastRow1 = wb.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Set wb = Workbooks.Open(Filename:=sPath & sFileName, ReadOnly:=True)
Set wsData = wb.Sheets("Sheet1")
wsSummary.Range("A3:A3" & LastRow1).Value = wsData.Range("F3:F3" & LastRow1).Value
wsSummary.Range("B3:B3" & LastRow1).Value = wsData.Range("D3:D3" & LastRow1).Value
wb.Close
<strike></strike>

<strike></strike>Appreciate the help! Thank You
Zab

 

[Jeffrey Mahoney]

Please try this.

Hey Zab, can you please post code within the CODE tags. It's easier to read and copy.

Sub Zab()


  Dim NewWB As Workbook
  
  ChDrive "c:"
  NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx", Title:="Please select a file")
  If NewFN = False Then
    MsgBox "Stopping because you did not select a file"
    Exit Sub
  Else
    Set NewWB = Workbooks.Open(Filename:=NewFN, ReadOnly:=True)
  End If
  
  'Dim sPath As String
  'Dim sFileName As String '
  Dim wsSummary As Worksheet
  Dim wsData As Worksheet 'sheet with data to copy
  'Dim wb As Workbook 'workbooks to loop thorugh
  
  Set wsSummary = ThisWorkbook.Worksheets("Sheet1")
  'sPath = "C:\Workfiles"
  'sFileName = Dir(sPath & "Valdatacopy.xlsx")
  'Set wb = Workbooks.Open(Filename:=sPath & sFileName, ReadOnly:=True)
  
  Set wsData = NewWB.Sheets("Sheet1")
  LastRow1 = NewWB.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
  'Um...  Repeat -------------------
  'Set wb = Workbooks.Open(Filename:=sPath & sFileName, ReadOnly:=True)
  'Set wsData = wb.Sheets("Sheet1")
  '---------------------------------
  wsSummary.Range("A3:A3" & LastRow1).Value = wsData.Range("F3:F3" & LastRow1).Value
  wsSummary.Range("B3:B3" & LastRow1).Value = wsData.Range("D3:D3" & LastRow1).Value
  NewWB.Close


End Sub