Hello!



Excel Marco is very new to me. I have some knowledge in VBA but nothing extensive. I have a massive data in excel that have 58+ worksheets.

We are in the process of doing our annual reviews, I have to run the excel report called Annual 12x’s and generate a workbook called Table1_2006 - Table58_2006, in each workbook it have 12worksheets called “Jan,Feb,March..etc”

Here is what I found and it seen to work but it copy the whole worksheet I only need to copy a range and text box (footnote) that I have, also my computer is super slow, is there a way to copy one workbook and close and open another workbook?

Sub testcopy()
  Dim basebook As Workbook
    Dim mybook As Workbook
    Dim FNames As String
    Dim MyPath As String
    Dim SaveDriveDir As String
 
    SaveDriveDir = CurDir
    MyPath = "C:\test"
    ChDrive MyPath
    ChDir MyPath

    FNames = Dir("table01_2006.xls")
    If Len(FNames) = 0 Then
        MsgBox "No files in the Directory"
        ChDrive SaveDriveDir
        ChDir SaveDriveDir
        Exit Sub
    End If
 
    Application.ScreenUpdating = False
    Set basebook = ThisWorkbook

    Do While FNames <> ""
        Set mybook = Workbooks.Open(FNames)

        mybook.Worksheets(3).Copy after:= _
            basebook.Sheets(basebook.Sheets.Count)
        On Error Resume Next
        ActiveSheet.Name = mybook.Name ' Or use Left(mybook.Name, Len(mybook.Name) - 4)
        On Error GoTo 0
        mybook.Close False
        FNames = Dir()
    Loop
    ChDrive SaveDriveDir
    ChDir SaveDriveDir
    Application.ScreenUpdating = True
End Sub

Sub Renamsheets()

Application.DisplayAlerts = False
    '--Sheets("testing-copy").Delete
    ActiveSheet.Delete
    '--ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True

ActiveWorkbook.Worksheets.Add(After:=ActiveSheet).Name = "Jan"
ActiveWorkbook.Worksheets.Add(After:=ActiveSheet).Name = "Feb"
ActiveWorkbook.Worksheets.Add(After:=ActiveSheet).Name = "March"
ActiveWorkbook.Worksheets.Add(After:=ActiveSheet).Name = "April"
ActiveWorkbook.Worksheets.Add(After:=ActiveSheet).Name = "May"
ActiveWorkbook.Worksheets.Add(After:=ActiveSheet).Name = "June"
ActiveWorkbook.Worksheets.Add(After:=ActiveSheet).Name = "July"
ActiveWorkbook.Worksheets.Add(After:=ActiveSheet).Name = "Aug"
ActiveWorkbook.Worksheets.Add(After:=ActiveSheet).Name = "Sept"
ActiveWorkbook.Worksheets.Add(After:=ActiveSheet).Name = "Oct"
ActiveWorkbook.Worksheets.Add(After:=ActiveSheet).Name = "Nov"
ActiveWorkbook.Worksheets.Add(After:=ActiveSheet).Name = "Dec"

End Sub
any help will be greatly greatly Greatly appreciated !

CThai