Free Microsoft Excel 2013 Quick Reference

[Solved] VBA : Switching between workbooks

Is there some way I can prevent a user from switching between workbooks?
In my code I open a workbook, and the user can adjust values on the new sheet, but I want to close and save the new workbook before returning to the previous one.

Please Help!


Workbook Name Changes – But Workbook Does Not For Five Seconds??

MS Office Excel 2003 / SP3 Installed

I have noticed an odd occurrence from time to time while switching between workbooks. When I switch from one workbook to another, (typically via the “Window” pull down – but the same result happens when switching workbooks on the tabs at the bottom of the screen), the “switched to” workbook name will instantly appear at the top of the application, but the actual switch to the selected workbook is delayed for several seconds, (five seconds or so).

While not a major issue, it is annoying. I suspect that it is a memory allocation issue, (I have a Duo CPU System / XP Pro / 2 GB RAM), and with that in mind I have both restated MS Excel and also performed a complete re-boot. While either option does (seem) to result in the issue being (temporally) removed from my machine, it does return. I may go for several days without this issue occurring, and then it appears for no known reason.

I am looking for a cause.

Any insight this esteemed contingent of intelligence could impart would be greatly appreciated.

Thank you

Choppork

1415 CDT

October 21, 2009

what is the most efficient way to switch between workbooks?

i have a program that fills up the summary sheet.

it opens up a file, extracts some data from specific cells, then closes it.

the program then goes onto the next file.

when i run the macro, it always stops at the point where i have opened the source file and reactivated the summary sheet file.

ex:


	VB:
	
ChDir "C:Documents and SettingsrreyesMy Documentstesttest2" 
Workbooks.Open FileName:= _ 
"C:Documents and SettingsrreyesMy Documentstesttest2MOVIEGALLERY.xls" 
Windows("Book4.xls").Activate 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
the program will stop at windows("book4.xls").activate.

i dont know why. there is no error number. it just says code execution has been interrupted. continue, end, debug, or help.

I'm having trouble finding a way to switch between two workbooks that I have open whose names are stored as variables. If the variable name were variable, for example, I have tried the following:

workbook(variable).activate
workbook.activate variable
workbook(variable).select
workbook.select variable

none of these work and I've tried a ton of other ways to get it to work but I just can't figure it out! Help would be much appreciated

I am working on a budget model where the model workbook is a read only file and it allows the user to select the data workbook they want to work on. This sub routine copies all of the data from the data workbook into the model workbook. I am using the Application.GetOpenFilename command to open the data workbook. That works fine. My problem is after the user has entered all of there budget data, they will click on a button that starts a new macro that will copy the data they just entered back to the data workbook and save it so the next time they go back into the model their data is where they left off. I can't get the sub routinue to do this part without having to use the Application.GetOpenFilename again in the code. This brings up the file open box again and they have to select the same data workbook they selected when they began or risk writing over another file.

How can I get this new sub to recoginize the two different workbooks without having to use the GetOpenFilename again. I want it to switch between the open workbooks?

I am open to any other commands that will do this. I am not locked in to GetOpenFilename.

Thanks

G'day, I have a question about switching between two open workbooks with a macro. I have a macro that is opening a workbook which has been defined as "sfile" using the following code:

Dim sDate As String
Dim sFile As String
sDate = Format(Range("B1"), "mm-dd-yy")

sFile = "S:dfw2GroupsCashAppsMellon Reports2008 Mellon ReportsRaw Downloads" & sDate & "
Prior.csv"
If Dir(sFile, vbNormal) = "" Then
   sFile = Application.GetOpenFilename("Excel Files (*.csv), *.csv")
   If sFile = "False" Then
      Exit Sub
   End If
End If
Now what I need to do is copy some data from the first workbook (the one containing the macro which is still open) into the workbook which has been designated "sfile". Normally that would be easy enough but I wasn't sure how to do it without specifically naming the "sfile" workbook; I need to keep this filename variable. I searched the forum and found the bit of code below, but am not sure how to modifiy it to meet my own needs. I believe this code is copying a range from the active workbook, into another workbook and I need to do just the opposite.

I would appreciate any help I can get.

Thanks!

