Switch Between Active Workbooks?

Jun 20, 2012

I am working with Excel for Mac. This has presented a number of challenges but I have successfully handled them. I am having a problem though and would love some input. I have a workbook that contains a pivot table. The pivot table is based on "Sheet1" of my workbook. The user will hit a button on the pivot table spreadsheet and the first operation is to select a file from their computer. I am using a function called - MacScript to open the finder window and select the file. The file name (with full path) is stored in a variable called filepath. The file consists of a single sheet - Sheet1 and I select all and copy the cells. I then go back to the original workbook, select my worksheet with the source data for the pivot and I again select all and paste the new information over the old data. I have a named range called pivotsource that references the 9 columns that are required for the pivot table. What I would like to do now is go back to the original workbook and close it, do not save anything, just close it like nothing happened. I have tried to close it by using:

/* Workbooks(filepath).Activate
ActiveWindow.Close
*/

Since I had a large amount of data that is in the clipboard, I will also have to deal with the message coming up asking what I want to do with the clipboard. My bigger problem is that the activation of the filepath workbook doesn't work. How can I get back to the original workbook? I thought about the index of the workbooks but I don't know if the users will have other workbooks open at the same time. If they did, I could not reference it.

View 1 Replies


ADVERTISEMENT

VBA Code To Switch Between 2 Workbooks

Apr 18, 2003

I have written VBA code to open a separate file and then copy back and forth between the two files. However, when I recorded, I got commands like the following:

Windows("DestinationFile.XLS").Activate
then the copy/paste stuff then
Windows("SourceFile.XLS").Activate

The problem is that my "SourceFile" may have a different filename. I need the code to be robust enough to handle the possibility of a different filename. I tried the following, but it did not work:

Dim MarketProfileName As String
MarketProfileName = ThisWorkbook.FullName
Windows("DestinationFile.XLS").Activate
then the copy/paste stuff then
Windows(MarketProfileName).Activate

View 6 Replies View Related

Switch Statement Is Too Long For VBA. To Make The Switch Statement Work Over Two Lines

Jan 6, 2009

I have created a very long switch statement, which is too long to be placed in one row in VBA. I have attempted to put a space and underscore at the end of one line and continue the statement on the row below by placing a comma at the start of the second line. VBA will accept my efforts, but when I run the statement in the immediate window, the following error appears.

"Invalid procedure call or argument"

I understand that there are certain rules where I can split a switch statement onto two lines, yet I do not know what they may be.

View 9 Replies View Related

Active Multiple Open Workbooks

Jul 18, 2007

I want to use a for command, but not include all the i's between two numbers. The reason is I want it to activate open workbooks that have similar names, like '110000 2007.xls', '120000 2007.xls', '130000 2007.xls', '150000 2007.xls' and so on.

I want my code to look something like this:

For i = 11, 12, 13, 15
Windows(10 * i & "000 2007.xls").Activate
'do stuff
Next i

Is there a way I can do this? Or use another command?

View 5 Replies View Related

Switch Tab Pages???

Mar 27, 2009

I have a user form that has several tab pages on it when the form opens it checks to see if the user has entered there default info if it is empty it needs to go to the default tab so the user can enter there info. How do I do this using vba in excel 2000 I have not been able to find any code that will do this if I use the set focus option for a text box on the tab page it comes up with an error saying the item is not visible! The user forme is names = UserDataInputForm. The tab pages I have are

1st = InputDailyTimesPage
2nd = OverTimePage
3rd = DataPage
4th = DefultDataPage
the form opens using the 1st tab page by default
in the user forms inisilisation code I have this check

View 2 Replies View Related

Cannot Just Switch The Columns Around

Mar 17, 2006

I have created a database of names & addresses from a software program called "CLIP" into excel, which in turn will become a label mail merge in MS word consisting of 900-1000 customers (for a mass mailing).

