Copy Hidden Sheets

Oct 17, 2007

I have 5 sheets in a workbook. Three are hidden. My code allows user to make as many copies of the hidden sheets as needed and place them at the end by means of a macro button. This works fine, but after multiple copies (4 to 15) it crashes! Don't know what I did wrong.


Sub CopyWs()

Sheet8.Visible = True
Sheet8.Select
Sheet8.Copy After:=Sheets(Worksheets.Count)
Sheet8.Visible = False

Sheet9.Visible = True
Sheet9.Select
Sheet9.Copy After:=Sheets(Worksheets.Count)
Sheet9.Visible = False

Sheet10.Visible = True
Sheet10.Select
Sheet10.Copy After:=Sheets(Worksheets.Count)
Sheet10.Visible = False
End Sub

View 9 Replies


ADVERTISEMENT

Copy Hidden Sheets To New Workbook As Values

Aug 9, 2007

I have a macro that copies 2 worksheets of an open workbook "Combined Sales Tool" and saves those 2 sheets in the root of the C drive with a variable name.

I want to know how I can #1 close the newly created workbook, #2 focus back to the original workbook "Combined Sales Tool", hide the 2 sheets that were copied to the new file, then close the original workbook with (and for example without) saving

part of my code below:

Sub esummary()
Dim OutApp4 As Object
Dim OutMail4 As Object
Dim cell As Range
Dim filedoc As String
Dim intFreeRow
Dim emailatt4 As String

Sheets("Summary").Visible = True
Sheets("Email").Visible = True

Sheets( Array("Summary", "Email")).Select
Sheets("Summary").Activate
Sheets(Array("Summary", "Email")).Copy
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

View 4 Replies View Related

Format All Cells In All Sheets To Protection Hidden On Visible And Hidden Tabs

Feb 28, 2014

I am trying to format all cells on all sheets (hidden or otherwise) as "Locked" so when the sheets are protected the user can't see the formulas. This macro individually selects every sheet in the book and applys the formatting. Is there a way to modify this code to accomplish the same thing without having it actually select every sheet? The only reason it is an issue is that after running the macro you end up on the last sheet in the book.

View 7 Replies View Related

Hiding Sheets But Still Letting Macros Run When Printing Hidden Sheets

Nov 10, 2008

i have a workbook that has the following sheets

working sheet
job sheet
receipt of deposit letter
completion sheet
delivery note
delivery note (2)
odd
even
t&t
glass
ggf

i want to hide every sheet except the working sheet.
I have tried this but the macros bring up an error when i run the macro

my macros involve printing certain pages dependng on what button is pressed

i get an error whatever
how do i stop this

View 14 Replies View Related

Very Hidden Sheets Are Very Hidden

Jan 2, 2007

i have an old spreadsheet that i created about a year or so ago.
i managed to make some sheets "very hidden" as opposed to just "hidden" or "visible".

the problem is i can't find them ! i have tried "format, sheet, unhide" but that is greyed out.

i do have passwords for them, but i can't find them to put these in!

View 9 Replies View Related

Hidden Sheets

Oct 3, 2008

I have a 15 sheet spreadsheet that has many formula and results that are obtained from a "Master Sheet." I want to hide the "Master Sheet" from users view.

When I do so the formulae can't find the info they require. How do I hide a sheet so that users can't see it, but the formula from other sheets can still access it?

View 4 Replies View Related

VBA With Hidden Sheets

Dec 5, 2007

i've an excel file where a final presentable sheet is linked to some hidden sheets which i dont want the user to see. Thru VBA, i'm performing some format related stuff in those hidden sheets, using: Sheets("Historical1").Visible = True. later, VBA sets it back to FALSE. But, i want to get rid of this hiding/unhiding, if its possible. My code has the following structure:

Sub tame_blph1()
Sheets("Historical1").Visible = True
Sheets("Historical1").Activate
Dim coll As Integer
Dim startcoll As Integer
Dim endcoll As Integer
startcoll = 25
endcoll = 124
For coll = startcoll To endcoll Step 2
If Len(selection) > 0 Then Call Macro1
Next coll
Sheets("Historical1").Visible = False
Sheets("Table").Select
End Sub

In other words, can we perform the above without hiding and unhiding the Sheet called "Historical1"

View 3 Replies View Related

Hyperlinks To Hidden Sheets?

Jun 15, 2013

I have a worksheet with 28 sheets. I want the first sheet to be the only sheet displayed when opening the file and then want people to have the ability to click a hyperlink to unhide a particular sheet. Then when they go back to the main page (via another hyperlink), the sheet that was unhidden will be hidden again. I was able to put together some code from [URL]. When I click on a hyperlink on the main page, I get a "Subscript out of range" error and it highlights Sheets(strSheet).Visible=True from the Worksheet_FollowHyperlink code. I made a basic spreadsheet with just 3 sheets and plugged this code in and it worked fine so I'm not quite sure what the error is. I attached the spreadsheet I'm using.

