Can someone help me with this??
It's working but id'e like to add a few things.
1) as the sheets are being copied from wb2 into wb1, rename the sheets to the name of wb2 its being copied from
2) make the sheets in wb1, once copied, to become visble
[VBA]
Private Sub Workbook_Open()
'Accesses Outlook and saves the attachments
'from the corresponding folders in my inbox (i.e., Fleet1, Fleet2, Fleet3)
' Call download_outlook_att_Fleet1
' Call download_outlook_att_Fleet2
' Call download_outlook_att_Fleet3
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim mypath, myfile, myextension As String
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = -xlCalculationManual
mypath = "C:\Users\pjimerson\Documents\Engineering\End Of Shift Reports\Fleet 3\"
myextension = "*.xlsm*"
myfile = Dir(mypath & myextension)
Set wb1 = ThisWorkbook
Set NewSheet = wb1.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
Do While myfile <> ""
Set wb2 = Workbooks.Open(filename:=mypath & myfile, UpdateLinks:=Flase)
wb2.Sheets("lists").Copy After:=(NewSheet)
wb1.Save
wb2.Close
myfile = Dir
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = -xlCalculationAutomatic
End Sub
[/VBA]
Thanks in advance!