Looping through list of workbooks to open & sheets to move

[Beverly]

I've just taken over the task of putting together a monthly reporting package
that combines about 80 separate workbooks into one. I want to automate this
incredibly boring task and was hoping that I could get some help here. From
what I've seen so far, you all are amazing!

Here's basically what I want my VBA code to do:

1. From my main workbook (which will be open), turn off automatic
calculation and then go to the Table of Contents sheet and delete the sheets
marked with an X in column F. There will be blank cells in this column as
well as X's. The end of the list is indicated by "End".
2. Go to cell G6 and open the file that I have listed there. Select the
active sheet, change the name to the value in cell E6, and then copy it into
my main workbook after the sheet listed in cell E5.
3. Close the file that I just copied the sheet from, and move on to the
next one listed in cell G7. If G7 is blank (and does not equal "End"), go to
cell G8 and open the file listed there. Stop when it reaches "End".

Any assistance would be greatly appreciated!

Beverly

 

[Joel]

The code below is close to what you need. You mention a main worksheett and
a table of contents but don't specifically say what the name of these sheets
are. Also where you delete worksheets you don't specify the starting row but
when you are copyying the worksheets you specify you sttart in row 6.

Modify the code as necessary, I will help if you need more help. You need
to specifically name the table of contents sheet because when yoi uopen a
workbook and copy sheets the active worksheet gets changed.

Sub update_workbook()

Set main = ThisWorkbook.ActiveSheet
main.EnableCalculation = False
Set TOC = ThisWorkbook.Sheets("Table of Contents")
RowCount = 1
Application.DisplayAlerts = False
With TOC
Do While .Range("G" & RowCount) <> "End"
If .Range("F" & RowCount) = "X" Then
Sheets(.Range("F" & RowCount)).Delete
End If
RowCount = RowCount + 1
Loop
End With
RowCount = 6
With main
Do While .Range("G" & RowCount) <> "End"
If .Range("G" & RowCount) <> "" Then

Workbooks.Open Filename:=.Range("G" & RowCount)
Set openbk = ActiveWorkbook
openbk.ActiveSheet.Copy _
after:=.Range("E" & (RowCount - 1))
ActiveSheet.Name = .Range("E" & RowCount)
openbk.Close savechanges:=False
End If
RowCount = RowCount + 1
Loop
End With

main.EnableCalculation = True
Application.DisplayAlerts = True

End Sub

 

[Beverly]

This is defintely close to what I need. Thank you so much! I made some
modifications and am now testing the code, but I am running into a "type
mismatch" error on this line:

Sheets(.Range("F" & RowCount)).Delete

I tried putting the sheet name into a variable and using
Sheets(wsname).Delete, Sheets("wsname").Delete, Sheets("'" & wsname &
"'').Delete, setting the wsname to "'" & .Range("F" & RowCount)).Value & "'",
but nothing works. Any ideas?

As I am stepping through the macro and watching the wsname variable change,
it looks correct (that is, it shows as "Summ_fct_bgt" when it hits my first
sheet to be deleted)...

Beverly

 

[Beverly]

Ok, I searched on "type mismatch" & "sheet" and found one of your older
posts, Joel, and made a minor adjustment and now it's working. Yay!!! Thank
you again!!

Beverly

 

[Joel]

I would make the following modification

from
Sheets(.Range("F" & RowCount)).Delete
to
ThisWorkbook.Sheets(.Range("F" & RowCount)).Delete

This would only be a problem if you have more than one workbook opend.
Thisworkbook will specify a sheet in the same workbook as the macro is
running would get deleted.

Do your data start in row 1 and do you have any blank rows of data?

Check what rowCount is when you get an error. Then verify the name in
column F matches exactly one of the sheets in the workbook.