Basically the main workbook opens 2 files at a time performs a calculate in the main workbook and then copies and pastes information in 3 ranges. Then closes the two open workbooks and loops and performs same operations until it hits the maximum loops. My macro is as follows and I have 3 question in capital letters.
View 3 RepliesI have several workbooks in a make your day folder categorized under several sub folders. Each workbook has a macro that calls the following macro:
View 2 Replies View RelatedI have one main workbook that, when opened, opens three other workbooks each in its own instance of Excel. I'd like to know how to close all three of these upon closing this main workbook.
View 2 Replies View RelatedHow can I close the currentmonthend AND previousmonthend workbooks in the following code, but leave workbook "MoEnd Compare" open? I'd like to do some additional comparisons with the "MoEnd Compare" file. I tried the code below to close those two workbooks, but it is not working.
Sub check_month_end()
Dim currentmonthend
Dim previousmonthend
Dim project_current
Dim ptcurrent
Dim yearcurrent
Dim project_previous
Dim ptprevious
Dim yearprevious
Dim r As Integer
Dim c As Integer
Dim l As Integer
MsgBox "This will check project type and year on current and last period month end report."
currentmonthend = Application. GetOpenFilename("Excel Files (*.xls),*.xls", , "Select CURRENT Month End Report?")
previousmonthend = Application.GetOpenFilename("Excel Files (*.xls),*.xls", , "Select PREVIOUS Month End Report?").......................
I made an Excel based program that uses multiple workbooks with two main workbooks (“Master List” and “Products”) that all the others pull information from. I run into a problem when a user clicks the close X in the upper right hand corner and the entire application closes. I found some code that will let me close all the workbooks but one (the code is put in the “Products” workbook in the BeforeClose event).
For Each wb In Workbooks
If Not wb Is ThisWorkbook Then
wb.Close SaveChanges:=True
End If
Next wb
Is there a way to modify the code so it will close all the workbooks but the two main ones if someone clicks the close X button in the upper right hand corner?
Is there anyway to change the following code so that it will save and close all workbooks except the one that has this code?
View 3 Replies View RelatedI tried to use Workbooks("Inactivebookname.xlsm").Close False
and many variations thereof, but I simply cannot close this workbook when currently another workbook is active.
I have no problem in closing an active workbook with
ActiveWorkbook.Close
I'm working on developing a stock option trading system in Excel using a live data feed and VBA. It tends to crash after running flawlessly for a half hour to 2 hours. It seems to crash when loading a support workbook, so I was hoping you could take a peek at my code and see if there's a problem with the way I'm dynamically opening/closing workbooks.
In order to cope with limitations in the amount of live data that I'm accessing (through DDE with Reuters Station), I've split up the data I need into about 600 workbooks, which open when they're needed or close when they're not (through VBA). At any given time, about 35-40 of these workbooks are open.
The main workbook that uses all this live data and support workbooks is set to have the application recalculate every 5 seconds (automatic recalc crashes the program almost immediately). After that refresh, it calls other subroutines, including opening/closing these support workbooks:
I have completed a long macro, and at the end of the macro I want to close a few workbooks automatically without saving. These workbooks have been assigned variable names.
Is there a command line I can use, that will not prompt the user to save?
Variables:
MARM_fileNM
MARC_fileNM
MAKT_fileNM
Temp_fileNM
We're using XP Pro using Excel 07
The background is there are about 40 people who use a Excel based program that contains a pivot and a bunch of other tools that they use on a regular basis, this set of tools has a version number. In this excel workbook, it has a function that looks on the network drive that we have and checks the local version vs the version on our network drive, if its wrong, then the user gets a popup stating your tools are out of date, would you like to update, then they click yes and I have these lines of code
Public Function GetNewTools()
Dim MyFullName As String
'Turn off alerts
Application.DisplayAlerts = False
'Open the new version of tools
Workbooks.Open Filename:= _
"Network DriveUpdate.xlsm"
End Function
The update file has this code that executes on fileopen in the thisworkbook section by calling the following sub
The main issue we're running into is near the very end, the code never makes it to "TEST 2". After the first workbooks close, the code just stops running. No crashes, errors, freezes, anything. It just stops running and never makes it to the second msg box.
I have an array of data type Variant, who's elements are workbooks opened by a user.
The array size is static, which for now isn't a concern but I can't work out how to close the workbooks in the array via a loop and the usual vba code of Workbooks("file").Close
Code I have that doesn't work is:
Sub Close_Workbooks_In_An_Array ()
Dim dFile (1 to 6) As Variant
Dim i As Integer, j As Integer
' // Some code to open files, set each dFile(i) as a file and then process
' // them. Max value for i is 6
j = 1
For j = 1 To i
MsgBox ("Closing: " & vbNewLine & vbNewLine & dFile(j))
Workbook.(dFile(j)).Close
Next j
End Sub
I have a workbook which includes a simple set of options on closing such as selecting the front sheet, restoring scrollbars and saving the workbook. To avoid problems with subscripts out of range I am using the ThisWorkbook statement to close the workbook.
This works fine and causes the workbook to close when close is clicked on any excel window. The problem is that excel falls over when it tries to resume closing the other workbooks. I am given (ironically) an error saying "excel has encountered a problem and needs to close". Does anyone know how to work around this?
My code is below:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call Toolbars9(True)
With ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayWorkbookTabs = True
End With
I have 30 workbooks closed and i want update a cell (f.e. b4) in all the workbooks.
Need code to open the files, update the data in that cell and close the workbook.
I'm trying to create a VBA macro which will open external workbooks in a specific folder. thes workbooks will have filenames which are identical to the value in cell B1 of my active workbook. Once open, I would use indirect to pull certain results from that external workbook and then close it. I have a separate macro already which runs through the list. I found a few examples online but they don't seem to work.
View 2 Replies View RelatedI have 77 excel workbooks that are created each week, they are all in the same directory. I have to open each one and copy the data into a single workbook. The file names change slightly each week.
example: DIST_91124_GROWTH_PRODUCT XXX _07072006.xls The 07072006 is the week ending date and will change. Each of the 77 files has a different DIST_number.
Is there a way to automate this process, it takes me about 4 hours to do it manually.
I am using the close.workbook command in VBA to close a workbook I opened to copy data from. I am getting a message that says "You have a large amount of data on the clipboard. Do you want it available?" What command do I use in VBA say no to this message?
View 5 Replies View RelatedTrying to solve a problem. I have two excel files one called test.xls and the other is test1.xls. I want to transfer the contents of a worksheet in test.xls called data to test1.xls and onto another worksheet called data1 (within test1.xls). I do not want to overwrite the test1.xls file but simply move
test.xls!data to test1.xls!data1, keeping any existing formulas etc on other worksheets within test1.xls intact. Anyone konw some vba code as a macro that could do this for me? Version of Excel is Mac 2004
I have a macro that opens all workbooks from one directory and runs a macro for each workbook to clean up the data. I cannot figure out how to take all those open workbooks and save them to another directory and close the workbook. Also, I do not want the macro workbook (xlsm) to save. I only want it to close. I am working in 2007 Excel.
View 8 Replies View RelatedI've set up a spreadsheet structure at work which is three workbooks linked together.
I created this initially within My documents saved locally to my machine. Now though, I need to move the folder containing these workbooks onto the server to be accessed by others. My problem is that when cutting and pasting the folder, the formulas within the three spreadsheets still refer to their original location within My Documents rather than following the folder to their new location.
I have to report on 6 departments each with 6 sub departments weekly. First I need to consolidate 6 ( move/copy worksheet to consolidated workbook using a macro) separate worksheets (all saved in the same folder) per department into single workbook (preferably generated automatically by macro/code). I then need to repeat the above process for another 6 files per sub department. The output files should be pasted as "values" while retaining all other formatting. In essence I need to automate the whole right click on tab, select move or copy worksheet function in excel to take the load out of doing it manually.
View 2 Replies View RelatedI am looking for a way to move workbooks to a new instance of excel by the click of a button. For example if I have multiple workbooks open under one instance of excel I do not want to have to minimize this and that... I have multiple monitors and I would like to hit a button on a selected workbook and have it auto open it to a new instance of excel. I image this may not be apart of excel and that it might require a macro to do so.
View 1 Replies View RelatedI have 25 files with certain worksheets that I need to move to 25 other files.
Worksheet 1, 2, 3 and 4 in Workbook A needs to be moved to Workbook A-2014 Worksheet 1, 2, 3 and 4 in Workbook B needs to be moved to Workbook B-2014 Worksheet 1, 2, 3 and 4 in Workbook C needs to be moved to Workbook C-2014 etc....
Is there a way to do this with a macro? Preferably I would like to do this automaticly - i.e. runing the macro from a master file that
1. Opens Workbook A copies the worksheets
2. Open Workbook A-2014 paste the sheets
3. Save and close Workbook A-2014
4. Close workbook A without saving
then doing the same for Workbook B, etc.
I've got a workbook for every department which is used to make a weekly status. Each of these workbooks contains a sheet with the 'weekly statuses over time', i.e. a table showing the weekly status for the department for week 1, week 2, week 3 etc. I've attached the file to make it easier to understand what I'm talking about, see Weekly_Test. Currently I can only see the performance of each department but I would like to be able to see the total performance for all departments, i.e. have a 'Total workbook' where all the weekly statuses are summed up weekly.
In the Weekly_Test file you can see the code which is used to generate the 'weekly status' sheets (also inserted at the end of this post). All workbooks for the departments are the same, apart from the file name which is the name of the department and this does not change. The weekly status is generated in the following way:
In the sheet 'Indtastning' (data entry) you enter the data and the date. Then click 'Update'. The sheet is then copied to a new sheet with the date and all data is copied to the table in the 'his.status' sheet. Now, I would like to add some code so that when I click 'Update' the data which is copied to the table in the 'his.status' sheet is also copied to a similar table in the 'Total' workbook.
Dim indtastArk
Dim arkDato
Public Sub Opdater()
arkDato = Cells(1, 1)
OpretDatoArk
' nulStilIndtastning
overførTilStatus
End Sub
Private Sub OpretDatoArk()
Sheets("Indtastning").Activate
With ActiveSheet
.Cells.Select
Selection.Copy
End With
ActiveWorkbook.Sheets.Add Before:=Sheets(4)......................
I am trying to do a Vlookup between 2 workbooks. I've tried a couple different ways and none seem to be working.
From the inventory workbook I need the Stator and Rotor SN's, O.D. and I.D. only for the ones that have the Status "SHOP" to come up on the Comparison Workbook.
Attached is the two workbooks.
It is quite standard to create links between workbooks, and generally I do this by inserting an "=" sign in the cell I want data to appear in for Workbook 1, I then open workbook 2 where the desired data is and click on the cell housing the data I want.
I just went to do this, and upon putting the = sign in the cell I want in workbook 1 and clicking in workbook 2 nothing happens except my cursor is now in workbook 2 and the = sign just remains alone in the workbook 1 cell.
If, however, I put an = sign in a cell in workbook on and then click another cell in that sheet or a cell in another worksheet but in the same workbook a proper link is created, so the problem seems to be isolated to links to other workbooks.
If you are working in an excel spreadsheet that is linked to other excel files, when you double click on the cell that is linked to another workbook, it immediately opens the linked workbook if your settings are correct. How do you change your settings to enable this functionality?
View 2 Replies View RelatedLOOKUP for 2 different workbooks. I have been using the following codes to do the task.
View 2 Replies View RelatedI want to link two workbooks together with the aim that when a option is selected in a drop down menu in workbook "band form", it auto populates the rest of the form with data from the second workbook "showsales2013". date, ticket price, support acts etc etc.......
as a example,the drop down in workbook "band form" is a list of bands (list data also taken from showsales2013) ive got this bit working ok (i think ), each band is playing on a different day so i have used this formula to get the "date" from "showsales2013"
=VLOOKUP(F4,[ShowSales2013.xls]Sheet1!$1:$65536,4,0)
this seems to work until i insert a new row and/or column (in the showsales wookbook - which needs doing from time to time as new bands get booked to play), then i get #REF in the date cell on the "band form blank".
i used pretty much the same formula to pull the other needed data (price/support acts etc ) from showsale2013, and the result is the same, #REF.
I have a spreadsheet that I need to open two other workbooks to copy and paste data from.
The name of the two workbooks changes each day, and the location because they are filed in subfolders.
I have the following code, which works to open the first sheet but then comes up with the following error when trying to open the second sheet. "Run-time error '1004: Excel cannot access 'Hub PVA'. The document may be read only or encrypted".
The folder is not encrypted because when I was only opening the second document with the same code, it worked.
The code I am using is:
'DECLARATION
On Error GoTo ERR1:
ChDir "G:GENERALTRANSPORTDAILYPLANS"
Workbooks.Open Filename:="G:GENERALTRANSPORTDAILYPLANS"
[Code]....
I have Workbook A and Workbook B(csv file)
In workbook one I want to look for a unique code that is in both workbooks and if it is then return a result of the total.
As an example
WORKBOOK A
a0YC000000YWPE1MAP
WORKBOOK B
Code qty
a0YC000000YWPE1MAP 10
a0YC000000YWPE1MAP 1
a0YC000000YWPE1MAP 14
Result I need is 25.
What to type if it is a different workbook and not just a sheet.