How to get the first worksheet only? [Closed]

Report
Posts
1
Registration date
Wednesday June 20, 2018
Status
Member
Last seen
June 20, 2018
-
Hi I'm new in VBA. My program should let the user choose a folder of excel files. Then it will automatically copy all of the first worksheet to master workbook and consolidate it, But my VBA code only let the user choose the folder. What should I do?



Here is my VBA:
Sub ConslidateWorkbooks()


Dim FolderPath As String
Dim Filename As String
Dim Sheet As Worksheet
Dim Work As Workbook

Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = Application.DefaultFilePath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing

Application.ScreenUpdating = False
FolderPath = Environ("userprofile") & fldr
Filename = Dir(FolderPath & "*.xls*")
Do While Filename <> ""
Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets


Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet

Workbooks(Filename).Close
Filename = Dir()
Loop
Application.ScreenUpdating = True



End Sub