View 3 Replies View Related

Hidden Sheets Within An Workbook

Nov 18, 2009

I have been sent a workbook which contains a number of formulae that reference hidden worksheets (see attached JPEG).

I cannot work out how to display these sheets? The workbook does not seem to be protected, I can edit and save the visible sheet. Can anyone shed some light on how to view the referenced sheets?

View 4 Replies View Related

Selecting Hidden Sheets

Sep 2, 2009

I have the following code which works fine when all of the sheets are not hidden:

View 3 Replies View Related

Hyperlink To Hidden Sheets

Apr 5, 2013

I have a total of 15 sheets on my excel documents. I've hidden 10 (5 sheets will be shown) of those sheets so i can hyperlink to them from the sheet called "The plan". The name of the hidden sheets are called:

"Week 1"
"Week 2"
"Week 3"
"Week 4"
"Week 5"
"Week 6"
"Week 7"
"Week 8"
"Week 9"
"Week 10"

On "The plan" I have hyperlinked the words to the corresponding sheets then I hid the sheets, now the hyperlinks don't work. What should I do?

View 2 Replies View Related

VBA To Delete All Hidden Sheets?

Aug 21, 2013

I just came across file that needs to be cleaned up. Problem is, the main summary references more than 50 hidden sheets for pay periods dating back to 2011.

I want to make everything values from previous pay periods then get rid of the hidden sheets.

View 4 Replies View Related

Macros And Hidden Sheets

Jul 31, 2007

ive got a macro which works with data on hidden sheets within the workbook - when i hide the sheets the macro gives errors and will not run. when i unhide the sheets everything runs fine!

View 9 Replies View Related

View Hidden Sheets

Dec 4, 2006

how can i see hidden sheets in a workbook?

View 3 Replies View Related

Different Password To Unhide The Hidden Sheets.

Nov 22, 2008

I have 5 sheets, 3 of them are hidden, how can i put a 3 different password to unhide the hidden sheets.

View 6 Replies View Related

Maximum Number Of Hidden Sheets?

Feb 25, 2009

I tried to Google this question but cannot find the answer. What is the maximum number of worksheets that Excel can hide within a workbook? I have a macro which hides all worksheets with the exception of the active worksheet and it has never failed. But this morning I had started working on a workbook containing many sheets and when I ran my HideSheets macro a maximum of 14 sheets hides every time. Is it my code (see below)?

View 2 Replies View Related

Advanced Filter On Hidden Sheets

Jul 6, 2009

I want this macro to run when I select sheet1 so I have dropped the code into sheet1. The macro runs fine as a stand alone but I get a 1004 protection error when placed into the worksheet module. Sheet "data2" is unprotected. I have tried "calling" the macro from the sheet1 module but it loops. I know it's simple but it's kicking my ***. data2 is hidden and workbook is unprotected as of now.

View 3 Replies View Related

Hidden/Visible Sheets In Listbox

Sep 22, 2009

I'm trying to create a userform that allows the user to swap sheets from visible to hidden status. I've made it to the point where all the sheet names are populated based on their current status in their respective listbox, but I am stuck on using the results of any swaps to newly set their visible property. the code I have in the userform is as follows:

View 2 Replies View Related

Table Of Contents For Hidden Sheets

Jan 3, 2012

I have to review work papers with 50+ sheets. The users often hide the sheets they do not use. As part of my review I have to review all sheets, even the hidden ones.

What I would like to achieve:

To create a list of all hidden sheets with hyperlinks so I can go directly to the hidden sheet. This would be the same as a table of contents but for hidden sheets.

View 4 Replies View Related

Use Userform And Hidden Sheets At The Same Time

Feb 1, 2013

Is there a way to use a userform and hidden sheets at the same time?

I want to limit the access to the information through a userform but my userform requires me to unhide the userform to show. Is there a way to go around this? Because the information might be misused if the user can simply close the form and have access to the information in the sheet.

View 3 Replies View Related

Macro For Selecting Hidden Sheets?

Dec 26, 2013

I have spreadsheet with 13 sheets the first of which is a title page, what I wish to do is hide the other twelve sheets and somehow be able to select them from a drop down box (or similar what ever works with a macro)on the front page, the selected sheets would then appear unhidden.

I am OK with formulas etc but macros are beyond my ability even trying to follow examples & tutorials online has me lost.

The sheets all have specific but different names and none need to be selected more than once (although if possible it would be good). I understand where to put the macro in but as to how it is written ......

View 7 Replies View Related

Code To Show Very Hidden Sheets

Mar 25, 2007

I downloaded a cool add-in from XL-Logic that lets you select sheets to print. It's great, except that I need to print very hidden sheets and it does not display very hidden sheets. Does anyone know how to modify this code so it will list all sheet including very hidden sheets? And is there a way to make the sheets appear in alphabetical order? I've tried to play with it, but I don't know enough about VB....

