Email:      Pass:    Pass?
Hi!
Close Window   
TE
Free Excel Tips in Your Email!
Join Over 20,000 Happy Subscribers!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


Free Excel Forum

Excel Shortcut- Switch To Previous Sheet

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I wanted a shortcut that moved between the last two sheets selected.

Similar to how Alt+Tab works with windows.

example: I have Sheet1 until Sheet 10, I work in Sheet2 then I switch my work to Sheet10, I want to back to Sheet2 quickly by using shortcut. Not use CTRL + PageUp because it just move to one previous sheet from Sheet10 to Sheet9.
Its really helpful if we have many sheet & need to work quickly.

Thanks

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Reverse Row or Column Order in a Worksheet
- This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Replace Formulas with Values (For The Entire Workbook)
- This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to

Similar Topics







I am trying to create a macro that will switch tabs within a workbook. Currently I have 2 shortcuts set ctrl + q and ctrl + w to just switch back and forth between 2 sheets that I switch back and forth from often. The code I currently have just goes to a sheet with a specific name. Is there a way to program it to go to the next tab? Will there be a problem when I reach the last tab or do I need to send it back to the first tab when it reaches a certain count or the end?

Thanks,

Rob


I've this cell(M4) where it calculate from sheet 1 to sheet 72..
as I key untill sheet 47, excel prompt me formula too long.

If there a shortcut which I don't have to keyin all 72 sheet. Thanks.


Formula
=SUM(IF(Sheet1!M4=4,1,0)+IF(Sheet2!M4=4,1,0)+IF(Sheet3!M4=4,1,0) +IF(Sheet4!M4=4,1,0)+IF(Sheet5!M4=4,1,0)+IF(Sheet6!M4=4,1,0) +IF(Sheet7!M4=4,1,0)+IF(Sheet8!M4=4,1,0) +IF(Sheet9!M4=4,1,0)+IF(Sheet10!M4=4,1,0)+IF(Sheet11!M4=4,1,0) +IF(Sheet12!M4=4,1,0) +IF(Sheet13!M4=4,1,0)+IF(Sheet14!M4=4,1,0)+IF(Sheet15!M4=4,1,0)+IF(Sheet16!M4=4,1,0) +IF(Sheet17!M4=4,1,0)+IF(Sheet18!M4=4,1,0)+IF(Sheet19!M4=4,1,0)+IF(Sheet20!M4=4,1,0) +IF(Sheet21!M4=4,1,0) +IF(Sheet22!M4=4,1,0)+IF(Sheet23!M4=4,1,0)+IF(Sheet24!M4=4,1,0)+IF(Sheet25!M4=4,1,0) +IF(Sheet26!M4=4,1,0) +IF(Sheet27!M4=4,1,0) +IF(Sheet28!M4=4,1,0)+IF(Sheet29!M4=4,1,0)+IF(Sheet30!M4=4,1,0) +IF(Sheet31!M4=4,1,0) +IF(Sheet32!M4=4,1,0)+IF(Sheet33!M4=4,1,0)+IF(Sheet34!M4=4,1,0)+IF(Sheet35!M4=4,1,0) +IF(Sheet36!M4=4,1,0)+IF(Sheet37!M4=4,1,0)+IF(Sheet38!M4=4,1,0)+IF(Sheet39!M4=4,1,0) +IF(Sheet40!M4=4,1,0) +IF(Sheet41!M4=4,1,0)+IF(Sheet42!M4=4,1,0)+IF(Sheet43!M4=4,1,0)+IF(Sheet44!M4=4,1,0) +IF(Sheet45!M4=4,1,0) +IF(Sheet46!M4=4,1,0) +IF(Sheet47!M4=4,1,0) )


Is there any keyboard shortcut to move forward and backward between previous location within excel sheet?

If no such shortcut, is there an add-on to achieve this?

Thanks.


Hi everyone,

Could some one amend the VBA below to allow a continous loop on moving to the previous sheet within a workbook. Currently when I move to the first sheet if I use the shortcut key I get a debug error.

Many thanks.


Code:

Sub Page_Up()
'
' Page_Up Macro
' Macro recorded 13/01/2009 by admin
'
' Keyboard Shortcut: Ctrl+q
'
    ActiveSheet.Previous.Select
