Open Workbook, Run Macro, Save - Automate
Sep 16, 2006
I have 5 Excel files. The first one is Main, and the other 4 are subfiles, namely A1, A2, A3 & A4. I have a need to open Main, then click a button to start a Macro (in Main) which will do the following, sequentially:
1. open A1
2. run a Macro in A1
3. close and save A1.
4. open A2
5. run a Macro in A2
...
12 close and save A4.
The files A1..A4 are a file server and being shared. If they're being used by another user, it cannot be saved - so I would have to skip it and continue with the next A. It would be great if start Excel, open Main, and run the Macro in Main fully automatically.
View 3 Replies
ADVERTISEMENT
Mar 14, 2014
I need to have a workbook (all of them in a folder, ideally) refresh in the middle of the night. The file is large so the refresh takes a full minute. I know I can't auto refresh a closed workbook (pretty sure anyway), and I can't leave the workbook open for various reasons.
So I'm thinking I could have a macro in an open excel (it kicks off on open) that will open a particular workbook elsewhere (the one I need refreshed), open it, refresh it and close/save it. Ideally it would do this for the entire group of workbooks in the folder but I'd be happy to start with just one. I would like to set it to occur every twelve hours (so at midnight, say, this would occur - I would just have to leave the workbook with the macro in it open on a computer so that it runs and completes this task every night).
View 7 Replies
View Related
Mar 8, 2013
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 Related
Feb 14, 2009
is it possible to work on a workbook lets call it 123
once macro button is pressed it opens workbook abc
then saves it as what ever is in cell a1 to a directory?
View 14 Replies
View Related
Apr 22, 2009
I often make templates of forms for weekly, bi-weekly, monthly, etc., forms for others to use and I tell them to be sure to use Save As... with a new name in order to keep the original form intact. We do not have a common location for templates here. I have tried a Workbook_open() macro to give them a reminder message, but the macro gets saved with the new file, which asks them again to use Save As..., which they shouldn't do.
Is there a way to delete the macro from the new file? Or some other method to acheive the goal?
View 9 Replies
View Related
Dec 4, 2009
I know that the line in my code Worksheets.Copy is NOT what I need to have to make this work, but with it it's the closest I could get to what I want.
I want the user to be able to click the SAVE command button, be allowed to either accept or change the default save options, save the current workbook and then go right back to the form that has the SAVE command button. Sounds simple, I know, but when you don't know the right commands to save the WORKBOOK, not just the WORKSHEETS, it becomes a P.I.A.
I know there's a lot there, but here's my code, which is just a slightly altered version of the code by Ron de Bruin:
Private Sub SaveData_Click()
'If no date has been selected then don't allow SAVE
If PubDateLabel.Caption = "" Then
MsgBox "Publication Date not set. You must select a date before saving.", vbCritical, "No Publication Date!"
Exit Sub
End If
View 9 Replies
View Related
May 8, 2007
I'm looking for information on building a macro that will automate making copies of a spreadsheet into another workbook that contains very particular information. My master sheet has lists of my customers and the rep's that service them. What I need to do is have macro buttons for each rep. that will pull all the shops that they service and all information for these shops into another spreadsheet.
View 9 Replies
View Related
Apr 27, 2012
Code, couldn't make it work with VB HTML Maker. I'm new with VBA Basically my idea is when someone open default file, insert data, try to save file, macros save workbook with inserted data as another file (*.xlsx) and make pdf in another folder. Pdf part is working fine, but with second part just can't find right option.
Pdf part
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Dim Name As String
Dim i As Integer, j As Integer
Name = ThisWorkbook.Path & "PDF Archive" & ActiveSheet.Range("F6") & " Invoice " & ActiveSheet.Range("B11")
[Code]...
Excel part
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ActSheet As Worksheet
Dim ActBook As Workbook
Dim CurrentFile As String
Dim NewFileType As String
Dim NewFile As String
[Code]...
View 1 Replies
View Related
Mar 12, 2014
I've got a kind of "menu" sheet that will pull up various reports from Excel spreadsheets. Rather than having the user work with the live data, the menu opens the file and saves it as a copy, closing the original. This is working fine.
The tweak I want to add is, since the user is looking at a copy rather than the live data, I don't want them to save when closing. Is it possible to programatically add a bit of code to the new copy to handle this?
Here's a sample of one of the menu button's code:
Code:
Private Sub CommandButton18_Click()
Dim wb As Workbook
Set wb = Workbooks.Open(Filename:="J:Z PII OvensRack Repair TrackingRack Repair List.xlsx")
With wb.Worksheets("Sheet1")
.Unprotect Password:="pass"
ActiveWorkbook.Worksheets("Sheet1").Copy
End With
[Code].....
View 7 Replies
View Related
Jun 13, 2008
What i need to do is Open a Closed workbook (which i have done in the code below)
1 . When this book opens there is a msgbox that needs to have "template" pasted in to then ok automatically clicked ( i have no clue if this can even be done)
2. Then go to Sheet("contactor info") find last row then paste certain info. Below is a peice of code i have from the open workbook but of corse it doesnt work. The red is where it hangs up.
openpath = ActiveWorkbook.Path & ""
Set wb = Workbooks.Open(openpath & "Template.xls")
With wb.Sheets("Contractor Info")
. Cells(Sheets("Contractor info").Rows.Count, "B").End(xlUp)(2, 1).Select
ActiveCell.Value = Msg1
ActiveCell.Offset(0, 4).Value = Msg2
ActiveCell.Offset(0, 5).Value = Msg3
ActiveCell.Offset(0, 3).Value = Msg5
ActiveCell.Offset(0, 2).Value = Msg4
ActiveCell.Offset(0, 1).Value = Msg6
ActiveCell.Offset(0, 6).Value = Msg7
Application.Calculation = xlCalculationManual..................
View 2 Replies
View Related
Feb 11, 2013
Using the developer tab I inserted an Active X combo box. Under properties I referenced a 'linked cell' that I want the data to appear in. This box works great as long as I keep the sheet open. Once I save and close the workbook and re-open, that reference is lost. It shows #REF! in that field. Why does it lose the reference? I have tried to reference a cell on the same sheet as well as a cell in a different sheet and it keeps the sheet name but not the individual cell.
View 13 Replies
View Related
Jun 10, 2013
I'm a fan of the Excel 2010 table styles, but can't figure one thing out. When I create a table in excel (Ctrl+T), I like to reformat it with a new defined style. When I save the style, I can use it while I'm in that instance of excel, but when I close and reopen excel, the style is gone. How to save the custom formats to they are always available?
View 3 Replies
View Related
Apr 14, 2009
i have this macro to save as a csv this macro only save the workbook the you are looking at, i will like for this macro to save as a csv all open workbooks preserving the name of the xls file.
Sub SAVE_AS_CSV()
Dim strFilename As String
strFilename = ActiveWorkbook.Name
strFilename = Left(strFilename, InStr(strFilename, ".")) & "csv"
ActiveWorkbook.SaveAs filename:=strFilename, FileFormat:=xlCSV
End Sub
View 2 Replies
View Related
Sep 27, 2006
I did a macro on my mac to transfer a sheet from one workbook to another worbook. It works very well when the destination workbook is open. Therefore I wanted to add some piece of code to check if the destination workbook is open. If not then I wanted the macro to open it before tranfering the sheet. Here is the code I´m using for tranfering the sheet
Sub Transfer_Sluttet()
If ActiveSheet.Index <> Sheets.Count Then
Application.DisplayAlerts = False
Set ws = ActiveSheet
Sheets(ws.Index + 1).Delete
ws.Move Before:=Workbooks("Sluttet.xls").Sheets("sheet2")
'Moves active sheet to beginning of named workbook.
'Replace Test.xls with the full name of the target workbook you want.
Application.DisplayAlerts = True
End If
End Sub
This is the type of macro I useually use on my pc to check if a workbook is open and if not then open it
If IsWorkbookOpened("Filename.xls", "C:Documents and ..................
View 3 Replies
View Related
Jun 10, 2007
When I save an Excel workbook, I would like to automate it to also save as a web page in 3 different locations (and automatically close after save as a web page). I would like to be able to automate the save as a web page in the 3 different locations and close these web pages. But my Excel workbook (which I have saved as an .xls) I will close manually.
One location to save the web page would be on my desktop and the other 2 locations would be on different shared drives. Can this be done with ******* event or something?
View 9 Replies
View Related
May 22, 2006
i've got the code below, and if you notice the directory and file line, I want a loop that'll do what the macro below does, but also for files j1k2-j1k200. I'm hoping there's a macro that'll be able to do this, as I don't want to have to copy, paste and edit the same macro below 200 times!
Application. ScreenUpdating = False ' turn off the screen updating
Set wb = Workbooks.Open("C:Uni StuffStocks_J20j1ka.xls", False, False)
Sheets("Sheet1").Select
Range("B6:S6").Select
Selection.Replace What:="]w1", Replacement:="]w2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
wb.Close True ' close the source workbook saving any changes
Set wb = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
End Sub
View 5 Replies
View Related
Sep 28, 2012
I have two workbooks open. I need to "Save As" and close workbook 2 with a file name from a cell in Workbook 1. The macro is running from workbook 1.
I'm guessing a change in the last line. I don't know what Dim means either.
Dim FName As String
Dim FPath As String
FPath = "G:"
FName = Sheets("sheet 1").Range("A1").Text
ThisWorkbook.SaveAs Filename:=FPath & "" & FName
View 1 Replies
View Related
Dec 7, 2012
Here's my macro:
Code:
Sub CopyRow()'
'Copies row to new sheet, highlights it, marks column 'A' as copied.
'
Dim cCell As Range
Set cCell = Selection.Cells(1, 1)
Selection.Copy
Sheets("Sheet2").Select
Rows("2:2").Select
[Code] .....
Is it possible to modify it to paste into a different workbook called c:filesDestination.xlsm, instead of the existing workbook (Source.xlsm)? The destination sheet name is the same (Sheet2). It's OK if both workbooks are open at the same time.
View 2 Replies
View Related
Apr 8, 2013
Is there a way to run a macro in an open workbook when the workbook becomes activated or deactivated? So if I have workbook A open with a macro in it and workbook B open as well, can I have the macro run on workbook A when that becomes the workbook I see on my screen?
View 2 Replies
View Related
Jan 23, 2012
the following scenario.
I am using the following code to save a workbook as a macro enable workbook on the selected path as today's date. But it is being saved as a macro free workbook.
Below is the code in module:
Sub save_file()
Dim tDate As String
Dim FileSaveName As String
Dim fPath As String
tDate = VBA.Format(DateSerial(Year(Date), Month(Date), Day(Date)), "dd-mm-yyyy")
'FileSaveName = Application.GetSaveAsFilename(InitialFileName:=tDate, filefilter:="Excel Files(*.xlsm),*.xlsm", Title:="Please save the file")
[Code]...
View 4 Replies
View Related
Feb 16, 2010
I have a folder containing 40 single sheet excel workbooks and I would like to automate following tasks:
- Open each excel file (need to open the file so as to update it since it gets the data from another workbook through =formulas)
- Copy paste as values
- Save this as excel html in the same folder as original excel files (keeping the original file name)
- Close (original excel file should not be changed ie formulas should remain in place, only the html file will contain values)
- Since there will always be xHtml files with same name need the macro to replace the excisting file
My abilities with excel are limited to functions, no VBA knowledge other than finding ready codes and pasting them in the module.
Since this routine is to be run almost daily the macro should run all files, instead of one by one.
I just hope that I am not asking too much for excel to handle and I hope that explanation is clear.
View 9 Replies
View Related
Apr 6, 2013
I have created a spreadsheet some time ago and have been asked to improve on it but I'm rusty with VBA.
I have an automated ordering system that saves each sent order as the date e.g "05-04-2013.xls" but the management team want a graph with the data for the last 4 weeks compared. I have created a seperate workbook called "consumables report.xls" which has a column with the products listed followed by columns "Quantity" and "cost" which is repeated for the 4 weeks of the month.
I want to add a button to prompt the user to choose the saved order e.g "05-04-2013.xls" (all orders saved in same directory) to copy and paste the quantity and cost columns (c8,D69) into "consumables report.xls". I got this to work earlier but it would only paste the formulas and not the values. So I need
A prompt to open workbook
Copy range (c8,d69)
Close work sheet
Paste special .value (c8,D69)
I dont care if it has to open the workbook to copy the data as this will only be used once a month so it dosnt matter how slow the code is.
week 1 week 2 week 3 week 4
Product
quantity
cost
quantity
cost
cost
quantity
cost
1
2
3
4
5
6
7
8
View 7 Replies
View Related
Mar 19, 2008
I want to record a macro (so i can asign it to a button) that saves the workbook to a specific folder. I want the workbook to be saved under a name from a cell in the workbook.
the code below saves it to the correct folder but does not save it under the name typed in cell "I5" (it saves it under "false" now)
ActiveWorkbook.SaveAs Filename = "Z:ESTIMATES 2.xls" & Range("I5").Value & ".XLS"
End Sub
View 9 Replies
View Related
Mar 11, 2014
am using Excel 2010 and having issues trying to save a worksheet to a specified file location with the save date....
I have tried several posts form this forum and elsewhere and can't seem to get the macro to do what I want.....
I want to save a 'worksheet' from an open workbook that I use for updating information to the same file path as the workbook with the date the file saved...
View 6 Replies
View Related
May 10, 2008
I would like to add some icons on the left side of excel open file pane to faciliate my work. Because i need to load some files under the same folder many times a day. Does anybody know how to do that? I've seen people has more icons on the pane before. The defaut setting has only 'History', 'My Documents', 'Favorites', 'Desktop' and ' My nutwork places' on it.
View 2 Replies
View Related
Nov 17, 2006
I created folder on my desktop to hold various documents. I have master excel document which contains forms and command buttons that open documents from same folder. Is there a way to change code instead of having full file path to specify current directory. I'm worried that if I move this folder to another location then my open file button will not work since location is changed.
Also when I open with command button word document, I have a button on word document to close. I can close document but word application is still active. Can I close word application with the document at the sam time. This is the code I'm using: "ActiveWindow.Close"
View 3 Replies
View Related
May 19, 2008
I want is a Macro that will save the file as .csv, with the same filename and location as the original (just in csv format). A hotkey would be quite handy for this. I tried using the macro recorder and fiddled around with the code a bit but my programming knowledge is quite limited and I ended up with something that doesn't quite work as i'd like. I'm sure this would only take a few seconds to code for an experienced user, which is why I post here. I would post my attempt at doing it myself but i'm on a computer that doesn't have excel at the moment.
View 3 Replies
View Related
Apr 29, 2009
I currently have a macro set up to run once a workbook is saved/ closed. This works fine unless tehre is more than one workbook open. When I try it with more than 1 different workbook open I get a run error.
My code begins:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
and then performs the macro. I don't really want to specify the filename as this is liable to change. Is there anyway of adding a "this workbook" command to the text string, and if so where would it go?
View 10 Replies
View Related
Dec 16, 2011
if workbook("Result") is not open then
add new workbook and name it "Result + date and time"
else activate workbook "Result"
View 3 Replies
View Related
Mar 21, 2013
I have got a master workbook and I have written macro to copy and paste data on another workbook. write a macro to save the new workbook to a file path with a file name where both file name and path are stored in master workbook sheet...
View 5 Replies
View Related
