lks55
04-01-2016, 07:50 AM
Hi,
i wrote this code to loop threw the whole folder, but it only opens one and the same exelsheet in the folder and it doesnt switches to the next one. what am I doing wring?
Sub AddNew()
Dim s As Variant
Dim s5 As Long
Dim lastcell As Long
Dim MyFile As String
Dim directory As String
MsgBox "Please open a file to show the path of the regions"
s = Application.GetOpenFilename("Excel Workbook (*.xls; *.xlsx; *.xlsm),*.xls; *.xlsx; *.xlsm")
s = Left(s, InStrRev(s, "\"))
MyFile = Dir(s & "*.xl??")
Debug.Print s
Workbooks.Add
's = Application.GetSaveAsFilename("WK00 - UK Total", "Excel Files (*.xlsm), *.xlsm")
'ActiveWorkbook.SaveAs Filename:=s
Do While MyFile <> ""
Workbooks.Open (MyFile)
s5 = 4
Do While Cells(s5, 2) <> ""
s5 = s5 + 1
Loop
Range(Cells(3, 2), Cells(s5, 13)).Copy
ActiveWorkbook.Close
If Cells(1, 1) <> "" Then
ActiveCell.End(xlDown).Select
lastcell = ActiveCell.Row
Cells(lastcell + 1, 1).Select
End If
ActiveSheet.Paste
Loop
End Sub
Best Regards
i wrote this code to loop threw the whole folder, but it only opens one and the same exelsheet in the folder and it doesnt switches to the next one. what am I doing wring?
Sub AddNew()
Dim s As Variant
Dim s5 As Long
Dim lastcell As Long
Dim MyFile As String
Dim directory As String
MsgBox "Please open a file to show the path of the regions"
s = Application.GetOpenFilename("Excel Workbook (*.xls; *.xlsx; *.xlsm),*.xls; *.xlsx; *.xlsm")
s = Left(s, InStrRev(s, "\"))
MyFile = Dir(s & "*.xl??")
Debug.Print s
Workbooks.Add
's = Application.GetSaveAsFilename("WK00 - UK Total", "Excel Files (*.xlsm), *.xlsm")
'ActiveWorkbook.SaveAs Filename:=s
Do While MyFile <> ""
Workbooks.Open (MyFile)
s5 = 4
Do While Cells(s5, 2) <> ""
s5 = s5 + 1
Loop
Range(Cells(3, 2), Cells(s5, 13)).Copy
ActiveWorkbook.Close
If Cells(1, 1) <> "" Then
ActiveCell.End(xlDown).Select
lastcell = ActiveCell.Row
Cells(lastcell + 1, 1).Select
End If
ActiveSheet.Paste
Loop
End Sub
Best Regards