The problem is, the names were created last, first and I would like to change them to first last. Is there a way to do that without manually typing them all in again?

The names are in one column, so I cannot just switch the columns around.

View 11 Replies View Related

How To Switch To Next Column In VBA

Feb 3, 2014

I try to code some vba here's my example

1 A B C D
2 3 5 1 2
3 1 2 0 1
4 1 3 0 1
5 1 0 0 0

I want to sum in line 2 all values below but i dont know how to switch to next collum in vba

View 6 Replies View Related

Switch Name Every 20 Turns

Mar 17, 2008

have been using the simple formula below to determine between a friend and myself whos turn it is next.

=IF(Q22

View 9 Replies View Related

Getting A FALSE When Switch Between Two Examples?

Apr 11, 2014

The setup here is I have multiple codes for a product. Coding structure is as follows (options are contained in parantheses):

N -(T,P) - (5L,6,7,8) - (DFL,DJL,DBL,UEDF) - P - (1,6,9,16,19,1H,1H9) - (g,s,blank) - (a,blank)

Clearly, there are a ton of options. What I'm doing is screening those codes and breaking them down into their component parts.

ex) NT6UEDFP1a: N - T - 6 - UEDF - P - 1 - a
ex) NT6DFLP16: N - T - 6 - DFL - P - 16

