Save A Copy Of The Open Workbook

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


ADVERTISEMENT

Save Copy Of Open Workbook And Make PDF For Archive

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

Open Workbook / Copy / Close Without Offer To Save?

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

Prompt Open Closed Workbook Then Copy Range And Paste Special Into Open Workbook

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

Open Workbook Then Save As Different Workbook Dependant On A1

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

Workbook Open To Remind To Save As

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

Macro To Open / Close / Save Workbook?

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

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 View Related

Open Workbook, Find Last Row & Paste, Save And Close

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

Create Open Copy Of Open Workbook

Jul 30, 2009

Create a full copy of an open workbook (eg. activeworkbook MyFile.xls) using VBA, with the new copy (eg Book1.xls) open as well ,without having to save a copy first then open it ?

View 9 Replies View Related

Excel 2007 :: VBA To Save And Close All Open Workbooks Except Macro Workbook?

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

SaveAs - Copy Sheet Into New Workbook And Save It In Same Folder As Original Workbook

Jun 17, 2014

I have been trying to edit a code which previously saved a copy in a new workbook to a specific folder/path. (Additionally it copies and clears some figures, but this is working as it should.)

However I would like the copy to be saved at the same location as the original workbook, regardless of the path the original workbook is saved.

I.e if I need to move the workbook containing the code to a new folder/location, when using the macro, the new copy should be stored in the same folder/location as the original one.

For now it is only saving the copy into "My Documents"

Code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 16-02-2009 by ceng
'

Sheets("Bunker ROB").Select
Sheets("Bunker ROB").Copy
ActiveWorkbook.SaveAs Filename:= _
ActiveWorkbook.Path & Range("D3"), _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

[Code] ........

View 2 Replies View Related

Copy Data From Workbook / Open Existing Workbook - Select Range And Paste

Mar 26, 2012

Copy data from workbook, open existing workbook, select range and paste. But my copied data is lost.

Sub Select_Copy_Paste()
'
'
Windows("ElektroFunctiesDatabase.xlsm").Activate
Sheets("PowerSupply's").Select
Range("A2:I6").Select
Selection.Copy

[Code] .........

' Here i need to do something to paste data into r.address?

View 4 Replies View Related

Making Backup Copy Of Active Workbook While Workbook Is Open

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

Modify Existing Macro To Copy To Different Workbook Instead Of Open Workbook?

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

ActiveX Combobox - Linked Cell Reference Lost After Save / Close And Re-open Workbook

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

Excel 2010 :: Can't Save Custom Table Style Format So That It Is Available Next Time When Open Workbook

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

Macro To Open File, Copy Paste As Values And Save As Excel Html

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

Copy From Closed Workbook To Open Workbook

Apr 25, 2007

I'm trying to write a macro that accomplishes the following:

" Book 1" is already open. The user runs a macro that lists all .xls files in directory "d:measurements" The user selects the desired file from the list or box the macro copies from this "book 2" " sheet 3", " range A6:I107 and pastes (values only) into "book 1", "sheet 5", "range A6" End of macro.

I want to accomplish this without opening the selected file (book 2)

View 4 Replies View Related

VBA To Copy Worksheet To New Workbook And Save

Oct 19, 2011

