MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Next Workbook or Previous Workbook

  Asked By: Violet    Date: Sep 17    Category: MS Office    Views: 1222

How do I code to go to next workbook or previous workbook ?

I have a proc that copies data to buffer, then opens a specific
workbook, inserts a new worksheet, and pastes the data.

I want it (preferably) to go back to the previous workbook
programmatically using VBA instead of ALT+TAB

Any help would be greatly appreciated...



5 Answers Found

Answer #1    Answered By: Vid Fischer     Answered On: Sep 17

You could use workbooks("name.xls").activate. with the name of the original
workbook instead of name.

If the name might change you could capture it before opening the other one


then after the paste use


Answer #2    Answered By: Daniel Costa     Answered On: Sep 17

I'll give that a try - thanks !

Answer #3    Answered By: Grace Ellis     Answered On: Sep 17

It didn't work for me - was I supposed to dimension something ? I
was wondering why you have workbooks instead of worksheets - maybe I
didn't explain - I only have one workbook.

Say its called USA.xls - and I have 50 tabs with USA_Summary the
first tab on the left - the second tab is AK, the third is AL the
fourth is AZ, and so on - If I'm on TX , and I copy and paste to
USA_Summary tab, I want to be able to go back  to TX.

Sorry I wasn't more clear.

Answer #4    Answered By: Alisha Johnson     Answered On: Sep 17

You said "How do I code  to go to next workbook  or previous  workbook ?" That
is why we were giving you code to do that.

If you want to activate a worksheet  you use


Instead of "Sheetname" you can use a variable which holds a string equal to
the sheet name. You can also use a number representing the position of the
sheet in the workbook but I would only use this if I was cycling through all
the sheets in for loop.

So while you are on the source sheet you could set a variable (say, PWkSheet)
equal to the name of the current worksheet. Then you could use the variable
to activate that sheet.

Sub something()
Dim PWkSheet As String
' other code
PWkSheet = ActiveWorkbook.ActiveSheet.Name
' more code
End Sub

Answer #5    Answered By: Varick Fischer     Answered On: Sep 17

Relying on previous/next is not a good idea, I don't think.

If I need to refer to something again, I just grab a reference to it for
future use.

Set MasterWorkbook = ....

Then I can use without further trouble, regardless of where it is in the

Didn't find what you were looking for? Find more on Next Workbook or Previous Workbook Or get search suggestion and latest updates.