Activating A Workbook That Was Previously Active
Dec 21, 2009
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 2 Replies
ADVERTISEMENT
Apr 24, 2008
I want to copy from a Workbook into an Excel Worksheet and close the Workbook.
HTML Workbooks.Open (stgPosition)
'Range("A1:I150").Select
'Selection.Copy
'Windows("Symbol Assembler.xls").Activate
'Sheets("Sheet1").Select
'Range("O1").Select
'ActiveSheet.Paste
Workbooks (stgPosition).Activate
Application.CutCopyMode = False
ActiveWindow.Close
When the Debug gets to "Workbooks (stgPosition).Activate", I get an error message:
Routine error "9" Subscript out of range.
View 9 Replies
View Related
Sep 27, 2006
I have multiple workbooks open, and would like to activate a workbook whose name has a certain string in it. How would I do this with VBA?
View 3 Replies
View Related
Jul 17, 2009
This has been kicked around for some time and so far there isn't an answer.
I want to approach it a little different this time.
My users can have let's say 5 separate instances of Excel open.
The user clicks on a macro to open a workbook and I have programmed a message that says "This File Is Alredy Open".
The code I used is: ....
View 6 Replies
View Related
Apr 3, 2014
I am busy with code that filters and copies from a "source file" using dialogue to choose criteria than adds a workbook and pastes filtered information. I than go back to "source file" and do second filter. My question is this newly added workbook changed from book1 to book2 etc everytime the user will run the macro. How do I go about activating the workbook for second and third paste without it being saved?
Here is name code:
[B]Workbooks.Open ("C:Documents and Settingsabek276DesktopSource File.xlsx")
Options = InputBox(Prompt:="Scheme Code", Title:="Options")
Options1 = InputBox(Prompt:="Scheme Code", Title:="Options")
Options2 = InputBox(Prompt:="Scheme Code", Title:="Options")
[Code].....
View 7 Replies
View Related
May 15, 2009
I have my VBA Codes set in a workbook (Production) that my supervisors open to calculate production. So when they open the workbook (Production) I have a button that they push that starts the code however I need to put something in that activates the other open workbook, The problem is that the name of the workbook that they run the code on can change.
View 2 Replies
View Related
Aug 1, 2014
For some reason I'm consistently getting an error in the following line:
Code:
Public wkbk1 As Workbook
Public shtInput As Worksheet
Public i As Integer, iPass as Integer
[Code]....
View 6 Replies
View Related
Feb 27, 2014
I've been using the following code to bring in individual cell values from one closed workbook to an active one. I would like to modify this is possible to bring in multiple cells at once and also pull them into a different worksheet in the active workbook. Basically, my command button is on Sheet1 but I'd like the data to pull into a cell on Sheet2.
Private Sub CommandButton1_Click()
With Range("Q9")
.Formula = "='C:Users[Workbook Name.xlsm]Worksheet Name'! N27"
.Value = .Value
End With
View 6 Replies
View Related
Jun 30, 2014
I run a model in Excel that automatically saves my file every xx iterations. After saving the file I want to make a backup of the file. Tried
[Code] .....
but get a permission denied error message.
I don't want to use .SaveAs as it is a huge file that takes a while to save and SaveAs has a tendency to break links that should not be broken..
View 13 Replies
View Related
Feb 6, 2013
I have some vba that opens a closed workbook, copies data from a named range and then pastes it to the active workbook.
However, what is happening is that the closed workbook is opened and only part of the data is pasted. What I would prefer to happen is this:
Open the closed workbook-->copy the named range-->paste(append) to next empty cell in column B.
Heres the code that I have got.
Sub Workbook_test()Dim wb As Workbook
Application.ScreenUpdating = False ' turn off the screen updating
Set wb = Workbooks.Open("G:WAREHOUSEPlanningSmartNew Training Plan raining plan.xls", True, True)
[Code]....
View 5 Replies
View Related
Jun 20, 2008
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 8 Replies
View Related
Mar 6, 2014
My requirement is as follows......
I want to get a copy of worksheet from specific workbook to active work book in which i want a copy of sheet get moved.
View 2 Replies
View Related
May 9, 2014
I am trying to transfer a value from the active worksheet in one workbook to another workbook. I keep getting a subscript out of range error on the line that uses activeworksheet command when I run my code.
View 3 Replies
View Related
Mar 6, 2014
I have a Picture in a workbook kept open. ( workbook 1 )And i have some X workbook open....i want a copy of sheet from workbook 1 To X workbook which is currently active.
View 1 Replies
View Related
Nov 20, 2006
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 9 Replies
View Related
Apr 22, 2009
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 9 Replies
View Related
Mar 23, 2014
Here is what I think I want to do (however there may be a better way to get to the end result).
Macro 1:
Unhide all worksheets however, first list only the worksheets that are visible.
Macro 2:
I want to be able to hide all worksheets except those in the above list.
My intent would be to have these in the personal file so I could run either process on any file I have open so I think I would need to insert a tab for the list when unhiding & remove the tab after hiding.
I have found many strings unhiding all & hiding all except a named sheet... but can't find anything on the above?
View 2 Replies
View Related
Mar 24, 2007
I have a set of four sheets that each act as a wall planner type of calendar (one for each team of staff) all in one workbook. I have a sheet that acts as a key where cells on the key display different types of holidays e.g. Annual Leave, Unpaid Leave etc. Normally the user would select the cells where they wish to take the time off against their name and go to the "key" sheet, copy relevant cell and go back to their team sheet and paste. I want to put all this into a macro to be used on a customised button on the menu bar.
I am able to do this with one sheet ( I simply put in the code the name of the sheet that I previously selected) but I don't know how with four sheets. How do I say go back to the sheet I previously selected.
View 9 Replies
View Related
May 30, 2008
In cells A1, A2, and A3 of Sheet1 I have data. This data is the result of some calculation else where in Sheet1. Nevertheless, I would like to link this data to Sheet2 in cells A1, A2, and A3. Now normally, you can't do this. But is their a way around this error message?
View 10 Replies
View Related
Apr 10, 2007
i want my userform to operate like this:
>each time the user opens the userform, he has to choose from the 1st combo box which AREA will he worked on (e.g., Math, English, History). upon selecting, the AREA chosen will populate the choices in the 2nd combo box (e.g. if Math, 2nd combo box will show: Algebra, Calculus, Trigonometry).
**thru some posts, this part is already solved. thanks. but this will lead me to my questions.
>1st and 2nd combobox selections (e.g., MATH, CALCULUS) serve as a match. Unique from the other matches.
>upon choosing the match, the user can encode in the succeeding textboxes (he would encode numbers).
**this is in another Userform Which Saves Data To Two Different Worksheets DATA TO TWO DIFFERENT WORKSHEETS
>my userform has an EDIT command button.
***what VBA codes can i use so that when I click EDIT, the userform will display all the matches that I encoded. If I choose the match from such display, all that i encoded which are related to that match will be reverted to the userform so that i can edit it right at the userform?
View 9 Replies
View Related
Jul 25, 2014
I wanted to hide/remove the already selected options in a dependent drop down list so that the next time i am left with only the remaining options.
View 7 Replies
View Related
Nov 5, 2009
I have generated a matrix in excel through iteration (I'm trying to calculate a dinamic covariance matrix between 50 values) which looks like this:.......
A 50x50 matrix. What I have generated in each cell is not the formula, but the text of the formula. Somehow Excel has a valid formula in a specific cell, but "doesn't know yet" that within the cell there is no longer a text. So, to make every formula run, I have to go cell by cell pressing F2, then enter, 2.500 times. Notice that in each formula I don't have something like this:
+"+COVAR(Rends!C4:AB4;Rends!C4:AB4)" or
'+COVAR(Rends!C4:AB4;Rends!C4:AB4)
but the valid formula: +COVAR(Rends!C4:AB4;Rends!C4:AB4)
View 2 Replies
View Related
Feb 15, 2007
I am working on an application where I have several workbooks open at the same time. I need to know which one is active. How do I do that?
View 3 Replies
View Related
Feb 21, 2008
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 9 Replies
View Related
May 31, 2006
I have a sheet with about 20000 rows consisting of hundreds of names which
occur randomly.After each name is a date of the type 29-Jan-06.The dates are in ascending order.I wish to print in a third column how long it is in days since that name appeared previously in the list,if it never appeared before this will be zero.
View 5 Replies
View Related
Apr 21, 2009
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 5 Replies
View Related
Jan 20, 2010
I've manage to do this a while ago, unffortunaly my hard drive got damaged.
View 3 Replies
View Related
Apr 3, 2004
How do i calculate only the active workbook in VBA?
simple question, but i'm really slow today.
View 5 Replies
View Related
Oct 10, 2004
if it is possible to delete the current workbook that is open?
I'm thinking of using an add-in to delete the workbook i'm viewing
View 9 Replies
View Related
Feb 11, 2007
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 3 Replies
View Related
