You are here:

Excel/Reverse order of worksheets


Brian wrote at 2009-12-20 02:07:25
I am using this subroutine using Excel 2003 and get the same result as Stephen. When I check for the total number of sheets it is correct. I've tried this in generating 4,5,10,20 & 8 sheets and get the same result every time (e.g. when Total = 5 I get Sheet5, Sheet1, Sheet2, Sheet3, Sheet4). I have also tried adding a another line after your code to move the leftmost sheet all the way to the right but in the example above it results in Sheet4, Sheet5, Sheet1, Sheet2, Sheet3 or Sheet5, Sheet4, etc., depending on whether I tell it Before or After.

Brian wrote at 2009-12-20 02:26:49
It occurred to me after my last addition that I failed to mention that what I am doing is through Visual Basic 2008. I am creating a spreadsheet with several sheets, placing data in each and then selecting a region of the data and creating a chart. I then select a second region of data and insert a new chart into each sheet. It is after this that I run you subroutine and get the results described before.

I just went into Excel 2003 and ran your macro on a group of sheets and it worked correctly! There is something about communicating via that COM that seems to cause different results.

Brian wrote at 2009-12-24 00:24:29
After recording a macro using Excel 2003 I rewrote it as a loop and came up with the following:

       Target = NewBook.Worksheets.Count - 1

       For n = 1 To Target

         NewBook.Worksheets("Sheet" & n).Select()

         NewBook.Worksheets("Sheet" & n).Move(Before:=NewBook.Worksheets(n))

       Next n

This works as a macro and in my VB 2008 program as well, no matter how many sheets are involved.

90210 wrote at 2012-03-19 21:00:55
Sub ReverseWorkSheetOrder()

Dim Target As Integer

Dim n As Integer

  Target = Sheets.Count

  Worksheets(1).Move After:=Worksheets(Target)

  For n = Target To 2 Step -1

      Worksheets(1).Move Before:=Worksheets(n)

  Next n

End Sub

Anita wrote at 2013-06-23 20:31:17
The line

Target = ThisWorkbook.Worksheets.Count

gives the number of sheets of the file the macro is defined in. Changing this line into

Target = ActiveWorkbook.Worksheets.Count

will probably give you the expected result.


About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Lawrence Marshall


Anything Excel - but specific in-depth (and unusual) knowledge in Excel VBA, and how to extract from it functionality that Bill Gates' team did not intentionally cater for (though they should have).


Old-school (mid-1980s) Machine-Code and later Assembler programmer working Z-80, 6502 and 8086 processors; as a commercial games programmer, I authored Interdictor Pilot (the very first real-time flight-and-fight space combat simulator) on TRS-80, C-64 and CPC-464 platforms. Also authored the games program Kamikaze-Khazi. Have been working in Excel VBA for 9 years, with a leaning towards business applications. I have a current commercial, specialist Excel VBA application licensed to clients requiring an environment that delivers integrated requirements engineering and assessment of competing bids on large scale procurement projects (typically 1M - 400M); it is licensed at 10k. It is quite large (~ 6 mB) and is very powerful - it has Excel performing feats beyond its design spec and integrates with MS Word to produce a wide variety of interactive print-ready outputs.

UK Masters-degree level Aerosystems

Past/Present Clients
UK Ministry of Defence (DPA, DLO and DE&S), BAE Systems, Northrop Grumman, Lockheed Martin, ULTRA Defence Systems, Offshore International, Signature Industries, Fernau Ltd, Rockwell Collins (UK)...

©2016 All rights reserved.