Excel – VBA Copy Sheet to End of Workbook (with Hidden Worksheets)

excelvba

I want to copy a sheet and add it to the end of all current sheets (regardless of whether the sheets are hidden).

Sheets(1).Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).name = "copied sheet!"

This works fine, except, when there are hidden sheets, the new sheet is only inserted after the last visible worksheet, so the name command renames the wrong sheet.

I have tried variations of the following to get a reference to the newly copied WorkSheet but none were successful and/or valid code.

Dim test As Worksheet
Set test = Sheets(1).Copy(After:=Sheets(Sheets.Count))
test.Name = "copied sheet!"

Best Answer

Try this

Sub Sample()
    Dim test As Worksheet
    Sheets(1).Copy After:=Sheets(Sheets.Count)
    Set test = ActiveSheet
    test.Name = "copied sheet!"
End Sub

Looking back at this, a better approach would be

Set test = Sheets(Sheets.Count)

As correctly mentioned in the comments below, there are lot of things that needs to be considered when copying and renaming a sheet. Would recommend checking the other answers as well.

Related Solutions

Javascript – Auto fade/show between multiple divs

You could try this:

$(document).ready(function () {
    var allBoxes = $("div.boxes").children("div");
    transitionBox(null, allBoxes.first());
});

function transitionBox(from, to) {
    function next() {
        var nextTo;
        if (to.is(":last-child")) {
            nextTo = to.closest(".boxes").children("div").first();
        } else {
            nextTo = to.next();
        }
        to.fadeIn(500, function () {
            setTimeout(function () {
                transitionBox(to, nextTo);
            }, 5000);
        });
    }

    if (from) {
        from.fadeOut(500, next);
    } else {
        next();
    }
}

DEMO: http://jsfiddle.net/CYJLA/

I'm sure it could be cleaned up and/or be more efficient, but I just put it together to get it working.

Excel – VBA Macro to check if sheet name exists

Try this :

Function sheetExists(sheetToFind As String) As Boolean
    sheetExists = False
    For Each sheet In Worksheets
        If sheetToFind = sheet.name Then
            sheetExists = True
            Exit Function
        End If
    Next sheet
End Function

And use like this :

if sheetExists("TEMPLATE") = true then
  'your code
else
  'code
end if

Excel VBA If WorkSheet("wsName") Exists

Best Answer

Related Solutions

Javascript – Auto fade/show between multiple divs

Excel – VBA Macro to check if sheet name exists

Related Topic