Adding Sheets

[hansjhamm]

I have a Summary Workbook & sheet in a folder. Currently this workbook
looks for "additional" workbooks in the same folder and opens each up
copies the data and paste into the summary workbook. These "additional"
workbooks are named a1,a2,a3 etc...and can vary from month to month. It
can go up to 15 "additional" workbooks.
What I am having to do now manually is this; open my summary workbook
add or delete the new sheets that I name with the same name, ie. a1,
a2, a3. Then I can run the VBA code to open a1, copy the data and paste
into the summary workbook in sheet a1. How can I go about having it
coded to where it will look for a1 workbook, copy the data, then if
sheet a1 does not exist in the summary workbook, create a1 worksheet
and paste the data? The opposite of this would be if an a14 workbook
does not exist, but in the summary workbook it does, then remove sheet
a14 from the summary workbook.

 

[mrice]

Detecting workbooks can be done with the dir statement.

If one is missing from your summary, you can use Sheets.Add and the
ActiveSheet.Name

For deleting, you may need to use application.displayalerts = false t
avoid confirmation dialog boxes appearing

 

[hansjhamm]

Martin,

I am not at all sure what, how or where to start on this one. I have
never used the Dir statement. Could you help me out and explain a
little more detail for me?
I typically start out with the code stating something like this:
Sub getdata()
Dim CMAPReport As Workbook
Workbooks.Open Filename:=ThisWorkbook.Path & "\c1.htm"
Cells.Select
Selection.Copy
Windows("CMAP Report.xls").Activate
Sheets("Air Circ. Sidecaps").Select
Cells.Select
ActiveSheet.Paste
With Cells
..MergeCells = False
End With
Columns("B:C").ClearContents
Workbooks("C1").Close SaveChanges:=False

Would I need to re-do this section? or add-in? etc...

Thanks,

Hans