Free Microsoft Excel 2013 Quick Reference

[Solved] VBA: Code to select all sheets in a workbook

I need to select all the sheets in a workbook so I can print them together as double sided (duplex) copies.


Post your answer or comment

comments powered by Disqus
Hi there. I am a VBA novice in desperate need of some simple help.
I need to be able to select all sheets in a workbook in order to export them to another file.
I have recorded a macro that essentially does what I need, but it is not flexible enough.
Whilst recording I used the "select all sheets" option that you get when right clicking on a sheet tab.
In VBA this selects the sheets by name "sheet 1", "sheet 2", "sheet 3" etc.
My problem is that I want it to select all of the sheets in the workbook, whether there be 3 or 30 and whatever they are called.
I imagine that there must be some simple code that can do this but have no idea how to write it.
In theory I should be able to re-write the code in the recorded macro telling it to select every worksheet and the problem is solved.
Is this the case? Can you help?
Thanks in advance, Paul.

Hi

I am learning VBA code now through a book. Can someone please tell me what code to write when i want to select all worksheets in a workbook?

From marco recording, it was
But what to write when i actually enter the code myself?

Thanks.

I have been recording different sets of commands then copying/pasteing them together to complete what I want. I have tried to clean it up as best I can but im sure it does things it does not need to. Is there a way to tell it to select all sheets in "Book2.xls" then remove the formatting (font color,comments,shading) rather than using the Array and naming each and every sheet?

What this will end up doing is openeing about 12 files, copying specific tabs from each of those files and pasteing them all to the same workbook (in this example it would be Book2). Then removing the formatting.

Code:
    Workbooks.Open Filename:="F:123Book2.xls"
    Windows("Book1.xls").Activate
    Cells.Select
    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy Before:=Workbooks("Book2.xls").Sheets(1)
    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
    Cells.Select
    Selection.Interior.ColorIndex = xlNone
    Selection.Font.ColorIndex = 0
    Selection.ClearComments


I was looking for some advice on VBA code to hide/unhide sheets in a dynamic named range. I have a monthly workbook (start a new workbook the first day of each month) and among the many sheets there exists one sheet for each potential day of the month, 1,2,3, . . . ,30,31. I already have dynamic named ranges with the names of the sheets that are weekdays and weekends respectively. For example, for June 2012, the weekdays dynamic range is a 22 X 1 array {1,4,5,6,7,8,11,12,13,14,15,18,19,20,21,22,25,26,27,28,29} and the weekends named range is a 8 X 1 array {2,3,9,10,16,17,23,24}. I would like to have code that hides/unhides both/either/or of these dynamic named ranges. I looked through previous postings and couldn't find something applicable. Any suggestions would be appreciated. Thanks.

Is there a quick code to select all worksheets in a workbook....no sheet is hidden....i want to use cell.select - but do it for everysheet and not just one sheet...

thank you for any help

Anyone know the code to group all Sheets in a workbook??...trying to do
the same page setup on all sheets in a workbook and need to group them
together first.

TIA.
-mike

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

There must be a way to format all sheets in a workbook with the same footer
format (i.e. file name, tab and date) at once rather than tab by tab. Can
anyone tell me how?

Hi,

Is there a way to delete all sheets in a workbook after a certain period, say a month? Any idea? Thanks.

Langwo

Please tell me how to write the script to select all shapes in a sheet in VBA (without knowing the name of the shapes). Thanks

Hi,

Can someone please let me know what VBA code to write when you want to select all worksheets except a few. An example:

Based on the above code, how to rewrite the VBA code if i dont want 90E04 and 90E09?

Thanks

How do you select all sheets in a macro??

I know you can do this:

sheets(Array("Sheet1", "Sheet2", "Sheet3")).select

The problem I'm having with the above code is if I add another sheet that sheet is not selected in the macro. Also if a sheet is deleted the macro crashes.

Is there a way to select all sheets regardless of adding and deleting sheets??

Thanks, Matt

Sorry for the double post, my computer froze.

Hello,

I am trying to make certain changes to the page setup on all sheets in a workbook. Here is my code but something is not working. I think I need to somehow select the next sheet because the macro is only performing the task on the active worksheet. My Visual Basic skills are somewhat limited so I'm sure this is not the most efficient way. Any help would be greatly appreciated.

Sub AlignFooter()
    Dim Sheet As Worksheet
    For Each Sheet In ActiveWorkbook.Worksheets
        With ActiveSheet.PageSetup
            .FooterMargin = Application.InchesToPoints(0.25)
            .ScaleWithDocHeaderFooter = False
            .AlignMarginsHeaderFooter = False
        End With
    Next Sheet
End Sub

Thanks,
AJO

Hi everyone,

I'm trying to get a macro that upon activation will select all sheets in a workbook and change their view to the "Normal View" rather than the Page Break View etc..

