Hello All,
I have been studying VBA intently for a few weeks now but needing it for a practical purpose as soon as possible and am needing help. I get several emails per week with Excel files that I save into a particular folder automatically through an Outlook Macro. Once a week I would like to take all of the workbooks and worksheets within them, and save them into a specific single workbook on different worksheets. The problem is when I added in the portion of code that copies and pastes the individual worksheets (Not just the workbooks). I assume it is a problem with my Variables as I only have a basic understand of variables.
Basically what I need this code to do is:
1. Allow me to choose which workbooks I want to open and loop through them. (Seems to be working)
2. Loop through each Worksheet in each Workbook selected (Unsure if Works)
3. Copy Each worksheet (Cell Data Only), into a new worksheet on my "Master Pull Emailed Files.xlsm" workbook (Does Not Work)
4. Name Each Worksheet tab with the value of Cell A4 on that particular worksheet. (Optional)
Here is what I currently have:
Sub ImportData()
Dim Filenames As Variant
Dim i As Integer
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws As Worksheet
Set wb1 = ActiveWorkbook
Set wb2 = Workbooks("Master Pull Emailed Files.xlsm")
Application.ScreenUpdating = False
Filenames = Application.GetOpenFilename(Title:="Open File(s)", MultiSelect:=True)
For i = 1 To UBound(Filenames)
Workbooks.Open Filenames(i)
'*************************Line Below is where I am getting the error (on the very first time it runs through)
For Each ws In wb1.Worksheets
If ws.Visible = True Then
ws.Copy After:=wb2.Sheets(wb2.Sheets.Count)
End If
Next ws
Workbooks.Open Filenames(i)
ActiveWorkbook.Close SaveChanges:=False
Worksheets.Add
Next i
End Sub
If anyone can explain what I seem to be missing or have mis-recorded, I would love to learn so I can avoid this problem both now and in the future. Thank you.
Bookmarks