MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Selecting all worksheets

  Asked By: Chigaru    Date: Sep 10    Category: MS Office    Views: 1166

I wish to create a shortcut macro to select all worksheets in a given
workbook. that way I can copy and paste values only and save it. I
tired searching, I am sure it has been asked a hundred times before.



5 Answers Found

Answer #1    Answered By: Scarlett Hughes     Answered On: Sep 10

I don't think you're going to find a way to do that all in one shot.
It sounds like if you have three sheets in your workbook, you want to select  all
three sheets, copy  them to the clipboard, then paste  back the "values".
the problem is that Excel expects a location to paste them.

The best you can do is set up a loop that cycles through all of the sheets,
selects the data, does the pastespecial, then moves to the next sheet.

I don't think you're going to find what you're looking for, because copy/paste
isn't an option with worksheets, but with the CONTENT of worksheets.

If you want help with the loop, let me know.

Answer #2    Answered By: Marina Smith     Answered On: Sep 10

if you want
to select  all the worksheets  and paste  them back to THEMSELVES to
get rid of all the formulas, you can do that with:

Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

but, if you need to copy  all the sheets to ANOTHER workbook, then
you're right, because you can't paste all the sheets at the same
time into a new location. For that, a loop would be needed, as you
said so far as I know.

Answer #3    Answered By: Verner Fischer     Answered On: Sep 10

i'm not exactly sure the vba part but you probably can record it... you can
group sheets together (ctrl + each tab) that you want .. then you should be
able to move/copy them as a group to a new workbook..

i'll try later if you want or you can try it yourself...

Answer #4    Answered By: Luz Hayes     Answered On: Sep 10

I was looking to copy/paste-values in the same sheet then save  as a
different version.

Answer #5    Answered By: Vidos Fischer     Answered On: Sep 10

Actually the answer was so simple I could not believe it:

Sheets.select will link all the worksheets  in the workbook. Then
cell.select highlights all the content in all the cells, and since
all are linked, you have all the sheets doing the same. Then Copy-
paste-values! Then saveAs, done!

Didn't find what you were looking for? Find more on Selecting all worksheets Or get search suggestion and latest updates.