The macro will be used in multiple workbooks, each containing differing worksheet names. The code I've got so far specifies each sheet in the workbook by name, rather than simply selecting the entire workbook or "all sheets". Can anyone help to make this macro a more generic one that I can use across the board please?

Sub NormalView()
'
' NormalView Macro
' This Macro will return the all sheets in the workbook to the normal view.
'
    Sheets(Array("How to Use", "Front Page", "General Instructions", _
        "Petroleum exploration", "Comments and time taken", "How to submit")).Select
    Sheets("How to Use").Activate
    ActiveWindow.View = xlNormalView
End Sub


Hi,

There is any help to find a value and activate the range through all sheets in a workbook.

Coz find code only for single sheet.

Thanks in advance.

I have this macro to creat a sheet named "XYZ" and then adds all sheets within the workbook. However, I do not want to include a sheet named "ABC".

Combine all sheets in a workbook except the sheet named "ABC". is my question. Can you help me on this??

Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "XYZ"

For J = 2 To Sheets.Count
With Sheets(J).UsedRange
.Resize(.Rows.Count).Copy _
Sheets(1).Range("A65536").End(xlUp)(3)
End With
Next

Application.DisplayAlerts = False

Hello all,

What's the right way to parse all sheets in a workbook? I want to put some code into a loop that runs through all the sheets in the current workbook, something like...

Code:
For Each sheet in CurrentWorkbook
 'blah blah blah
Next Sheet
FYI, the names and number of sheets is variable.

Thanks!

Hi, this is my first time posting, I have been reading the forum for at least 4 hours and havent found the answer I need so I apologise if this is something that comes up regularly.

Ok my problem is I need to automatically include details from all sheets in a workbook in excel if a certain condition is met.

I.e. My workbook is around 100+ worksheets in size. Each worksheet contains details of houses being rented, and payments being made on those houses. Each worksheet has various and many calculations which I wont bore you with but anyway the main calculation is whether someone has paid their rent and if they are in arrears.

What I am trying to do is get excel to search each individual worksheet (each worksheet name is the name/street of the house being rented) if cell B1 is YES then to add the information in cells A5, A4, B4, C4, P10, P12, C6, J13 and J14 to a new worksheet called arrears.

I am an advanced user of excel so I can live with most of what you chuck at me, I appreciate all the help you give me and if you can possibly forgive me for being cheeky, I would rather have the answer using functions (if its possible) and I would also like the calculation to be done when I requested - ie when I open the worksheet. I dont want too much do I lol.

Macros for Protect/Unprotect all sheets in a workbook

--------------------------------------------------------------------------------

I have been using the macros below for Protecting/Unprotecting all
sheets in a workbook, they work ok unless the sheets are password
protected, in which case the Unprotect drop down appears and I have to
enter the password for each sheet

Is there any way to change the macros so that irrespective of how many
sheets there are I only have to enter the password once to either
unprotect them all or protect them all

If possible the macro should still run for sheets with no password

Sub unprotect_all()
Dim wks As Worksheet
On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
wks.Unprotect
Next
End Sub

Sub protect_all()
Dim wks As Worksheet
On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
wks.Protect
Next
End Sub

--
Paul Sheppard
------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=392836

Macros for Protect/Unprotect all sheets in a workbook

--------------------------------------------------------------------------------

I have been using the macros below for Protecting/Unprotecting all sheets in a workbook, they work ok unless the sheets are password protected, in which case the Unprotect drop down appears and I have to enter the password for each sheet

Is there any way to change the macros so that irrespective of how many sheets there are I only have to enter the password once to either unprotect them all or protect them all

If possible the macro should still run for sheets with no password

Sub unprotect_all()
Dim wks As Worksheet
On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
wks.Unprotect
Next
End Sub

Sub protect_all()
Dim wks As Worksheet
On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
wks.Protect
Next
End Sub

Hi All,

Is there a way to format all sheets in a workbook to be the same. I find myself having to go into page set-up change from portrait to landscape, change my margins etc. in a workbook I produce weekly.

I was wondering if there is a way to set-up all sheets on a workbook to my predetermined defaults?

Thx

Gary

how would you copy and paste the values for all sheets in a workbook?

Thanks,

Joshua

I need some help with writing a macro which can prevent th euser to add
additional sheets in a workbook.

thanks

how to hide work sheets in a workbook

Hi everyone,

I am very puzzled at a basic question as the title suggests and what's worse is that google doesn't seem to be helpful either:

How to list all querytables in a workbook?

I know I could skim every cell on every worksheet to locate them but it's very tedious and they are not very easy to spot either so a list of them in an Excel workbook would be very helpful to overview how many querytables are there and where they're located on the worksheets so as to make maintenance much easier, e.g. amend existing queries.

Thanks in advance for your kind help
Frank


No luck finding an answer? You could always try Google.