End Sub





I recently got a new laptop, and the shortcut key Ctrl+Alt+F3 to bring up the "New Name" box no longer works. I went back to my old laptop to make sure I'm not crazy, and the shortcut works there, still. The other F3 shortcuts work okay (Ctrl+F3 for the "Name Manager" and Ctrl+Shift+F3 for "Create Name").

I've searched the web for discussions about this shortcut, and I can only find a handul of posts that discuss Ctrl+Alt+F3 as a shortcut. Any ideas?

Thanks!


Good afternoon.

I'd like to ask you if there is any shortcut in Excel to move between last used sheet and now used/active sheet.
(Similar like in Windows aplication Alt+Tab (Ctrl+Tab).)

I know there is only shortcut for moving up or down among sheets (Ctrl+Page Up, Ctrl+Page Down). But I don't want this.


I've seen several posts for how to sum the same cells over multiple worksheets, but what I'm trying to do is sum different cells over multiple worksheets. I know what the syntax would be if I was to manually type in the formula. For instance if I wanted to sum A1 to A5 on sheet 1, B2 to B3 on sheet 2, and A20 to D20 on sheet 3, it would be

SUM(Sheet1!A1:A5, Sheet2!B2:B3, Sheet3!A20:D20)

but how can I do that by simply highlighting the desired cells and using the summation button? I would've thought that it would go like so: highlight the cell you want the summation to appear in, then click the summation button in the toolbar, then highlight the range of cells on that sheet you want to include, then switch to the next sheet and hold control while highlighting the cells on that sheet, and continue like that for the rest of the sheets. But of course that doesn't work.

The closest thing I have found so far is this: click the summation button and highlight the cells on that sheet you want to include, then press comma, then switch to the next sheet and do the same thing, pressing comma in order to switch between sheets. This method works, but there must be a more intuitive way to do it using shift or control, right?


Hi,

This problem seems stupid but I really in trouble and need your help. Currently, I used Windows 7 with Microsoft Excel 2010 in Lenovo Thinkpad laptop. I notice that I cannot use an Excel keyboard shortcut to add row, delete row and other shortcut i.e. remove border

Normally, I used following shortcut;

- Ctrl + shift + '=' (beside backspace) or Ctrl + '+' will add new row to the row you currently select in Excel.
- Ctrl + '-' (beside '0' number) will remove a select row in Excel.

However, now I can't use any of those shortcut anymore. I have to turn on "numlock" and use '/' button which very complicate for me.

I try other machine that have Windows 7 and Excel 2007 and it came out that the above shortcut work fine. My old system use Windows XP and Excel 2010 also work fine as well.

Anybody know what's happen with my system?


I have protected one of the worksheets in my workbook, so that it's only possible to select unlocked cells.

If I start on an unprotected sheet, then I can use Ctrl + Page Up / Down to navigate to the protected sheet as usual.

However, once I'm in the protected sheet, I get stuck there and the shortcut stops working. Instead of navigating to the next sheet, the shortcut moves me over to column AF in the same sheet!

What's happening!?!

Thanks, Helen

PS I haven't had any suggestions yet. So I've attached an example workbook. If you open it up and try navigating through the sheets with Ctrl + Page Down, you'll see it doesn't work. I would really appreciate an explanation for why the shortcut doesn't work anymore. Anyone any ideas?? Is there any way I can work round it to make the shortcut key work again?

Thanks again, Helen


Hello again . . .

It seems the shortcut for navigating between sheets will work if I allow the user to "select unlocked cells" and "select locked cells" when I protect the sheet. That way the user needs to tab between the unlocked cells instead of using the arrow keys. But at least the navigation shortcut between sheets doesn't stop working!

I would still be interested to hear from anyone who has any ideas about why the shortcut stops working if I don't allow the user to "select locked cells". Is it just one of the those funny Excel inconsistencies???

Thanks for those of you who pondered over this query. /Helen


Hi,

I'm quite new here and a self taught beginner so forgive me if my phrasing is poor and question rather simple.

I'll explain as best I can:

I have a work book with 10 sheet all with the same input set up to record figures. As it stands I have a summary sheet at the end that totals values from the 10 sheets using the following code:

=IF(SUM(Sheet1:Sheet10!C3)>0, SUM(Sheet1:Sheet10!C3),"")
=IF(SUM(Sheet1:Sheet10!C4)>0, SUM(Sheet1:Sheet10!C4),"")
=IF(SUM(Sheet1:Sheet10!C5)>0, SUM(Sheet1:Sheet10!C5),"")

and so on up to C30

I want to divide this information into 2 different sub totals, using the data validation option I have a list selection cell on each sheet with 2 options, lets call them "option a" and "option b" This list cell is located in J43 on all sheets.

As each sheet will be designated 1 or the another, I was hoping to be able to total up the values of just the option a's and then just the option b's. The part where it checks that the value is greater than zero isn't compulsory

I'll express my desire in terms of my dodgy attempt at what I think the code could potentially look like:

=SUMIF(Sheet1:Sheet10, J43="option a", Sheet1:Sheet10!C3)

So to reiterate, I want to add the values in cell C3 on the sheets sheets where cell J43 equals option a.

From there I can extrapolate the code myself to work in the neighboring cells and reverse the option to obtain all of the option b's.

Thank you for your time,

Mick.


I have an existing working with 2 sheets that have names...

When my VBA ads a new sheet, it might be sheet1 or sheet3 or sheet10, depends where I am in my process.

I want to move the sheet added after the template sheet, that part works, but then I want to select the sheet with the name Sheet(whatever number) sheet1 or sheet10 and rename it.

I am not sure how to select that, how would that work ? "sheet*" ?

Sheets.Add
Sheets(1).Select
Sheets(1).Move After:=Sheets("template")
Sheets("Sheet").Name = "Custom Segment Results"
ActiveWorkbook.Sheets("Custom Segment Results").Tab.ColorIndex = 43 ' turn Tab Green color


?

Thanks!


Hi guys,

I have read many threads about members trying to navigate their way around workbook sheets by using a Combobox. There seems to be many alternatives but not a simple solution, can anybody help please?

My problem is this:

I have 10 sheets (Sheet1 to Sheet10)
I would like to have a ComboBox in Sheet1 which contains Sheet2 to Sheet5 and when selected jumps to that sheet.
I would also like a ComboBox in Sheet6 which contains Sheet7 to Sheet10 and when selected jumps to that sheet.

I can create buttons and use Macros but my end product will have certain sheets with many sheet selections. This will be used by people with no Excel experience so a ComboBox will make things look and feel a lot cleaner and more professional.

My knowledge of VBA is limited, so any help and simple guidance would be greatly appreciated.

Thank you in advance.


Hi Friends,

I want shortcut key which will save my time

see i have a worksheet and sheet1 contain all the link formulas which is in sheet2
so when i edit a2 in sheet1 it show me "=Sheet2!A2".

Now i want to know is there any kind of shortcut key that when i edit a2 in sheet1 and press any shortcut key then it will goes to sheet2 A2


Hello,

