Select Another Active Workbook
I've manage to do this a while ago, unffortunaly my hard drive got damaged.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Open Workbook, Find Sheet That Contains Cell Value From Active Workbook
I'm trying to figure out a way to find a specific sheet in a workbook that does not contain the macro. Within the macro I have a cell which holds the name of the specific sheet I would like to find but I can't get it to work for some reason... 'Dim officen As Integer 'Dim thiswb As Workbook officen = Range("A2").Value Set thiswb = ActiveWorkbook ' Open the Active Info file Workbooks.Open "C:My DcoumentsActive 20080616.xls", , , , "xxxxxx" ' Dim sourcewb As Workbook Set sourcewb = Workbooks.Open"Active 20080616.xls" Sheets("officen").Select RowCount = ActiveSheet.UsedRange.Rows.Count Range("B2").Select.............................
View Replies!
View Related
Select Previous Active Window
I have multiple excel workbooks open at the same time. I need to run a macro on one of them every 15 min but I need to return to the active window when the macro is done. How do I read what window is active then return to it when the macro is done? Sub AutoSave() dTime = Now + TimeValue("00:15:00") Application .OnTime dTime, "AutoSave" Windows("data.xlsm").Activate Sheets("Data").Copy Application.DisplayAlerts = False ActiveWorkbook. SaveAs Filename:="c:excel" & Format(Time, "hhmmss"), FileFormat:=xlCSV ActiveWorkbook.Close Application.DisplayAlerts = True Windows(1).ActivatePrevious End Sub
View Replies!
View Related
Select A Range Based On Active Cell Location?
I'm trying to select a range of cells whereby the range is dependent on the currently active cell. I know you can use the "Activesheet.Range("A1:D2").select" method to select a range where the cells are always the same, but I'm after a dynamic selection where the values can be programmatically altered depending on some other result. For example, let's say that I make a certain cell active (based on the result of some other formula), and I want to select the range of cells in the adjacent column that is X rows deep. Putting this into context, imagine the resultant active cell is B2, I then want to select the range C2:C10, but if the active cell is E10, the range selected would be F10:F18 (if active cell is X, then range would be Y:Z). The Offset function would allow me to position the cell based on the current active one, but it doesn't let me select a range. The Range function only lets you choose either hard coded or index cells, e.g. "Range(cells(y,z), cells(y,z)).select", but this is still no good because I'd need to know the index value of the active cell (can this be done?).
View Replies!
View Related
Copy Worksheets From Active Workbook To A New Workbook
I am trying to code a Macro so that i can take all the worsheets and save them as individual Workbooks. I wrote a macro that appeared to work, but, after it saves the first sheet as a workbook, i get a debug error. MS VB Script error: Runtime error '9': Subscript out of range Any advise would be greatly appreciated. Thank you Code is below.. Sub saveall() ' ' For Each ws In ActiveWorkbook.Worksheets ThisFN = "C:Documents and SettingsUserDesktop" & ws.Name & ".xls" I = I + 1 Sheets(I).Select Sheets(I).Move ActiveWorkbook.SaveAs Filename:= _ ThisFN, FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False Next ws End Sub
View Replies!
View Related
Making Workbook Opened From Other Workbook Active
I have a workbook that is a formatted report that I need to insert data from a name variable "CSV" file. I have created a macro in this workbook that calls an "Open" dialog for CSV files that I can use to browse to and open the source file. The macro then is supposed to "select all" copy and close the workbook then make the original workbook active and paste the data starting at row 2. The macro "seems" to be working perfectly except when I do the open the macro then makes the original workbook active, selects all copies then wants to close the original workbook. How can I tell the macro that the csv file that I just opened should be the active workbook, baring in mind that the name won't be known before the time it's opened so I can't hardcode the "active. workbook (NAME) "route.
View Replies!
View Related
Select/Copy/Paste From Workbook To Workbook
I've been doing various searches for past few hours and can't seem to find out if one its even possible, the closet I've found on here is to copy row to another workbook. My question is I have one excel book called level1.xls and another excel book level2.xls, now in level1.xls I have a row of fields filled in and in level2.xls I have a blank form which for example I would like to try programme in VBA from the level2.xls file to click a button and open automatically level1.xls then grab field "A2" value from level1.xls and place it in level2.xls field "L4" then continue using level1.xls "B2" to level2.xls in field "C3" and then continue down when I add more values A3, A4 so on in level1.xls to the same field in level2.xls L4. Steve.
View Replies!
View Related
Name Of Active Workbook From Add-in
I have an add-in (MyAddin.xla) that is called from my open_workbook event in my workbook (MyBook.xls): Private Sub workbook_open() AddIns("MyAddin").Installed = False AddIns("MyAddin").Installed = True ThisWorkbook.Activate Run "MyAddIn.xla!WbookOpen" End Sub I put this in my WbookOpen procedure: Sub WbookOpen(pWorkbook As Workbook) MsgBox "Wbook Open Active workbook is " & ActiveWorkbook. Name End Sub The message displayed is: Wbook Open Active workbook is MyAddin.xla I thought this would be: Wbook Open Active workbook is MyBook.xls.
View Replies!
View Related
Macro Will Not Look At Active Workbook
I have 2 workbooks open in the same excel session. each with its own unique name. The macro looks for a specific name and then copies data from this workbook to the 2nd workbook. For some reason it no longer like the name of the workbook #1 which has not change (as far as I can tell) When I run the macro i get the run time error '9' subscript out of range, when i click on the debug this is what is highlighted and underlined.
View Replies!
View Related
Save As And Close Active Workbook
I am trying to create a form button that will save as and close the active workbook. I have the Save As code working but I can't find any code to close the workbook that will work. A couple of issues: I don't have control of what the workbook is called - the user will name it - so I can't do a simple Workbooks (" ").Close. Also, I don't want to quit the application. I don't know if the user will have other worksheets open. I also tried ActiveWorkbook.Close and that didn't seem to work either. Not sure what I am doing wrong.
View Replies!
View Related
Copy Active Worksheets Into One New Workbook
I have 16 proposals that are either hidden or visible in a proposal generator. I need to be able to copy only the visible worksheets over to a new workbook that uses a name from a cell inside the proposal generator. Then a save as prompt to force the user to save the file wherever they choose.
View Replies!
View Related
Find Active Sheet In A Workbook
I am creating a macro that will open another workbook and take all the contents off a page in it and paste the contents to itself. The problem is that sometimes those workbooks will only have one page and it will have the data I need, but sometimes there will be two or three pages in the workbook, but again, with only one sheet that has data on it. How can I have the macro find the one page with data on it?
View Replies!
View Related
Active Workbook Refreshall Security
I have a function that is called in Access vba code that refreshes all sheets in a given workbook. We use it all the time for workbooks containing any number of tabs to be refreshed. The code has a key line: ActiveWroksheet.Refreshall. I need to now start to secure the pivot so the user can not refresh it. Another post pointed me towards: .PivotCache.EnableRefresh = False. This apparently can only be used for a particular sheet; ie it can not be applied to ALL sheets in the workbook. The trouble is the function is generically used to refresh any workbook, and the number of sheets may vary. Any thoughts on how I can disable the ability to refresh a pivot for ALL sheets. I'd prefer not to turn off the pivot wizard since the user should be allowed to manipulate it...just not refresh it.
View Replies!
View Related
Activating A Workbook That Was Previously Active
An Excel file is e-mailed to my company for all the credit card transactions in a given day. Because we manage many stores and each store is a different legal entity and a different fiscal year ending, I need my macro to sort the information and separate the transaction by store and by fiscal year end. I just hit my first block. I need to open a workbook off the server to grab information for a vlookup formula. I can get the workbook open, but I can't get back to the original workbook or find a way to reactivate it. Because the macro is going to be saved in the "personal" workbook, I can't use the "thisworkbook.activate" code. Also the workbook name and tab name that is emailed to me will always be different so I can't use other solutions that I've seen posted.
View Replies!
View Related
Removes All Code From The Active Workbook..
I have this script below that removes all code from the active workbook. The reason I am removing all the code is because I am running code from my personal library and the code changes the worksheet I am working with.So while stepping through I noticed it moves over to the sheet change sub routine from the active workbook that I am working on. So I tried to remove all the code from the Active workbook but after I remove all the code it returns a expected end of sub error, where the old worksheet change sub was? but no code is in the entire workbook? So I tried to enter something in the blank sheet mod and it returns a (Cant Edit Module) error.
View Replies!
View Related
Save Active Sheet To Another Workbook
Copy the active sheet that is open in a work book and save it to another file with the date in the name? For example Report_11-03-08.xls The only problem I see with this. How could I also make it append a letter to report if the file already exits Example saved as Report1_11-03-08.xls. Then just continue incrementing. Sub CopySave() Application.ScreenUpdating = False ActiveSheet.Copy Application.DisplayAlerts = False ActiveSheet.SaveAs Filename:=ThisWorkbook.Path & "/" & "Report_" & Format(Date, "mm_dd_yy") & ".xls" ActiveWorkbook.Close Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
View Replies!
View Related
Find Last Row In Non-active Workbook
I have a number of files which I am opening, manipulating and copying the result to a master workbook I am attempting to use code like this Selection.EntireRegion.Copy Destination:= Workbooks(Master). Range("A" & LastRow) Is it posible to define the value of LastRow without making Master the active workbook?
View Replies!
View Related
Close Active Workbook And Open Another
Two workbooks named"test1.xls"test2.xls I have a userform in "text1.xls" workbook when I click on commandButton it validates Staff name and Staff number stored in sheet1 of "test1.xls" workbook and opens test2.xls file which is in different folder. Problem is how do I code to close or unload "test1.xls" after opening test2.xls Private Sub goButton_Click() F$ = "D: estFiles est2.xls" ' can I use Path so I don't need to type the full directory path Workbooks.Open Filename:=F$ End Sub
View Replies!
View Related
Addin Workbook Remains Active
I have created a workbook as an addin. After the addin has been installed I can't make the original workbook active again either through VBA or the Window -> Select Workbook Name function. The code is below. Private Sub Workbook_AddinInstall() Dim Wbook As String Wbook = ActiveWorkbook.Name Call AddToolbars Workbooks(Wbook).Activate End Sub Sub AddToolbars() Dim customBar As CommandBar Dim myControl As CommandBarButton Set customBar = CommandBars.Add("Concur Reports") Set myControl = customBar.Controls _ .Add(Type:=msoControlButton) With myControl .FaceId = 2 .OnAction = "Main" End With customBar.Visible = True End Sub
View Replies!
View Related
Set Macro For All Worksheets (active Workbook)
Sub display_all_false() With Application .DisplayGridlines = False .DisplayHeadings = False .DisplayOutline = False .DisplayZeros = False .DisplayVerticalScrollBar = False .DisplayWorkbookTabs = False .DisplayFormulaBar = False .DisplayStatusBar = False .ShowWindowsInTaskbar = False .CommandBars("Standard").Visible = False .CommandBars("Formatting").Visible = False .CommandBars("Control Toolbox").Visible = False .CommandBars("Drawing").Visible = False End With End Sub But, when I run the code, seems there're run-time errors detected at these codes: .DisplayGridlines = False .DisplayHeadings = False .DisplayOutline = False .DisplayZeros = False .DisplayVerticalScrollBar = False .DisplayWorkbookTabs = False 1. Is there any way to correct these codes? 2. Does the bolded codes applied to ActiveWindow (Worksheet) only (eg. With ActiveWindow ...)? Can I change it to ActiveWorkbook (perhaps)? 3. Can the command menus (File Edit View Insert Format ...) be hidden also? 4. Does looping method for each worksheet
View Replies!
View Related
Linking Graph Data To Active Workbook
In the code below I am defining ranges in a sheet and then using an offset and a scrollbar linked to the offsets. Then I make a graph using the data so that I can use the scroll bars to move the linegraphy left or right to compare the data sets. My problem comes when linking the values to the graph. I know I can do this by referring directly to the xls file, but as I have loads of different files I want to apply this to I would rather use an active workbook reference...however this does not seem to work. The current code referring to the sheets works but then I have to go into the graphs and change the source data to get them to operate off the scroll bars.
View Replies!
View Related
VBA Script Stops When The Workbook Is Not Active.
I work in Excel with a VBA script. The purpose of my script is that every 5 seconds a procedure will be executed. This works well. If my workbook is not active, it does not work. Or when I work with another workbook. Or when I work with another program. Then the script will stop to execute. It looks like a break. I have tried several scripts that have the same effect. I want the script continues normally with execution.
View Replies!
View Related
Workbook Save, Make Sure Active Cell Is A1
When saving a file that has 5 sheets that extend to the right of the users screen, sometimes the left part of the report is left somewhat hidden because the last person to save it was using the part to the far right. Is there a way to move all the active cell to A1 and make sure rows A:D or so are showing?
View Replies!
View Related
Extracting A Value From Another (non-active) Workbook Using A Path Dynamically Formed
I can’t get Excel 2002 to resolve an external reference to a value in another workbook. If I have the following literal expression in a cell in my active worksheet, it works: ='C:Documents and SettingsMy DocumentsHCSSAC123.xls'!NumSAC In this case, the contents of the variable NumSAC is copied into the active workbook from another workbook named SAC123. This works fine without the SAC123.xls workbook being active. But what I really need to do is have Excel reference an expression that I form by concatenating together strings and a cell value (from the active worksheet). The following expression evaluates to be exactly the literal expression above (which worked), but it doesn’t act as a valid reference. =”'C:Documents and SettingsMy DocumentsHCS" & $B12 & ".xls'!NumSAC" The result displayed in that cell is 'C:Documents and SettingsMy DocumentsHCSSAC123.xls'!NumSAC and not what I want. If I try to use the INDIRECT function with the above character string, like this, =INDIRECT(”'C:Documents and SettingsMy DocumentsHCS" & $B12 & ".xls'!NumSAC") then I get #REF! However, if I open the SAC123 workbook (so that it is in the ring of active workbooks), then I get the correct value put into the cell. The INDIRECT function only works if the referenced workbook is active. Is there a way of having Excel evaluate a character string as a valid reference without having to have the referenced workbook(s) active? In my case, I want to have a master active workbook that references over 200 other workbooks.
View Replies!
View Related
Copy Ranges From Closed Files To Active Workbook
I have a number of files in a directory, with data in columns A:E, and variable rows deep (200-300) that I’d like to copy to the active workbook. I’d like to have a file window open to the same set directory (ie. “CArchives” and be able to select any file in that directory to copy. Note: There are 2 separate ranges to copy, which must be done separately because the headings are to be moved 2 columns over - also, there is data in beteen the headings and main data in the active workbook. Range 1: Two headings cells in A1:B1 get transposed to C1:D1. Range 2: the files’ data starts at A4 but gets inserted starting at cell A6 of the active workbook.
View Replies!
View Related
Capture Active Workbook Events Via .xla Add-in
I wrote a small .xla add-in for Excel, which puts a command bar on top, and provides several buttons that do some tasks on the workbook that are frequently needed in our office. At some point, I needed to catch the event of a newly activated sheet, because my add-in will do something upon this event (namely, change the state of one of the buttons on its own command bar) After some reading and searching, I managed to accomplish the task, however not completely. I first want to copy my code. The code has some tags in it, in order to make it easy to follow the flow and find out where the error lies. Here is the class module in the xla, which handles the events of the application: ' Class name is EventClassModule Public WithEvents App As Application Private Sub Class_Initialize() MsgBox "Event Class initialized" ' Just to follow End Sub Private Sub App_WorkbookOpen(ByVal Wb As Workbook) MsgBox "WB Opened: " & Wb.Name ' Just to follow End Sub.......................
View Replies!
View Related
Ensure Active Workbook When File Opened By Windows Scheduler
This code opens a UserForm in the Workbook_Open event when the file is opened. After 10 seconds, the message is supposed to go away if the user doesn't click anything, then macros that follow are supposed to run. It is intended to allow a user to stop the macros. This works when you open the file manually (macro security must be low), you'll see a msgbox pop up after the UserForm goes away. However, when the workbook is opened as a Windows Scheduled Task (Windows/ Control Panel/ Scheduled Tasks), the UserForm does not come to the front of the screen unless a user actually clicks on the window. It just flashes at the bottom indefinitely.
View Replies!
View Related
Save Active Sheet As New Workbook, Formats & Values Only
I would like to link a macro to a button on the active sheet, that saves the active sheet In a New Workbook, with Format And Values Only. Preferably saved in the same location as the origonal workbook. This is my main goal. If possible I would like the new workbook to be named from a chosen cell (E19) on the active sheet with date added.
View Replies!
View Related
Macro: Create Directory Based On Active Workbook And Save To It
Read “My Documents” Path And Use Result Problem: Note: Typical user OS will be Windows XP Pro / Win 2K Excel version : 97 / 2002 / 2003 1. Corporate network security settings will only allow directory/subdirectory creation in the “My Documents” section of customers individual computers. 2. Per customer request, VBA application needs to save extracted files for future use. 3. I can specify an initial “My Documents” subdirectory be made and the VBA application file be loaded/copied into that location – i.e. – “My DocumentsCat”. 4. When VBA application is opened from that specified directory, (first time), the application needs to make an additional subdirectory tree to save future files. I can read the opened from location via VBA with the following: Dim filepath As String filepath = ThisWorkbook.Path As an example – this code would produce a string definition of “filepath” – such as the following:............................
View Replies!
View Related
Copy Data From Open File To Current Active Workbook
I want to create a standard macro that will copy values from a Master workbook to the current, active workbook I have open. The problem I have run into is when I record a macro it also records the name of the active workbook I'm copying data into and I would like it so when I bring up a workbook I just run the macro and it copies the values in. I'll be doing 8 different macros that each copy different values from the Master spreadsheet
View Replies!
View Related
Copy Of Active Workbook Path Stamped Onto Spreadsheet With Date And Time / Edit Check
1) I need to add an edit check 2) have a copy of Active Workbook Path stamped onto spreadsheet with date and time to create a visual record of where the file has been saved (described after the code below). 1) I need to verify that two cells (S7 and S9) are not blank before running my code below (=IF(OR(S7<>"",S9<>""),RUN CODE,"You must select your Provider or Division before you can save this document")). - If both of these cells are blank a message box should notify the user that they must select the provider and/or division before they can continue with the save. - If one or more of these cells are not blank the code below should run.
View Replies!
View Related
Code To Select All Tabs In A Workbook
I have been recording different sets of commands then copying/pasteing them together to complete what I want. I have tried to clean it up as best I can but im sure it does things it does not need to. Is there a way to tell it to select all sheets in "Book2.xls" then remove the formatting (font color,comments,shading) rather than using the Array and naming each and every sheet? What this will end up doing is openeing about 12 files, copying specific tabs from each of those files and pasteing them all to the same workbook (in this example it would be Book2). Then removing the formatting. Workbooks.Open Filename:="F:123Book2.xls" Windows("Book1.xls").Activate Cells.Select Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy Before:=Workbooks("Book2.xls").Sheets(1) Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select Cells.Select Selection.Interior.ColorIndex = xlNone Selection.Font.ColorIndex = 0 Selection.ClearComments
View Replies!
View Related
How To Have Macro Allow Select Of Workbook And Worksheet
The last few days I have had great support in answering my questions. I would like to tweak the code one more step.. The RFQ LIST workbook grabs data from Inventory workbook after select the workbook. This code in RFQ LIST allows to select the workbook, I would also like to select the workbook.sheet as found out they want to tab the data and keep long term history.
View Replies!
View Related
|
TRACKER