I am using Office 2003 Pro on a Windows XP computer. I suddenly cannot copy
and paste between Excel workbooks. The function works fine within the
workbook and also between Excel and other applications. I have tried running
Excel in safe mode but have the same problem. I have used the repair function
of Office and have also uninstalled and reinstalled Excel but still have the
same problem.
I switch between workbooks by just clicking on the task bar.
If I have the Office Clipboard open, I can use that to paste to the other
workbook but cannot utilize the paste special commands.
Thanks for any help you can give.

Hi,

I'm trying to write a piece of VBA code which will switch back and forth between 2 workbooks, but I was hoping that I would not have to name both workbooks in my code.

So far I have been able to name the second file as a variable, open it and copy the data I need, but I now need to switch back to the original file. Is there any way of doing this without hard coding the file name?

Hi,

If I want to switch between (activate) different opened excel workbooks,
what code should I use in VBA?
Thanks.

Ivan

This one is driving me crazy.

I have a workbook that runs vba to start a solver routine on a sheet "Model" then transfers the results of the solver to column on a results sheet. The solver resets and then using lookup routine gets data form a readings sheet paste into the solver sheet and re runs solver.

This loop continues for continues for approx 70 cycles then ends.

All works fine but the screen constantly switching between the sheets looks very amateurish and annoys me!

I did try hiding the sheets at the start of the routine but this stopped the macro on an error.

This is the main code for this section.

	VB:
	
 75 
    dateprod = Cells(nbline, 1) 
     
    Worksheets("Model").Activate 
    Cells(3, 3) = dateprod 
    solverreset 
    stwobbe = Cells(36, 5) 
    Worksheets("Results").Activate 
    Cells(nbline, 4) = Application.WorksheetFunction.Max(0, stwobbe) 
    Worksheets("Model").Activate 
    solverok setcell:=Cells(36, 5), maxminval:=3, valueof:=Wobbelimit, bychange:=Cells(42, 16) 
    solversolve userfinish:=True 
    volume = Cells(42, 16) 
    massco2 = Cells(53, 6) 
    Worksheets("Results").Activate 
    Cells(nbline, 3) = Application.WorksheetFunction.Max(0, volume) 
    Cells(nbline, 5) = Application.WorksheetFunction.Max(0, massco2) 
     
     
     
Next nbline 

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

Anyone have any ideas.?

Or should I not be so fussy?

Thanks

Hi Excellers.
This is presenting a problem to me and I hope someone has an idea of how to work around this.

I have a workbook which hides most of Excel's native functionality including commandbars, rightclick menus, ect... This necessitated including a dropdown menu to list other open workbooks thus allowing the user to switch to the same and therefore returning Excel to normal functionality via code. This is fine but when the user returns to my workbook, I need to re-hide and return to the customized settings. This is achieved in a procedure which is called via the Workbook_Activate event. The problem is this event is not fired when ALT-TAB, an everyday Windows keyboard shortcut, is used to move between open workbooks. Has anybody else run into this and, if so, please share your solution before I go overboard and start disabling keys and such.
Thanks!
Tom

I'm new at this excel stuff. But I can't figure out how to switch between 2 existing worksheets in a workbook. I have the code all figured out but it creates a new worksheet called results but I want it to paste the info into an existing worksheet called results. Here is what I have so far:

Code:
Sub Extract_Data()
'this macro assumes that your first row of data is a header row.
'will copy a row from one worksheet, to another blank workbook
'IF there is a 0 in column N
'Variables used by the macro
Application.ScreenUpdating = False
Dim FilterCriteria
Dim CurrentFileName As String
Dim NewFileName As String

Set a = ActiveSheet
'Select Range
'(note you can change this to meet your requirements)
Range("b15:t4000").Select
'Apply Autofilter
Selection.AutoFilter
FilterCriteria = 3
'NOTE - this filter is on column N (field:=14), to change
'to a different column you need to change the field number
Selection.AutoFilter field:=4, Criteria1:=FilterCriteria
'Select the visible cells (the filtered data)
Selection.SpecialCells(xlCellTypeVisible).Select
'Copy the cells
Selection.Copy

'Open a new file
'Workbooks.Add Template:="Workbook"
'Get this file's name
'NewFileName = ActiveWorkbook.Name
Sheets.Add().Name = "Results"

Set B = ActiveSheet
'Make sure you are in cell A1
Range("a1").Select
'Paste the copied cells
ActiveSheet.Paste
Range("a1").Select 'unselect everything
'Clear the clipboard contents
Application.CutCopyMode = False
'Go back to the original file
'Workbooks(CurrentFileName).Activate
a.Select
Selection.AutoFilter field:=14, Criteria1:=FilterCriteria
Selection.SpecialCells(xlCellTypeVisible).Select
'Clear the autofilter
'Selection.AutoFilter field:=1
'Take the Autofilter off
Selection.AutoFilter
'Go to A1
'Range("b15").Select
Application.ScreenUpdating = True
End
End Sub
Here is the part I need help changing. I just want it to open a existing worksheet:
Code:
'Open a new file 
'Workbooks.Add Template:="Workbook" 
'Get this file's name 
'NewFileName = ActiveWorkbook.Name 
Sheets.Add().Name = "Results"


Hi all - I hope you can help!

I am currently coding in Excel 2007 VBA. (Please forgive the likely very lax program coding you will come across in this post - I have had no formal VBA training!)

A brief description of the problem:

I recently created an intricate Excel 2007 based model for a large fuel cell company. The model outputs large output files which take time to analyze and open.

The client has asked that I create a concatenation program to take a number of output files (up to 8 - although if I can crack the problom for 2 files the extension to 8 should be trivial) and extract basic static graphs and tables for output to a small summary file which opens more quickly.

i.e. 8 large output files becomes one small summary file containing 8 sets of simple tables and graphs (without all the complex analysis that goes on in the output files).

I have enclosed a file named "summary file" which does the job that is required for 2 output files, but it requires me to continuously open and close files. Basically, I do not know a way of switching between open work books using a macro and my way of doing things will be painfully slow when I try to access the larger output files.

If any of you could offer a simple solution I would be extremely grateful!!!

Thanks,

Sam

Hi guys,

Is it possible to switch between IE and the Workbook that I am working on. If yes then can I please get the code?

Cheers
plasma33

This question might also belong to the XP-groups, anyway:

I'm running Excel 2000 (SP3) under XP Pro (SP2).

As Excel was newly installed I had no problems maneuvering between different
excel workbooks/sheets (run under the same Excel session) using Alt-Tab.
Somewhere along the road this feature was lost, i am back to clicking the
Window menu to accomplish the same. I have no idea what-so-ever what kind of
settings have been altered.

Creating a local account on the same computer, running Excel from there made
me aware that i once again could use Alt-Tab to switch between the
workbooks. However, changing to another account when running Excel is not an
option... :-)

Thanks in advance!
Kjetil

How do I switch between windows in the active workbook inside a Macro.

I have a Macro that reformats how ever I need it to do that in Window 2 of
the active workbook. I cannot have it file specific. It needs to take the
active filename and drop it into the formula.

Here is what I have so far. This works but I cannot figure out how to get
the active filename.:

Windows("FS Side Vent.xls:2").Activate
Sheets("Print").Select
Range("OrderNo").Select
Cells.Select
Selection.EntireRow.Hidden = False
For I = 1 To 800
Range("A" & I).Select
If ActiveCell.Value = "0" Then
Selection.EntireRow.Hidden = True
Else
End If

Next I

Range("OrderNo").Select

Hello,

So I know how to copy values (without formulas) between workbooks but I have a different problem.

I'm trying to copy values from one workbook (the values are resulted from formulas) to another. When I try to copy the data over it copies the formula and not the values.

This results in an error occuring as its not the formula's that i need copied over.

How can I copy/paste the values over?

Thanks!

How do I copy and paste between workbooks in VBA? I am trying to write a macro to copy a selection of cells from one workbook to another but I keep getting error messages. How do I write this so that I can run this macro on different files?

