Add New Worksheet after the Last Worksheet

[jwb1012]

Hello, I am trying to get the new worksheet I am adding to be placed after the last workbook.

I want to replace the "Add" in the line:

"Set Dest_Sh = ActiveWorkbook.Worksheets.Add."

with

"Add(After:=mainWB.Sheets(mainWB.Sheets.Count)).


But, it is not working for me. Any thoughts?

Sub Consolidate()
    Dim wb As ThisWorkbook
    Dim sh As Worksheet
    Dim Dest_Sh As Worksheet
    Dim CopyRng As Range
    Dim Start_Row As Integer
    Dim End_Row As Integer
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    'Delete the sheet "Export - Labor BOEs" if it exist
    Application.DisplayAlerts = False
    On Error Resume Next
    ActiveWorkbook.Worksheets("Export - Labor BOEs").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    'Add a worksheet with the name "Export - Labor BOEs"
  [U][I][B]  Set Dest_Sh = ActiveWorkbook.Worksheets.Add[/B][/I][/U]
    Dest_Sh.Name = "Export - Labor BOEs"
    
        'Loop through all worksheets beginning with Labor BOE
        For Each sh In ActiveWorkbook.Sheets
            If Left(sh.Name, 9) = "Labor BOE" Then
                End_Row = sh.Range("L" & Rows.Count).End(xlUp).Row
                Start_Row = Dest_Sh.Range("L" & Rows.Count).End(xlUp).Row + 1
                
            
                'Set the range that you want to copy
                Set CopyRng = sh.Range("A2", "L" & End_Row)
                'This example copies values/formats
                CopyRng.Copy
                With Dest_Sh.Range("A" & Start_Row)
                    .PasteSpecial 8    ' Column width
                    .PasteSpecial xlPasteValues
                    .PasteSpecial xlPasteFormats
                    Application.CutCopyMode = False
                End With
            End If
    Next sh
ExitTheSub:
    Application.GoTo Dest_Sh.Cells(1)
    ActiveWindow.DisplayGridlines = False
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    
End Sub

 

[Trebor76]

Hi jwb1012,

Not sure why you delete the tab only to create again when you could just clear the contents, but this will do what you're after:

Set Dest_Sh = Worksheets.Add
    Dest_Sh.Move After:=Sheets(Sheets.Count)

Also don't use Integer as variable for rows as its maximum is 32,767 where from 2007 Excel row limit is 1,048,576. Use Long instead.

Regards,

Robert

 

[smallxyz]

Hi, try this one:

Set Dest_Sh = ActiveWorkbook.Worksheets.Add(After:=ActiveWorkbook.Worksheets(Worksheets.Count))

 

[jwb1012]

Hi Robert - do you have any suggestions on how to switch my code to "clear contents" instead of Delete & Re-add the sheet? In theory, I would like to do this because after I would like to have 3 macro buttons in the first row on the "Export - Labor BOEs" worksheet (export to PDF, export to Word, export to separate workbook). It would be best if this was a permanent sheet where the data consolidated and the macro buttons would be displayed.

 

[Norie]

Which workbook do you actually want to add the new sheet to?

 

[Trebor76]

do you have any suggestions on how to switch my code to "clear contents" instead of Delete

You can incorporate this code into yours:

Option Explicit
Sub Macro1()

    Dim Dest_Sh    As Worksheet
    Dim lngLastRow As Long

    Set Dest_Sh = Sheets("Export - Labor BOEs")
    
    lngLastRow = Dest_Sh.Cells(Rows.Count, "A").End(xlUp).Row 'Sets the 'lngLastRow' variable based on the last row in Col. A. Change to suit if neccessary.
    'If the 'lngLastRow' variable is 2 or greater then...
    If lngLastRow >= 2 Then
        '...clear the contents from Row 2 to whatever the last is as defined by the 'lngLastRow' variable
        Dest_Sh.Rows("2:" & lngLastRow).ClearContents
    End If

End Sub

HTH

Robert