Clean up Code

eddg2011

New Member
Joined
Sep 21, 2017
Messages
8
So I have this vba code, but it seems pretty redundant. I'm not that good at VBA yet, so could anyone could help me clean this up?

Code:
Sub GetSheets()
Dim Path As String
'FolderName gets directory of folder
Dim FolderName As String
 With Application.FileDialog(msoFileDialogFolderPicker)
   .AllowMultiSelect = False
   .Show
   On Error Resume Next
   FolderName = .SelectedItems(1)
   Err.Clear
   On Error GoTo 0
 End With
 MsgBox FolderName & "\"
'Shows directory
 
Path = FolderName & "\"
Dim bFileSaveAs As Boolean
ProjID = InputBox("Project ID:")
'runs through to get all files starting with ProjID
Filename = Dir(Path & ProjID & "*.xl*")
  Do While Filename <> ""
  Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
     For Each Sheet In ActiveWorkbook.Sheets
     Sheet.Copy After:=ThisWorkbook.Sheets(1)
  Next Sheet
     Workbooks(Filename).Close
     Filename = Dir()
  Loop
'Returns PFA reports
Filename = Dir(Path & "PEW_PFA_RPT_" & ProjID & "*.xl*")
  Do While Filename <> ""
  Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
     For Each Sheet In ActiveWorkbook.Sheets
     Sheet.Copy After:=ThisWorkbook.Sheets(1)
  Next Sheet
     Workbooks(Filename).Close
     Filename = Dir()
  Loop
'Returns MTOP
Filename = Dir(Path & "MTOP_" & ProjID & "*.xl*")
  Do While Filename <> ""
  Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
     For Each Sheet In ActiveWorkbook.Sheets
     Sheet.Copy After:=ThisWorkbook.Sheets(1)
  Next Sheet
     Workbooks(Filename).Close
     Filename = Dir()
  Loop
 bFileSaveAs = Application.Dialogs(xlDialogSaveAs).Show
 If Not bFileSaveAs Then MsgBox "User cancelled", vbCritical
End Sub
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Forum statistics

Threads
1,223,937
Messages
6,175,518
Members
452,650
Latest member
Tinfish

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top