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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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 ?
 
Upvote 0
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.:)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top