jordanburch
Active Member
- Joined
- Jun 10, 2016
- Messages
- 440
- Office Version
- 2016
Hey Guys
I have the below. I just want it to open the file and then import the sheet regardless of the file name. Basically it has one sheet and they client keeps changing the name and they arent going to be consistent so I need a solution. Any ideas? The sheet is called IPAC PROCESSED in this example.
Jordan
I have the below. I just want it to open the file and then import the sheet regardless of the file name. Basically it has one sheet and they client keeps changing the name and they arent going to be consistent so I need a solution. Any ideas? The sheet is called IPAC PROCESSED in this example.
Jordan
VBA Code:
Sub DCASIMPORTIPACPROCESSED()
Worksheets.Add().Name = "IPAC PROCESSED"
Dim myfile As String
Dim erow As Long
Dim filepath As String
Dim wb1 As Workbook, wb2 As Workbook
Dim fn As String
fn = Left(ThisWorkbook.Worksheets("Variables").Range("A1").Value, 6)
Application.ScreenUpdating = False
Set wb1 = ThisWorkbook
filepath = "K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\Source Files\DCAS\" & ThisWorkbook.Worksheets("Variables").Range("A4").Value & "\" & fn & Right(ThisWorkbook.Worksheets("Variables").Range("a2").Value, 2) & "\"
myfile = Dir(filepath)
Do While Len(myfile) > 0 And myfile <> "suspense automation.xlsm"
erow = wb1.Sheets("IPAC PROCESSED").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Set wb2 = Workbooks.Open(filepath & myfile)
With wb2
.Sheets("IPAC Processed").Range("A2:n300").Copy Destination:=wb1.Worksheets("IPAC PROCESSED").Cells(erow, 1)
.Close savechanges:=False
End With
myfile = Dir
Loop
Application.ScreenUpdating = True
End Sub