View 9 Replies View Related

Prevent Unhiding Of Hidden Sheets

Jul 20, 2009

Regarding the age old problem of trying to ensure macros are enabled in a workbook, using the process of hiding all sheets bar one:

Automatically Close Workbook If Macros Are Disabled

Reafid inserts some excellent code so that worksheets are hidden BOTH before closing AND on a normal Save event (I have also attached Reafid's zip file with the workbook that includes his/her code).

Having done much searching it seems to be the best solution out there, however I can still break it fairly easily and I'm worndering if anybody can stop me doing the following to break it:

Open the file in its restricted state (i.e. only Warning tab shown, all other sheets very hidden) and NOT enabling macros on opening. I'm therefore at the position Reafid intended; macros are disabled so all sheets are very hidden. Then to break it, in my personal macro workbook I have a simple macro that unhides all worksheets in ThisWorkbook, I run that personal macro and everything in Reafid's file is unhidden.

Reafid's file therefore has been opened with macros DISABLED and now all worksheets are visible.

View 9 Replies View Related

Hidden Sheets - Flashing Images

Aug 2, 2006

I have written some code to be executed in Sheet1 which copies formats from Sheet1 to Sheet2 and then values from Sheet 3

Sheets("Sheet1"). Range("E4:M9").Copy Sheets("Sheet2").Range ("A1").Offset(9 * i + 3, 1)
Sheets("Sheet3").Range("P90:W93").Copy _
Sheets("Sheet2").Range("A1").Offset(9 * i + 5, 2).PasteSpecial _ Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

The code does exactly what I want it to do, but when Sheets 2 and 3 are hidden images of what is being pasted briefly flash up on the screen. Is there a way of avoiding this? The amount of data in the whole sheet is quite large (about 4MB), could it just be that the program is running too slowly?

View 3 Replies View Related

Password Protecting Hidden Sheets

Jul 25, 2007

I am using some VBA I found here to password protect a hidden sheet. It works perfectly for 1 sheet, but I am trying to hide 6 but am having problems. Under the HideSheets part I've modified it fine, by using


Sub HideSheets()
'Set worksheet to Very Hidden so that it can only be unhidden by a macro
Worksheets("Confidential1").Visible = xlSheetVeryHidden
Worksheets("Confidential2").Visible = xlSheetVeryHidden
Worksheets("Confidential3").Visible = xlSheetVeryHidden
End Sub.........

View 9 Replies View Related

Reset Visibility Of Accidentally Hidden Sheets?

Oct 19, 2012

I was playing with some codes on hiding and unhiding the sheets and then I tried this code to hide a sheet but then I decided to delete the code and the module after. The problem now is I can't unhide the sheet. Even the Insert/Rename/Delete sheet option when I right click it are all disabled.

VB:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheet2.Visible = xlSheetVeryHidden
Me.Protect ("password")
End Sub

View 6 Replies View Related

How Can I Make A Macro Work To Hidden Sheets?

Aug 21, 2009

OR can I? I get a "Run Time Error '1004' Select Method of Worksheet Class Failed"

Is there anyway around this?

Basically all the sheets are hidden from the user except for the Entry sheet. They input there data on there and then they press a send command button which then needs to copy that information to a hidden sheet. This is where the problem occurs.. Thought maybe their was a way around that or do I need to unhide the sheets during the macro and then rehide them again?

View 13 Replies View Related

Sort Data On Protected & Hidden Sheets

Sep 16, 2007

Is there a way I can sort multiple hidden protected worksheets with a password “Protect”
I have 12 worksheets “Jan”, “Feb”. Etc

“The Range on each worksheet is the same ("A11:CR200") ....

View 9 Replies View Related

Formula Error In Hidden Sheets With Formulas Linking To A Sheet Of Same Workbook?

Nov 30, 2012

I am currently working with a hidden sheetname "hide_template" which contains formula linking to another sheetname "rawdata" of same workbook.

In this "rawdata", the contents is updated from time to time.

The problem is when I open this workbook and update the entries in "rawdata", the formulas/results in "hide_template" is not updated and invalid (ie. #N/A, #VALUE..)

This is the overview of process that Im working with:

===> open workbook ===> paste new entries on "rawdata" ==> run a macro to copy range in the "hidden_template" to paste to new sheet of same workbook

I already have a working macro to copy range from the "hidden_template" which works as follows:

===> set to visible the "hidden_template" ===> copy range from "hidden_template" ===> paste to new sheet ===> set to hidden the "hidden_template"

View 1 Replies View Related

Copy Without Hidden Columns

Jul 15, 2005

when I want to send by email I find I cannot copy these smaller sheets - every attempt at a copy will also copy the hidden columns.

How to copy a spreadsheet and NOT pick up the hidden columns?

I realise this might be nearly impossible or considered useless because no hidden columns would mean either making the copy with blank hidden columns or altering all formulas to suit a sheet which now does not have all the original columns.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved