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.
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
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?
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.
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.
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:
C2 value will go in Test workbook B1 C4 value will go in Test workbook A1
Close 1st workbook
C2 value will go in Test workbook B2 C4 value will go in Test workbook A2
close 2nd 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.
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?
[code] 'Conditional Format status cell's .Range("G4:P" & (xRcount + 3)).Interior.ColorIndex = 4 iRow = 4 iRow2 = 4
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.
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?
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.
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.
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:
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.
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
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..