I'm trying to do multiple if/then scenarios for each option. However, the way I have it now, I'm getting a FALSE when I switch between these two examples (the dfl works, the uedfp generates false). (there are other codes which is why the first line is there. I'm just having trouble with this particular "nt/np" group)

=IF(OR(LEFT($A$14,2)="np",LEFT($A$14,2)="nt"),

IF(OR(MID($A$14,4,3)="dfl",MID($A$14,4,3)="dbl",MID($A$14,4,3)="djl"),[code].....

View 2 Replies View Related

Number Format Switch

Aug 15, 2009

I have several phone numbers that I copy and paste from the web:
I.E:

(888) 456-7890
(333) 234-5678

I need to switch all of them to just standard numbers: They should look like this:

8884567890
3332345678

I go to "format" - "cells" - "numbers" but nothing works. I wonder if any formula can do it. It should work for ALL THE LIST, I want something else than change them one by one by hand.

View 2 Replies View Related

A Button To Switch To A Worksheet

Jul 13, 2008

I have made a little program for a customer at work which has 4 different worksheets.

Each worksheet works out a cost based on different prices.

I would like to make a 'Welcome' worksheet, with buttons on it so my customer can click the button which corresponds to the option he would like to cost, and then excel automatically selects that worksheet.

how to create buttons but don't know the code to but behind it. It would be nice to also hide the other 3 worksheets when they have not been selected by the welcome page.

View 9 Replies View Related

Switch Statement With A Default Value

May 5, 2009

Is there a way to use a switch statement with a default value, for when none of the expressions evaluate to TRUE?

View 5 Replies View Related

Switch Between New Instance Windows?

Nov 15, 2011

Im having trouble switching between windows that are open in separate instances of excel.

I used to be able to switch easily between these workbooks when they were both being being run in the same instance of excel.

But since having to change it so the workbooks are opened in seperate instances, i cannot get it to work, the code just fails when it trys to activate the first window.

I currently have the below code:

Code:

Sub CloseAndSave()
Application.EnableEvents = True
'//Activate InfoHub workbook and put a tick in the checkbox.
Workbooks("403 InfoHub.xls").Activate
Sheets("HOME PAGE").ToggleButton1.Value = True

[Code]..

Is there anyway to do this?

View 1 Replies View Related

Accumulate Target Then Switch

Jan 6, 2012

I am trying to set up a budget spreadsheet for a friend. The sheet will have the usual incoming and outgoings with a surplus at the end of each month. This surplus needs to be accumulated each month in a particular Account (Cell).

Once the amount in this cell reaches £5000 any surplus accumulated each month from that point needs to be deposited in another account (Cell).

View 2 Replies View Related

Hotkey To Switch Between Sheets?

May 22, 2013

I'm trying to work out what the Hotkey is to switch between sheets in excel.For example there is Alt+Tab for switching between windows, Ctrl + Tab for switching between open internet tabs, surely there must be a way to switch between sheets in Excel too, right?

View 3 Replies View Related

Macro: Switch Between The Two Worksheets

Jul 12, 2007

If I have an excel workbook open and then, select the side-by-side option to open another workbook, and I want to write a macro that will switch between the two - how can I do this? The problem is, I don't want it to switch by file names (one workbook constantly changes names), I just want it to switch between active workbook and inactive workbook.

Once I have the code to switch between active workbook and inactive workbook, how can I have my macro look at a cell and see if data is present and if so, drop down to the cell below.

Windows("Job Number Request form.xls").Activate
Range("C9").Select
Selection.Copy
Windows("Backup Copy of 2007 Capex Job Log.xls").Activate
Range("E570").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

View 9 Replies View Related

Switch Off Conditions In And Statement

Jan 15, 2010

I have multiple anded conditions both Boolean and numerical. For testing purposes, I wanted to be able to switch off individual numerical conditions in the If statement. It seemed like an easy matter that I could simply comment out the And statement of choice and recompile. When I tried this, I received a compile error.

Another option would be to use individual togglebuttons to selectively turn off the 5 numerical comparisons. I have no idea how to set that up.

how to do this with either commenting or togglebuttons?

The If statement
If OptionButton4.Value = True _
And ToggleButton1.Value = True _
And Range("G3").Value >= Range("N6").Value _
And Range("AV22").Value >= Range("AU22").Value _
And Range("E3").Value = "Long" _
And Range("G3").Value Range("BG7").Value _
And Range("H4").Value >= Range("L4").Value _
And TradeFound = False And _
DeletedFound = False Then

View 9 Replies View Related

Switch Cell Values

Apr 11, 2008

im trying to write a macro that will switch two selected values for example if a select a1 and run then a1 & a2 values would switch places.

so far ive tryed and got nothing

also is there like a wait for user input or mouse click function.

so when i run a macro there is a point where it would wait for me to say, select another cell before continuing?

View 6 Replies View Related

Find Method To Search For The Active And Non Active Values

Jul 14, 2009

I have a range of amounts in Sheet 1 from F7:Q13 and im using the find method to search for the active and non active values in the cell. Which means that if there's a value in the cell it will transfer the value in Sheet 2, if nothing is found in the cell the cells in Sheet 2 will return as nothing or null.

I think the problem lies on the FindWhat variable. Im getting a compiled error which im not sure what is it.

I've attached the spreadsheet so you get a better idea of the problem that i encountered.

View 13 Replies View Related

Macro With Reference To Active Chart On Active Sheet

May 14, 2014

I currently have the following macro running to set a chart's data values:

Sub C3Quarter12013()
'
' C3Quarter32013 Macro
'
'
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Values = _

[Code] ......

When I copy the tab and change some of the data within the cells, I want the macro refer to the chart on the current tab and the values in the current tab - as currently it refers to only "Chart 2" and the values in the tab 'Figure 2 - WE OPH'.

I've tried changing the sheet name to ActiveSheet.name but that doesn't seem to work.

View 4 Replies View Related

Shortcut Key To Switch Between Worksheets Within Workbook?

May 22, 2014

Any shortcut key that allows you to switch between worksheets within the same workbook?

Found the shortcut - CTRL and Page Up / Page Down

View 1 Replies View Related

Switch Back To Previous Workbook

Nov 29, 2007

I have a macro that unprotects a worksheet copies data from that worksheet and pastes it into a newly created and saved workbook. However I have two problems

1) I'm attempting to copy the data from the first workbook by selecting all cells and then when switching to the new workbook, select all cells and the paste special (values) then paste special (formats). This works when recording the macro but when I attempt to run it it bombs out.

Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

2) How do I get the macro to switch back to the workbook it got its data from. The name of the source workbook varies and there may be from 2 to 15 workbooks open. Is there a previous active workbook command? or do I need to create some kkind of temporary value to hold the file name of the source workbook.

View 9 Replies View Related

Getting Excel To Switch Back To A Workbook?

Apr 24, 2009

Hi I want to be able to use VBA to open a 2nd sheet when the spreadsheet first starts ,with

View 8 Replies View Related

Can't Switch Tab Back In Multipage Userform

Nov 17, 2011

I have two pages in the userform. What I was trying to do is that certain textbox (txtDate) needs to be entered on the page1 before going to page2. The user can still select page2 however I want the vba to bring the page back to page1 if the data is missing.

Here is the code:

Private Sub MultiPage1_Change()
If MultiPage1.Value = 1 And txtDate = "" Then
MultiPage1.Value = 0
txtDate .BackColor = &H40C0
End If

It didn't work completely - the tab selection on top was on page1 however all the controls shown are still the controls under page2. I have to manulally click page1.

I also tried MultiPage1.Page(0).Enabled=True but it didn't work either.

View 3 Replies View Related

Toggle Switch For Turning Protection On Or Off

Feb 6, 2012

How can I do the following:

I need a toggle switch that switches between protecting and unprotecting the worksheet.

It should display the text 'Entry Mode' when the sheet is protected and 'Filter Mode' when the sheet is unprotected.

View 3 Replies View Related

Filtering Switch Off In Protected Cells?

Sep 7, 2013

I have a macro that turns off filtering option when it runs. What I want is for to first warn the user that rows have been filtered and the Macro will not run, then once the filters are switch off, the macro will run. It does the last bit at the moment but switches off the filtering of protected cells and once the macro has run the sheet can no longer be filtered .

Sub NewRowSQT()
Dim ans As String
ans = MsgBox("Ensure you have selected a cell in column B immediately below the section you wish to add to or the Macro will FAIL!", vbYesNo)

[Code]....

View 1 Replies View Related

Chart - Allows To Switch Data Range

Mar 16, 2014

I have data in columns A to Z. Column A has the name of Bonds and columns B to Z have different returns for those funds. I would like to create a scatter plot chart that allows me to switch which column to pull the data from in addition to column A which will always have the name of the Bonds.

For example in addition to the name in column A I would like the chart to plot data from Columns C and F on the x and y axis respectively.

I'm thinking of copying column A to a new tab and then having a drop down box in columns B and C to copy over values from the 2 columns I select. How can I get a drop-down box to copy over values from another column?

View 2 Replies View Related

Switch 2 Datasets By User Selection

Feb 19, 2007

i have list of users with name, company, division, subdivision, date ...

the list can contain more than 10000 users and i want to be able to switch 2 users using a listbox/filter/ validation list.

there can be multiple users with the same name even company so a simple validation list which is limited to 1 column will not suffice. the interface for the switch should be fool proof so anyone can switch 2 users.

View 6 Replies View Related

Switch Based On Cell In Range

Mar 11, 2007

I would like to find a way to chose a string and number based on cell location

ex.

_____A_________ B _______C
1___Mike,1____Mike,2____Mike,3
2___John,1____John, 2___John, 3
3___Steve, 1__Steve, 2__Steve, 3

I Want to run this in a subroutine, but I am not entirely clear on the abilities of datachecking in ranges.

View 9 Replies View Related

Switch Between Variable & Fixed Spreadsheets

Jul 20, 2007

Trying to copy certain cells from a spreadsheet (this varies) to a fixed spreadsheet.

I copy 3 values plus a range of cells from the variable spreadsheet to a fixed one.

I have done one big macro that runs 5 smaller macros (using code from this brilliant forum).

as follows: ....

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved