Switching Between Opened Workbooks

May 9, 2007

I have 3 workbooks open, the main WB has my command button + code in it, and the other 2 were opened up using the existing code in my main workbook.

Using general code e.g( Thisworkbook, activeworkbook, workbooks(1)), how can I activate one out of the 3?

I know the main workbook can be accessed using "thisworkbook", but when I activate it, I can't activate one of the others in my next line of code using activeworkbook.activate, or workbooks(1).activate.

View 9 Replies


Switching Between 2 Workbooks

Jun 12, 2007

From a workbook, I need to open a further workbook called 'transfer' and then switch back to the original workbook. Presumably I need to save the current workbook name to a variable, open transfer with Workbooks.Open Filename:="c:debworkTRANSFER.xls"
then switch back to the original

How do i pass the original workbook name to a variable, and then reactivate it. I have tried numerous options but just cannot get it to work. Do i reactivate with workbook. activate or windows.activate

View 9 Replies View Related

Switching Between Workbooks

Oct 7, 2006

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?

View 5 Replies View Related

Switching Between Workbooks Without Having To Use The Activate Command

Jun 22, 2009

Is there a way to switch back and forth between workbooks without having to use the Activate command? I have everything planned out but this aspect is still a source of confusion. The Project: I need to have qty purchased and price in a table from Worksheet B entered into the matching customer index location in Worksheet A. Worksheet B contains customer number, sku, qty purchased and price. Worksheet A contains customer numbers in row 3 and sku's in column A. Minor data manipulation will need to be done on Worksheet B before before accessing it which I plan to do after opening it in this macro.

View 4 Replies View Related

Selecting A Range When Switching Between Workbooks

Feb 1, 2007

I select workbook 1, cell A4, then I select workbook 2, cell A4, but when I'm in workbook 2, it does not select cell A4. Am I doing something wrong here? See my code below.

Sub CopyData()
Dim i As Variant

Workbooks("1_2007 Forecast (Ancillary).xls").Worksheets(1).Activate

Workbooks("1_2007 Forecast (LGBU).xls").Worksheets(1).Activate

End Sub

View 4 Replies View Related

Switching Two Workbooks And Transferring Data With Variables

Jan 4, 2007

I have create a nice invoice with macros to random insert pages and summations etc. Works fine, but to complete it I want to go one step further and there ìt got out of hand.
I want to create the following, but I can not get al this combined in two Macros.

Basis: Workbook 1 = Invoice, Workbook 2 = Invoicelist
Required actions Macro1:
1. Starting point is that Workbook “Invoice” is open.
2. The user activates Macro1.
3. Check if Workbook “Invoicelist” is active.
4A. If YES then continue,
4B. If NOT then reply with some sort of Alertbox “Invoicelist not active! Click OK to open (needs to open Workbook “Invoicelist” or Cancel to stop (needs to stop Macro).
5. Workbook 2 “Invoicelist” is now open.
6. Find the last invoicenumber in the list. Add “1” and put the new number to the list (to prevent using double numbers). Then I want to take this number to the “Invoice’ workbook, but that can be done by copy and paste.

Now I get really stuck because I want to use variables instead of multiple copy paste actions.

Required actions Macro2:
7. We are in Workbook “Invoice”, the user activates Macro2.
8. Same check if Workbook “Invoicelist” is active as in Macro1.
Several data from “Invoice” has te be transfered to “Invoicelist”. And to be safe, this does not have to be the last cell if the user “forgot” to store date for a previous invoice!
9. Store data from sevaral cells in variables, including invoicenumber!
10. Find invoicenumber in “Invoicelist”.
11. Paste data from variables into fields on same row as correct invoicenumber.
12. Save Workbook “Invoicelist”.
13. Go back to Workbook “Invoice”.

The two problems I cannot fix is checking if Workbook is Active and store and use cell values with variables.

View 9 Replies View Related

Switching Between Workbooks Whose Names Are Stored As Variables

Jul 20, 2006

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.activate variable
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!

View 8 Replies View Related

Show The Name Of Opened Workbooks In The List Box

Jun 3, 2009

I want to show the name of opened workbooks in the list box, but the following code does not work.

for example, I opened 'a1.xls' and 'a2.xls'. The listbox of 'main.xls' should show their file name.

View 2 Replies View Related

List Sheet Names From Opened Workbooks

Dec 2, 2006

This code loops through all columns in all sheets in all workbooks of a specified directory.

My copy paste to column C in "Loop Folder" works well thanks to this forum especially wigi

I want to paste the file name once in Column A and have it repeat for each new file opened. My code only does this for the first one as I simply can not fathom the coding

In column B I'd like to paste the sheet name once as they get opened.
I am struggling here as well on Sheets.Name coding ....

View 9 Replies View Related

Code To Run When Workbook Is Opened And Copy Values From Other Workbooks In It?

Feb 19, 2014

I want the code to run when we open excel workbook "TEST" and it should open up all the workbooks one by one in the folder J:ABC and copy cells C2 and C4 values in the A and B columns of TEST workbook.

e.g There are 5 workbooks in the folder J:ABC so when the TEST workbook is opened then the code should run and open 1st workbook and copy values in cells C2 and C4 to it and close the workbook.

The code should run as below:

1st workbook:

C2 value will go in Test workbook B1
C4 value will go in Test workbook A1

Close 1st workbook

2nd workbook:

C2 value will go in Test workbook B2
C4 value will go in Test workbook A2

close 2nd workbook.

3rd workbook:

C2 value will go in Test workbook B3
C4 value will go in Test workbook A3

close 3rd workbook.

and so on It will be going to next rows in A and B columns.

View 2 Replies View Related

Switching Last Name/first Name

Oct 31, 2009

I have a column of cells that have the last name first then the first name. Example:

Smith John

Is there a formula/macro that can switch them all?

View 9 Replies View Related

Switching Languages

Nov 25, 2006

Working for a dutch company and using a lot of excel and access it is sometimes a pain that when you are looking for functions. for example "left(b3;3)" in dutch is "links(b3;3)" now this is an easy one but some are hard to guess.

I wrote some neat code which condionally formats cell's in an Excel range and dependend on a value in column "Q" (= status) then sets the color of the field. it works fine

However when working with an english version of Excel I need to use "left" instead of "links"

how to check the local settings for Excel?

'Conditional Format status cell's
.Range("G4:P" & (xRcount + 3)).Interior.ColorIndex = 4
iRow = 4
iRow2 = 4

View 9 Replies View Related


Apr 22, 2007

I am trying to figure out commissions with 3 different commission structures depending on type of product. So on C2 I have a drop down box with the 3 types of products. So what I would like to do is when C2 shows say "New" I want it to calculate using one formula. When "used" is selected, I want to use a different formula. And when "scratch_dent" is used, I want it to use a 3rd formula.

View 9 Replies View Related

Prevent Tab Switching

Feb 11, 2008

I made up a small subprocedure that searches through a large list of zipcodes (These zipcodes are in a separate sheet). Whenever I execute my subprocedure in another sheet, it jumps to the zipcode sheet and then goes back to where I originally executed the subprocedure.

Is there any way that I can prevent the sheets from switching back and forth?

View 9 Replies View Related

US/UK Date Switching

Sep 30, 2009

I want the people filling in the form to put the date into a box. I did have a calendar box but this wouldn't work on a lot of PCs , so I've switched it for three combo boxes, Day-Month-Year.

I've got the 'Add' button to put it all together - DayBox.Value & "/" & MonthBox.Value etc

This seemed to work fine and excel processes this as a date. It automatically switches to the US date format (MM/DD/YYYY) which would be fine as users don't see it and I know it's at least consistent.

But it's not.

1st August 2009 = 08/01/2009 (ie MM/DD/YYYY)
13th August 2009 = 13/08/2009 (ie DD/MM/YYYY)

And while I can format cell to show me the month name in the first example, the 13/08/2009 refuses to change format even though it's recognised as a date.

View 9 Replies View Related

Switching Info Around In The Same Cell?

May 21, 2014

I have names in cells in this format.

Smith John

I would like to switch that around so the name in the cell looks like this

John Smith

Is there an easy way to do this?

View 6 Replies View Related

Switching Between Multiple Spreadsheets

Oct 23, 2007

I'm having a problem with how excel opens my files. Up until yesterday when I would open any of my spreadsheets they would open seperately. I would have multiple files open across my taskbar and could easily switch between files with a simple Alt-tab windows function.

Now when I open multiple excel files it opens them under "one" file so to speak. The only way to switch between files is if I go to Window>then select the file I want or minimize within excel so I can see the other spreadsheets.

View 2 Replies View Related

Stop Switching To Different Worksheet

Oct 4, 2009

I need help with stopping the screen to swiching to different worksheet. Everytime I execute this macro, the screen is switching to different worksheet.

View 8 Replies View Related

Stop Sheets Switching

Nov 13, 2009

Stop sheets switching. I have a simple autofill macro

View 4 Replies View Related

VBA Switching Window Focus?

Mar 18, 2014

I have a macro that launches another program (Windows Title - Storm). I then use sendkeys to have it export the file to an excel file. (This launches another instance of excel.) I want to be able to focus back to (Storm) so I can sendkeys to close the program down. I can get it to the end result using sendkeys, but I am trying to have it switch back to the Storm application so the user does not need to close it manually.

View 2 Replies View Related

Switching Between Sheets - Shortcut

Oct 7, 2002

I often find myself moving from one sheet in a workbook to another over and over. I wanted a shortcut that moved between the last two sheets selected.

Similar to how Alt+Tab works with windows.

Does anybody know a keyboard shortcut or if not is there a macro that could be added to perosnal.xls that would mean i would aways b able to switch between two sheets quickly.

View 9 Replies View Related

Switching Languages: Formulas Vs. VBA

Jan 21, 2009

I am currently creating a massive bilingual report using Excel based on data gathered from a customer visit, as most of the engineers are non-native English speakers, and most everywhere else in the world wants reports in English. Currently I am using this roundabout method:

I have a lookup Table sheet: .....

View 10 Replies View Related

Switching Cells Per Date Entered?

May 19, 2014

how to switch cells per date entered?

View 3 Replies View Related

Track Changes Without Switching To A Shared Workbook

May 21, 2009

Which version of excel allows you to do a Track Changes without switching to a shared workbook? I am awared the Excel 2002 version allows Track Changes on the conditioned that the workbook is switch to shared mode.

View 3 Replies View Related

Keyboard Shortcut For Switching Through Worksheets

Feb 5, 2010

I need keyboard shortcut for switching through worksheets.

For now, I have this VBA macro and shortcut Ctrl+R:

View 7 Replies View Related

VBA - Copy And Pasting Without Switching Tabs

Nov 30, 2011

I am creating a code that loops through several list and Copys and paste the value in a given cell to another cell. However I would like it to do so without switching to the other tab. Additionally I also don't want to freeze the worksheet because I have a display that shows progress through a conditional formatting thing. I need it to stay on the "Rotation" tab.

Sub Roladex()
Dim RegionPaste As Range
Dim row As Integer, row1 As Integer, row2 As Integer


View 3 Replies View Related

Switching Page Numbers During XML Import Using VBA

Aug 4, 2012

I've been programming my VBA script to import data from an XML file which looks like this:

HTML Code:
<response responseStatus="success"> <result xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:type="apiPagination"> <currentPage>1</currentPage> <list xsi:type="apiReport"> <Variable1>Name1</Variable1> <Variable2>41</Variable2> </list> <list xsi:type="apiReport"> <Variable1>Name2</Variable1> <Variable2>20</Variable2> </list>

So far everything works great and it imports everything correctly to Excel 2010. This is the code I'm using:

Sub test()
Dim WS As Worksheet: Set WS = ActiveSheet
Dim req As New XMLHTTP
Dim Link As String
Dim PageNumber As Integer
Dim Url As String
PageNumber = 1


The problem is that I only import page1 (only link [URL])

How do I get all URLs so that it also imports the data located on other pagenumbers? For example [URL]

View 1 Replies View Related

Macro To Enable Switching Sheets

May 2, 2013

I am looking for a code that would enable switching of sheets. For example, on Sheet1 (Master Sheet) I have got Industry1 to Industry3 and the companies within the Industries are on Sheet2 to Sheet4. When someone clicks Industry1 on Sheet1, it should navigate to Sheet2 (where it gives a list of companies within the industry), and when Industry2 on Sheet1 is clicked, it should navigate to Sheet3 and so forth..

View 3 Replies View Related

Switching Words Around In A String Of Characters?

Oct 10, 2013

I have a sheet of about 10k rows that I need to switch some things around on. The string is below:


It needs to say:


How to fix this with a macro or formula. The Make Model and Year on each string will be different. Basically I need the format to be Make|Model|Year.

View 9 Replies View Related

Keeping The Same Cell When Switching Worksheets

Mar 30, 2007

I would like my cursor to stay in the same cell when I switch worksheets. If I'm in B40 on worksheet one, and switch to worksheet two I would like the cursor to be in B40.

I'm using very basic excel functions with no scripting at the moment.

View 9 Replies View Related

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