Is there a keyboard shortcut in Excel 2007 that will return me to the previous cell I was just at? After doing a ctrl [ to see the source of a cell, I want to be able to go back to where I was at originally; however, the ctrl [ doesn't work since the new cell likely has other dependents. Thanks.


Hi,
I have been searching for similar questions to my pain but most answers are for more advance request.
I have a workbook with 20 sheets. the sheets are from sheets 1-20.
I would like to print the following:
Sheet1, Sheet3, Sheet5,Sheet8,Sheet9,Sheet10,Sheet11,Sheet12 and Sheet14. I also want to repeat a few sheets. So in the end I really want this:

Sheet1, Sheet3, Sheet5,Sheet8,Sheet9,Sheet10,Sheet11,Sheet12 and Sheet14,Sheet1,Sheet1,Sheet2.

I found a neat VBA code

VB:

 
Sub prnt() 
    Sheets(Array(1, 3, 5, 8, 9, 10, 11, 12, 14, 1, 1, 2)).PrintOut 
End Sub 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



But it does not reprint the repeated sheets like 1 & 2.
Does anyone know what I am doing wrong?

Thanks so much

Can anyone tell me how to change the shortcut in excel for switching worksheets?

At the moment, I have to use ctrl + Page up or down, Can I change to other shortcut.

Just too lazy to use two hands.


Hi Guys for some reason

when i go to print preview and then get back out of it. It leavesquare lines everywhere (after certain columns) but i cant seem to get this off. I need to be able to look at print preview and then go back to normal screen when i exit.

The other problem i have is that at work there are certain keyboard shortcuts assigned to application i am using at work (say windows).

In excel i am trying to use certain shortcuts such as ctrl + F3 CNTRL + SHIFT + F3 ETC... However it does not use the excel shortcut but the shortcut for windows..It appears to be disabled for excel as it used whats set up as a shortcut for the windows.

Another problem i have is that certain formulas i enter (i.e mid(c9,row(indirect("1":len(c9))),1) gives and error of (there are too many nested in the formula and therefore can not save in the current file name or something like that)

slightly confused


a simple question ....
is there a shortcut for navigating between 2 worksheets
in the same workbook...
for eg if i am copying some values from sheet 1 into sheet 2 of the same workbook...wat i am doing now is clicking sheet1 copying the value then clicking sheet2 and pasting the value in the cell i want..
hope u got it...
i want some shortcut ( if its there ) like ALT+TAB which is for navigating between different open windows.
thanks in advance for any help


Hello

Iv made a macros with the shortcut as Ctrl+Shift+D

Im trying to get it to work but whenever i press Ctrl+Shift+D it gives me Todays date.

I know excel has built in shortcuts such as Ctrl+Shift+D and Ctrl+Shift+T but i was always under the impression that macros shortcuts overrule them. (I checked using options that the shortcut was assigned to Ctrl+Shift+D)

Is there any way whereby i can get my macros to work using Ctrl+Shift+D cos im sure i saw someone using Ctrl+Shift+D as a shortcut.

Just incase - The macros which im using is a Trace Dependents one with the following code

VB:

 
Sub Dependents() 
     '
     ' Dependents Macro
     '
     ' Keyboard Shortcut: Ctrl+Shift+D
     '
    Selection.ShowDependents 
End Sub 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines




I'm trying to have one of my menu buttons use a shortcut key for me to use,
but aside from the main ones in the standard menu toolbar [File, Edit, View,
Insert...], I don't know how to quickly recognize what shortcut keys are
already in use.

Is there a way to do this... to find out what shortcut keys are already in
use?

If I had more time I would search through these posts but I have to get some
things finished quickly, so am hoping that someone will be able to share some
knowledge today or tomorrow [or whenever, I'm not too picky on being helped].

Any help would be very much appreciated.
thank you.



Hello all

Just wondering if there is any shortcut to go back to the previous cell, like in word Shift+F5 takes back to the previous word then again to the previous one and so on.

I tried Ctrl+Backspace which i read but its not working

Thanks


I am using Windows XP Pro SP1a with Excel 2003. I created a shortcut to a
file I use alot, and placed it on my desktop. This location is on a network
drive. When someone moves the file to a different location on the same
mapped network drive, when I run my shortcut link, it opens the Excel file -
yet external file location links that are referenced inside this Excel
sheet, are no longer good.

How can this Excel shortcut know where this file resides after it has been
moved? Is this built-in functionality for Excel 2003? For Windows XP Pro?

When I move the Excel file, I want my shortcut to the file that is on my
desktop, to no longer work.

TIA,

-Tom






Hello Folks,

I am trying to figure out a way to jump to the previous selected tab using a macro. For example, I select Sheet1, enter some data there, then click on Sheet2. I want to be able to run a macro at this point to go back to the last selected cell on Sheet1. If I then ran my macro again, it would take me back to Sheet2.

The reason this would be helpful is I have a sheet with 30+ tabs and it gets old flipping back and forth between sheets.

Is this possible? I was thinking maybe some kind of named range but couldn't figure it out.

I am using Excel 2003 SP2.

Thanks!


Hi there,

I have a macro and I'm sure it's really messy so I'm hoping someone can fix it up for me!

Basically, I have a combobox that is linked to a cell, if the value in that cell equals 1, sheet 1 unhides and sheets 2-10 are hidden. If the value is 6, Page 1-6 are unhidden and page 7-10 are hidden.

Quote:

Sub Rooms()
Application.ScreenUpdating = False
If Range("RoomCheck").Value = 1 Then
Call Unhide
Sheets(Array("Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8", _
"Sheet9", "Sheet10")).Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Sheet1").Visible = True
End If

If Range("RoomCheck").Value = 2 Then
Call Unhide
Sheets(Array("Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8", _
"Sheet9", "Sheet10")).Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Sheet1").Visible = True
Sheets("Sheet2").Visible = True
End If
If Range("RoomCheck").Value = 3 Then
Call Unhide
Sheets(Array("Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8", _
"Sheet9", "Sheet10")).Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Sheet1").Visible = True
Sheets("Sheet2").Visible = True
Sheets("Sheet3").Visible = True

End If
If Range("RoomCheck").Value = 4 Then
Call Unhide
Sheets(Array("Sheet5", "Sheet6", "Sheet7", "Sheet8", _
"Sheet9", "Sheet10")).Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Sheet1").Visible = True
Sheets("Sheet2").Visible = True
Sheets("Sheet3").Visible = True
Sheets("Sheet4").Visible = True

End If

If Range("RoomCheck").Value = 5 Then
Call Unhide
Sheets(Array("Sheet6", "Sheet7", "Sheet8", _
"Sheet9", "Sheet10")).Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Sheet1").Visible = True
Sheets("Sheet2").Visible = True
Sheets("Sheet3").Visible = True
Sheets("Sheet4").Visible = True
Sheets("Sheet5").Visible = True

End If

If Range("RoomCheck").Value = 6 Then
Call Unhide
Sheets(Array("Sheet7", "Sheet8", _
"Sheet9", "Sheet10")).Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Sheet1").Visible = True
Sheets("Sheet2").Visible = True
Sheets("Sheet3").Visible = True
Sheets("Sheet4").Visible = True
Sheets("Sheet5").Visible = True
Sheets("Sheet6").Visible = True

End If

If Range("RoomCheck").Value = 7 Then
Call Unhide
Sheets(Array("Sheet8", _
"Sheet9", "Sheet10")).Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Sheet1").Visible = True
Sheets("Sheet2").Visible = True
Sheets("Sheet3").Visible = True
Sheets("Sheet4").Visible = True
Sheets("Sheet5").Visible = True
Sheets("Sheet6").Visible = True
Sheets("Sheet7").Visible = True

End If

If Range("RoomCheck").Value = 8 Then
Call Unhide
Sheets(Array("Sheet9", "Sheet10")).Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Sheet1").Visible = True
Sheets("Sheet2").Visible = True
Sheets("Sheet3").Visible = True
Sheets("Sheet4").Visible = True
Sheets("Sheet5").Visible = True
Sheets("Sheet6").Visible = True
Sheets("Sheet7").Visible = True
Sheets("Sheet8").Visible = True

End If

If Range("RoomCheck").Value = 9 Then
Call Unhide
Sheets(Array("Sheet10")).Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Sheet1").Visible = True
Sheets("Sheet2").Visible = True
Sheets("Sheet3").Visible = True
Sheets("Sheet4").Visible = True
Sheets("Sheet5").Visible = True
Sheets("Sheet6").Visible = True
Sheets("Sheet7").Visible = True
Sheets("Sheet8").Visible = True
Sheets("Sheet9").Visible = True

End If

If Range("RoomCheck").Value = 10 Then
Call Unhide
End If

Application.ScreenUpdating = True
End Sub

Quote:

Sub Unhide()
Application.ScreenUpdating = False

Sheets("Sheet1").Visible = True
Sheets("Sheet2").Visible = True
Sheets("Sheet3").Visible = True
Sheets("Sheet4").Visible = True
Sheets("Sheet5").Visible = True
Sheets("Sheet6").Visible = True
Sheets("Sheet7").Visible = True
Sheets("Sheet8").Visible = True
Sheets("Sheet9").Visible = True
Sheets("Sheet10").Visible = True
End Sub

Any help would be appreciated... :]


I made a bunch of macros, and I turned them into add ins, however the macro had a shortcut of ctrl+e and ctrl+m. when i first add the add ins to excel they work fine, but if i close excel and reopen it with another form that the add in is compatible with, the shortcut for the add in does not work. i have to uncheck the add in, hit ok, then recheck the add in for it to work. anyone have any ideas? these add ins are going to be on multiple computers so i need to get this resolved. any help is greatly appreciated!