Create 10 similar tables with Excel VBA

Hi there,

Can you post the macro that you're currently using?

Is it possible for you also to post a either a full or a partial typical worksheet?

Regards,

Greg M

Hi again,

Try the following code:

Option Explicit


'=========================================================================================
'=========================================================================================


Sub CreateNewWorkbook()

    Const sNEW_FILE_NAME    As String = "1-123.xls"

    Dim rRangeToCopy        As Range
    Dim vaDataValues        As Variant
    Dim iErrorNumber        As Integer
    Dim wbkTarget           As Workbook
    Dim wks                 As Worksheet

    mvaWorksheetsToCopy.Copy

    Set wbkTarget = ActiveWorkbook

'   Replace the contents of each worksheet with the values it contains
    For Each wks In wbkTarget.Sheets

        With wks

            Set rRangeToCopy = Range(.Cells(1, 1), _
                                     .UsedRange.Cells(.UsedRange.Cells.Count))

            vaDataValues = rRangeToCopy.Value
            rRangeToCopy.Value = vaDataValues

        End With

    Next wks

    With wbkTarget

        On Error Resume Next
            .SaveAs Filename:=ThisWorkbook.Path & "\" & sNEW_FILE_NAME
            iErrorNumber = Err.Number
        On Error GoTo 0

        .Close SaveChanges:=False

        If iErrorNumber = 0 Then
              MsgBox "The new workbook has been created and saved", vbInformation
        Else: MsgBox "An error occurred - the new workbook was NOT saved", vbExclamation
        End If

    End With

End Sub


'=========================================================================================
'=========================================================================================


Private Function mvaWorksheetsToCopy() As Variant

    Const sSHEET_NAMES      As String = "AA,BB,CC"  '   No spaces after commas!!!
    Const sCOMMA            As String = ","

    Dim vaWorksheetsToCopy  As Variant

    Set vaWorksheetsToCopy = Sheets(Split(sSHEET_NAMES, sCOMMA))

    Set mvaWorksheetsToCopy = vaWorksheetsToCopy

End Function

You'll see that the names of the worksheets to be copied are defined as a Constant in the "mvaWorksheetsToCopy" function:

Const sSHEET_NAMES      As String = "AA,BB,CC"  '   No spaces after commas!!!

It's important to note that there should be no blank spaces between the separating commas and the sheet names.

The above code will copy the required worksheets to a new workbook, replace formula results in each worksheet with the corresponding calculated values, save the new workbook, close it, and then generate a "Task Completed" message.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi again,

Many thanks for your feedback, and especially for your compliment at the end!

I'm very pleased that I was able to help.

Best regards,

Greg M