copying sheet issue caused by hidden sheets in fhile

**welchs101** · 04-17-2017, 06:49 AM

hi,

i am having a problem. I have a file (see enclosed). THere are 5 sheets in the file.

sheets labeled: tstsht1, tstsht2, tstsht3, main, tstsht5

I have "hidden" tstsht3 and tstsht5.

What i am trying to do is copy the "main" sheet and paste into a new sheet into the workbook. Once macro is done i should have 5 new sheets labeled "1_1", "1_2", "1_3", "1_4", and "1_5". This is indeed what i get when ALL sheets NOT hidden. However, when i hide "tstsht3" and "tstsht5" the macro does not work correctly. The new sheets created are "main (2)", "main (3)", "main (4)", "main (5)", "main (6)".

Obviously, the problem has something to do with hiding the sheets "tstsht3" and "tstsht5" but i dont know why and i dont know how to fix the problem other than run the macro and then hide all the sheets at the end but i would rather not do this.

Can someone help?

**Sintek** · 04-17-2017, 07:41 AM

Hi welchs101

This code will copy the main sheet and rename even if sheets are hidden...Edited from Tomach's code

Option Explicit

Sub CopySheet()
    Dim MySheetName As String
    Dim i As Integer
    MySheetName = "1_"
    For i = 1 To 5
    Sheets("Main").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = MySheetName & i
    Next i
End Sub

**welchs101** · 04-17-2017, 10:42 AM

yours does work.........wonder why mine does not? any ideas on why mine does not work.........mine and yours are very similar.........

**Sintek** · 04-17-2017, 11:30 AM

Not sure. Here's another way:

Sub test()
Dim shArray() As Variant
Dim i As Long
shArray = Array("1_1", "1_2", "1_3", "1_4", "1_5")
For i = LBound(shArray) To UBound(shArray)
    Sheets("Main").Copy After:=Sheets(Sheets.Count)
    Sheets("Main (2)").Name = shArray(i)
Next i
End Sub

**welchs101** · 04-17-2017, 04:00 PM

thanks again for the code. i will try it out.

However, if someone can tell me why my code does not work i would appreciate it. I am missing some fundamental thing i think.

**welchs101** · 04-18-2017, 06:58 AM

anyone know why my code does not work?

**xlnitwit** · 04-18-2017, 08:48 AM

Hi,

Your code doesn't work because Excel actually copies the sheet after the last visible sheet, which puts each new sheet before the hidden sheet tstsht5. It is that last sheet that gets renamed, not the new sheets.

**welchs101** · 04-18-2017, 10:49 AM

hi,

yes i see that the wrong sheet gets renamed but i dont know why.

i thought i was specifying the correct sheet when i use the following:

wrksht_main_sht.Copy After:=wb_macro.Sheets(initial_sht_count + LC1 - 1)
wb_macro.Sheets(initial_sht_count + LC1).Name = shtarray(LC1)

obviously this is not working which tells me referencing the "index" number of the sheet is not correct but why ....

**xlnitwit** · 04-18-2017, 11:13 AM

The index is wrong for the reason I mentioned- the code copies the sheet to after the last visible sheet but that is before the last hidden one. This means that the index in your code is referring to the last sheet (the hidden one) and not the copy you just created, which is the one before the hidden one.

**welchs101** · 04-19-2017, 06:48 AM

thanks. i understand what your saying thanks. just seems like this is "wrong" for excel vba to do........when you tell it to add a sheet after a hidden sheet the indexes should still work but i guess not.

How would you handle this situation?

**xlnitwit** · 04-19-2017, 10:37 AM

Using the approach that sintek mentioned in post #2.

copying sheet issue caused by hidden sheets in fhile

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

copying sheet issue caused by hidden sheets in fhile

Re: copying sheet issue caused by hidden sheets in fhile

Re: copying sheet issue caused by hidden sheets in fhile

Re: copying sheet issue caused by hidden sheets in fhile

Re: copying sheet issue caused by hidden sheets in fhile

Re: copying sheet issue caused by hidden sheets in fhile

Re: copying sheet issue caused by hidden sheets in fhile

Re: copying sheet issue caused by hidden sheets in fhile

Re: copying sheet issue caused by hidden sheets in fhile

Re: copying sheet issue caused by hidden sheets in fhile

Re: copying sheet issue caused by hidden sheets in fhile

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Selecting a sheet from a range - issue with hidden sheets.

3D Referencing

Copying formulas on hidden sheet

Issue with Sheet Hidden

copying hidden cells and paste to non hidden cells on same sheet

[SOLVED] "With Sheets" Issue - macro on one sheet to affect hidden rows on other sheets

Copying Data to a hidden Sheet

Bookmarks

Bookmarks

Posting Permissions