How to copy the entire worksheet into a new workbook and save the file to a specific directory with the following filename format ("exceptions191011 - ie. the word exceptions followed by today's date in ddmmyy format).

Here is what I've got so far:

Code:

Sub SaveAs()

Dim FName As String
Dim FPath As String

FPath = "G:Exceptions"
FName = "Exceptions" & Format(Date, "ddmmyy") & ".xls"

Sheets("DataSort").Copy
ThisWorkbook.Sheets("Sheet1").SaveAs Filename:=FPath & "" & FName

End Sub

How to add the following condition that if a file already exists with that name then to bring up an error message stating the file already exists? As well as save the new workbook, it appears to close the original workbook - can I stop this?

View 3 Replies View Related

Save Workbook As Copy To Another Directory

May 20, 2009

I need save my workbook as copy to another directory, ( C: ) with VBA code

View 9 Replies View Related

Copy Values Only To New Workbook & Save

Sep 8, 2006

i need to copy from one workbook to another at present i have formulars in columns a b c d e g in f and h the is data that could go to f and h100 these are not formulars

i need to be able to copy the data from a1 to the last cell with data in it in column A to the same row in column h

View 9 Replies View Related

Copy Worksheet To New Workbook & Save

Oct 18, 2006

I have a workbook with a worksheet named "Report"... this sheet and it's macros generate a report from data on another sheet. I'm adding a button to copy the (filled) Report sheet to a new workbook, rename the new sheet in the new workbook "Rep[Name]", then save and close the new workbook. I've recorded a maco of me doing these tasks manually, but what's recorded seems very generic, and does not seem to specify what's really occurring...

For example (in part);
> Rightclick on the Report tab
> Move or Copy
> To Book: (new book)
> [x]Create a Copy
> [OK]

yields:

Sub Macro1()
Sheets("Report").Select
Sheets("Report").Copy
End Sub

This recorded macro does not specify (new book), or Before:"Sheet1" as was the result of my actions... and when replayed back, only makes a copy in the active workbook, before the active sheet...

View 4 Replies View Related

Open Workbook & Copy To It

Jul 5, 2007

I am trying to write a piece of vb code that opens a workbook and enters some data into it, saves it as a different file name and closes it.

Basically, I have a worksheet with two sheets. One is a list of a few values (order number, description, date), the other has three cells which the user enters the values into, and a command button. When pressed it transfers the data into the table on the first sheet.

I then need it to open a workbook C:/ewr.xls and put these values into this, rename it as ewr180.xls, (where 180 is the order number) and save it

I'm almost there but just cant seem to get the values into it

I know i must be missing something so simple, any help would be much appreciated.

Private Sub createEWR_Click()
Dim ewnnum As Integer
Dim descr As String
Dim ewndate As String

' enter new row with data

View 3 Replies View Related

Macro To Copy Rows To New Workbook And Save?

Mar 9, 2012

I'm looking for a macro that can do the following:

Contact
First Name
BD
Case
XXXXXXX
Aaron
A
XXXXXXXXXXXXXXX
XXXXXXXX
Abigail
C
XXXXXXXXXXXXXXX
XXXXXXXXXXXXXX
Accounts
D
XXXXXXXXXXXXXXX
XXXXXXXXXXXXXX
Adam
C
XXXXXXXXXXXXXXX

Filter by BD 'C'

Copy all rows containing 'C' to new workbook.

Save new Workbook as 'Mailing C.xls' and close to specific flolder.

View 7 Replies View Related

VBA To Copy Multiple Sheets Into New Workbook And Save

Jun 23, 2014

I have managed to pull together code that does the required task - save two sheets from a work book in to a new workbook on to a dorectory each day. However I woul;d liek to paste special the values and cannot figure our how to reference that on the below:

'saves text file in day on day folder
Dim WS As Worksheet, CheminDest As String, fNAME As String
'create directories as needed
On Error Resume Next
CheminDest = "T:DMRatesReportsChecks" & Year(Date) & ""

[Code] ..........

View 3 Replies View Related

Save Static Copy Of Workbook. No Formulas

Aug 16, 2007

Is it possible to use VBA code to save a copy of your workbook while removing all formulas and replaceing them with values and formats only? I know how to save and rename, but can;t find the command for a "Static Copy".

View 9 Replies View Related

Save Workbook Copy To Same Path As Original

Aug 27, 2007

I'm trying to save a copy an excel file in the same directory of the original file. The code is pretty standard but it does not work if the file is stored in C: (only c: ) Is that a bug or am I doing something wrong? Anyways here is the code in case someone is interested in trying it out:

Sub CreateCopy()
ChDrive ThisWorkbook.Path
ChDir ThisWorkbook.Path
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls", _
InitialFileName:="CMS_" & Format(Now(), "mm-dd-yyyy"))
If fileSaveName <> False Then
MsgBox "Backup copy saved as: " & fileSaveName
End If
End Sub

View 5 Replies View Related

Save Copy Of Workbook, Leaving 1 Sheet Only

Sep 11, 2007

I have a workbook that has worksheet called "Data". In that workbook, there is some codes for the event of "workbook_beforeprint". My question is whether i could create a copy of the "master" workbook and delete all the unwanted sheets but "Data" sheet using VBA. There are some codes of creating a new workbook but i suppose i need to the exact copy of the current workbook using VB

View 3 Replies View Related

Save Copy Of Workbook Without Closing Original

Jun 14, 2008

I want to have a user press a button on a worksheet page to save a copy of the workbook (and be able to name it and choose where to save it to) without closing or modifying the original. Note that if you "save as" manually it renames the workbook and closes the original.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved