Hi
Does anyone know of a line of VBA code that selects all sheets in the workbook (including hidden sheets)?
Many thanks
Johno
Hi
Does anyone know of a line of VBA code that selects all sheets in the workbook (including hidden sheets)?
Many thanks
Johno
Re: Selecting all sheets
Mmm, this does work, but i need a simple line of code that selects all worksheets in one go. Is this not possible?
Thanks
Johno
Re: Selecting all sheets
Thanks Roy, but that line of code doesn't seem to work for me
Re: Selecting all sheets
I don't think that you can select a hidden sheet. If you want to do something with a hidden sheet you will probably need to unhide it first.
Re: Selecting all sheets
You can not select hidden sheets.
What are you trying to do?
Re: Selecting all sheets
Thanks for the reply
I guess I don't really need to select the hidden sheets. But I do need to select all the visible sheets.
What I am trying to do is select all the sheets at once, copy them and re-paste them in another file as 'values'. By doing this I am creating a file without all the formulas in the cells.
Hope that makes sense. Is this possible?
Johno
Re: Selecting all sheets
Hi Johno - could be wrong now, but I dont think you cant PasteSpecial(values) an entire workbook, or an entire worksheet....
See this post for a similar type problem and a solution.
http://www.ozgrid.com/forum/showthre…remove+formulas
Ger
Re: Selecting all sheets
You can do it manually though....
Re: Selecting all sheets
Yes, you can select all sheets manually, and yes you can copy all selected sheets manually to another workbook and this can be done in two lines of VBA.
But even manually, I dont think you can paste the worksheets into a new workbook and paste the worksheets as values (ie removing the formulas).... could be wrong...
Ger
Re: Selecting all sheets
What about simply copying all the sheets at once and re-pasting them as values ovre the top (in the same workbook)?
Johno
Re: Selecting all sheets
Why not use Aaron Bloods code in That's Cool, then simply use SaveAs a new name. You will then have a copy including hidden pages
Re: Selecting all sheets
Is 'That's Cool' a thread? I can't seem to find it.....
Cheers
Johno
Re: Selecting all sheets
Thanks Ger
Re: Selecting all sheets
Here's a link to Aaron's code
Re: Copy All Sheets to New Workbook as Values Only
Hi
Still having trouble with copying the worksheets into values.
The macro fails at this point:
I get the following error message:
Run Time Error 1004
Select method of Range class failed
Any idea why this happening? I suspected it may be because I have got hidden columns on some of the worksheets, but sometimes I dont get this error. Very strange.
Johno
Don’t have an account yet? Register yourself now and be a part of our community!