I am currently writing a presentation which involves me switching between PowerPoint slides and an Excel spreadsheet designed to demonstrate. I know how to launch Excel from PowerPoint (by simply attaching a command line to the particular icon), but would like to be able to launch PowerPoint from Excel. Not simply the basic application, but to return to the point in the slideshow where I last left. If I'm lazy, I'm sure I can do this with ALT-TAB but I wonder if there was a neater way to do this? I'm pretty comfortable with VBA, and I know there is a line to launch applications generally, though I'm looking to return to a particular point in a slide show (the slideshow application won't be closed, because I'll simply click on an Excel icon to launch the particular Excel file).

Hope someone can help.
Peter

Hi All,

I am having a simple problem that I can't figure out. I am working on multiple excel files which I like to alt + tab between a lot. Makes my life much easier.

However, now every time I open multiple excel files only one excel box appears in my start menu toolbar and I can't alt-tab between workbooks. I can switch workbooks by going to the window tab but this is inconvinient for me. Does any know how to swtich it so I can continue to alt-tab between my workbooks?

Thanks!

-Sia

After installing PowerPivot I now get a ding sound when I switch between workbooks - even when Power Pivot not activated. I checked and the "Provide feedback with sound" in the options menu is NOT checked. Any help on this would be appreciated!

Why isn't my copy and paste working between workbooks? We use excel 2000. I've resorted to using a lot of selection because I can't seem to figure out what is going on otherwise...please help
I'm sure this should be much simpler...

Code:
 
Sub PortData()
Dim wbA As Workbook, wbB As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet, ws5 As Worksheet, ws6 As Worksheet
Dim sPath As String, sName As String, bTextSwitch As Boolean
Dim nLast As Long
Dim i As Long
'********************************************************************************************************************************************************
'********************************************************************************************************************************************************
'This routine copies the names and phone #'s over to the
'master employee list for each branch that is unknown based on phone #
'Lots of help Zack Barresse, aka firefytr @ Mr.Excel.com
'********************************************************************************************************************************************************
'********************************************************************************************************************************************************
'************************
'Set Paths and filenames
'************************
sPath = "H:Reports"    'SET
sName = "Tester.xls"    'SET
StrFilePath = ActiveWorkbook.Path 'This is for using the save incremental
'*********************************************
'If file/directory cannot be found, error out
'*********************************************
If Dir(sPath & sName, vbNormal) = "" Then
    MsgBox "The path and/or name specified does not exist!", vbCritical, "ERROR!"
    Exit Sub
End If
'****************************
'Do NOT allow visual updates in excel
'****************************
Call ToggleEvents(False)
'****************************************************************************
'Since we are going to be shifting between workbooks/worksheets
'Let's set the workbooks and sheets here to prevent unwanted data corruption
'****************************************************************************
Form_Progress
Task_Progress (1)
Set wbA = ActiveWorkbook                   'SET, should be QUERY report
Set ws1 = wbA.Sheets("TEST_Export")     'SET
Set ws2 = wbA.Sheets("X Visit")            'SET
Task_Progress (10)
If WbOpen(sName) Then
    Set wbB = Workbooks(sName)
Else
    Set wbB = Workbooks.Open(sPath & sName)
End If
Task_Progress (30)
Set ws3 = wbB.Sheets("X Visit") 'SET
Set ws4 = wbB.Sheets("pivot")   'SET
Set ws5 = wbB.Sheets("TEST_Export")
 
With wbB
On Error Resume Next
    ws5.Delete
err.Clear
End With
Task_Progress (35)
'Copy Sheets Over
    ws1.Copy After:=ws4
 
Task_Progress (75)
    wbB.Activate
    wbB.Sheets("X Visit").Select
    Range("A2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Delete
 
THIS PART ISN'T WORKING!?!?!
    wbA.Activate
    wbA.Sheets("X Visit").Select
    Range("A2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Copy
 
 
wbB.Sheets("X Visit").Range("A2").Paste
 
Task_Progress (90)
wbA.Close
Task_Progress (100)
'SaveFileVersion
Call ToggleEvents(True)
Unload frm_progress
Message_Done
 
End Sub


Is there a short cut (i.e., CTRL V H to change headers) for switching between
the different sheets in a workbook??

I would like to find out what is the shortcut for switching between
worksheets in the same workbook

Does anyone know if there is a shortcut key or combination of keys for you to
switch between sheets or tabs in an excel worksheet?