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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

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,344
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,657
Messages
5,838,632
Members
430,558
Latest member
Krampus

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