routine to move sheets (and switching between sheets)

Barbarte

Board Regular
Joined
Dec 13, 2006
Messages
125
Hi everyone,

I'm writing some code that inserts two new sheets into workbook.
then I'm gonna copy the information from all the other sheets into the two new ones (they're called "DT and "HD").

However, I want to move the two new sheets to the end of the list, and tell the loop to finish when the sheet named "DT" is selected.

the best I've come up with so far is this:

Code:
Sheets("HD").Move After:=Sheets(11)

The trouble is that not all the workbooks I'l be using this on end on sheet 11.

how can I tell excel to put the sheets at the end of the list of sheet tabs?

I'm using:

Code:
ActiveSheet.Next.Select

to loop though the sheets.

thanks,

Paul
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Sheets("HD").Move After:=Worksheets(Worksheets.Count)
 

Barbarte

Board Regular
Joined
Dec 13, 2006
Messages
125
OK,

I thought
Code:
sheet.previous.select
would select the previously active sheet.

but it doesn't, it selects the previous sheet in the list.

Is there way to switch between two sheets the way you can switch between two workbooks with Alt + Tab ?
 

Barbarte

Board Regular
Joined
Dec 13, 2006
Messages
125

ADVERTISEMENT

no one? :(
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,267
Office Version
  1. 365
Platform
  1. Windows
Paul

Why do you need to select the sheet in the first place?

It's not normally (ever?) necessary to select sheets/ranges/etc to work with them.:)
 

Barbarte

Board Regular
Joined
Dec 13, 2006
Messages
125
Yeah, I'm still trying to get my head around that whole concept :oops:
I done a 3 day VBA course a couple of months ago, so everything I know is based on that.
I'll have to read up a bit more on VBA in my own time.
don't often have time to do it during working hours.

I figured out a way to do it:

Code:
Sub GrabLabelCopySheets()

MakeDTandHD

Dim rowno As Long
Dim sheettotal As Long
Dim sheettotal2 As Long


sheettotal = Worksheets.Count

rowno = 2

Do Until ActiveSheet.Name = "DT"

    TransferLabelCopy
     
    sheettotal2 = sheettotal - (sheettotal - rowno)

    Sheets(sheettotal2).Select

    rowno = rowno + 1

Loop


End Sub


that's probably an extremely long winded way of doing it, but it works.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,328
Messages
5,624,036
Members
416,007
Latest member
csf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top