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"

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


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

For All Workbooks In Folder - Copy Range To Different Workbook - Save To New Folder?

Apr 21, 2014

I have up to 50 workbooks in one folder with data in a specific range. I also have one workbook which includes additional data, including conditional formatting and dropdowns. I need to copy the desired range from the first workbook in the source folder to the second workbook, then save the latter to my destination folder, using the same name as the first. I need to repeat this process for all workbooks in the source folder.

View 2 Replies View Related

Copy Sheet To Same Workbook Without Overwriting Original

Aug 11, 2007

I have some code written to duplicate a template and rename the copy to "Working Copy".

But if I run the code more than once, it breaks as VBA tries to overwrite the sheet with the same name.

Would I would like is for the macro to check to see it already exists and duplicate the copy with some type of incremental integer. Thus the first duplicate would be Working Copy 1, and if the macro is run again, the duplicated copy would be Working Copy 2, etc.

here is what I have so far:

Sub SCButton()
Dim i As Integer 'for making Working Copy 1, 2, etc.

Sheets("SCTemplate").Select 'this file will eventually be hidden and thus the user with only see the wokring copies.
Sheets("SCTemplate").Copy After:=Sheets(4)
Sheets("SCTemplate (2)").Select
'some sort of If statement here to check for the sheets

Sheets("SCTemplate (2)").Name = "Calculation"

View 5 Replies View Related

Save Copy / Backup Of Workbook As Date In Certain Folder

Sep 7, 2013

I need to save an excel workbook when a "backup" button is pressed to a folder named "Backups". I want the name to be the "date" (09/07/2013) only and I want it to automatically overwrite anything previously saved for that day.


View 2 Replies View Related

Save Workbook In Folder & Sheet Named In Cells

Aug 23, 2007

What is the code to automatically save the new template worksheet in a folder and name defined in a couple of cells.

The folder name will be always be: "Q:AssetsCustomersfolder name"

I need the folder name to come from cell C3 on the spreadsheet and the filename to come from cell R3.

View 2 Replies View Related

Prevent Workbook Save But Allow SaveAs

Jun 24, 2008

I've got a workbook that I want to keep intact in a central location on our local network, to make it easier for everybody to access, but don't want people to be able to save changes to the master workbook itself. I'd like to be able to disable the 'Save' feature while still allowing 'SaveAs'. The following code (installed in ThisWorkbook) results in the OPPOSITE of what I'm trying to accomplish (i.e., it allows 'Save' but disables 'SaveAs'):

Private Sub Workbook_BeforeSave(ByVal SaveUI As Boolean, Cancel As Boolean)

If SaveUI Then
MsgBox "The 'Save' function for this workbook has " & Chr(10) & "been disabled. Please use 'Save As'.", vbOKOnly + vbInformation, "Save Disabled"
Cancel = True
End If

End Sub

Obviously I'd also like to check the filename they're performing the 'SaveAs' under and disallow it if it matches the master filename.

I've thought about making the workbook into a template, which would sort of accomplish the same thing, but it would be much easier to just keep it as a workbook.

View 9 Replies View Related

SaveAs Worksheet To Another File / Folder While Keeping Original Open / Active?

Apr 15, 2014

I have an Excel Worksheet (let's call it "AA.xlsm") which uses VBA code to loop through column A, update column B with some results/values, and then (for each value in column A) SaveAs the worksheet into a different filename/path as a ".csv" type file.

My line of code for the SaveAs is as follows: Workbooks("AA.xlsm").SaveAs LEpath & CurrFldr, FileFormat:=xlCSV

My problem is that my original worksheet, AA.xlsm, gets closed whereas I need it to stay open/active after each SaveAs iteration.

View 3 Replies View Related

Delete Original Workbook After Save As

Mar 21, 2008

I don't believe this is possible but I've been wrong before.. Is it at all possible to Kill the current workbook using VBA? I " saved as" in another location and do not wish to keep the current workbook.

View 3 Replies View Related

Copy Whole Sheet To Another Close Workbook In Same Folder

Feb 2, 2009

How can I copy whole sheet (only value and format) in other close workbook next to used sheet if it is in the same folder (folder can be in Desktop in C or D)

View 9 Replies View Related

SaveAs Of Multi Sheet Workbook With Filters?

Mar 25, 2009

I have a Workbook containing seven or eight Worksheets. The first Worksheet is a data entry and options selection sheet displaying a selection of Textboxes, Option buttons, Combo boxes etc, the second sheet contains reference data, following these are a number of Worksheets that are calculated and filtered as a result of those options and inputs. A 'print' button on the input Worksheet then runs a Procedure that applies the relevant filters, assigns print areas and prints out the 4 or 5 filtered worksheets. I now want to expand this Procedure to take a copy of just these filtered Worksheets and save them as another Workbook (values only) for subsequent free editting.

I have started by selecting the relevant cells on the first filtered Worksheet, copying, opening a new Workbook, renaming the first Worksheet to match the one I'm copying, PasteSpecial formats, PasteSpecial the values, go back to the original Workbook, select the relevant cells on the next Worksheet, copying, opening the new Workbook, renaming the Worksheet etc etc. Now this seems to be a very labour intensive approach and I'm wondering if there is an easier way and how to do it. Ideally something like... taking a copy of the whole Workbook somehow changing all the cells to values only, rather than formulars then deleting the first two Worksheets (input and data ones).

View 4 Replies View Related

ActiveWorkbook.SaveAs Only Saves One Sheet, Not Whole Workbook

Aug 2, 2006

I have a csv .txt file that I am importing into Excel, and then creating a pivot-table from that data. This results in 2 tabs within my workbook, pivot-table and data. Using the following code does everything I want it to, but when I re-open the saved .xls file I only have the pivot-table sheet. I'm specifying to save the full workbook, aren't I?!?

Sub format_bgas_flowtracer()
Dim v_excel As Excel.Application
Dim v_worksheet As Worksheet
Set v_excel = CreateObject("Excel.Application")
On Error Resume Next
Kill "c:oh_for_gods_sake_JUST_WORK_YOU_STUPID_SODDING_THING.xls"
On Error Goto 0
v_excel.Workbooks.OpenText FileName:="D:gas_flowtracker_out.txt", Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, _ ...............

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

Single Sheet SaveAs Without Changing WorkBook FileFormat

Feb 25, 2009

I wish to save a single sheet from a multi-sheet workbook as "xlTextPrinter"
to do this I use:

View 4 Replies View Related

Copy Current (Selected) Sheet To New Workbook And Save As Tab Name

Feb 28, 2014

I want to take the current sheet and copy to a new workbook (keeping the tab name) and then save as the tab name (xlsl) and close.

I've found ones that do a Saveas box or ends up saving as "Sheet1," "Sheet2" etc but can't seem to find one that keeps tab name and saves.

Here is the one I was using, but it saves all tabs not just selected:

Sub CopySheet()
Dim fname As Variant, ws As Worksheet, wb As Workbook
Dim nCol As Integer, iCol As Integer


View 1 Replies View Related

VBA Copy From Sheet To New Workbook And Save As From Name In Target Cell Then Loop

Aug 27, 2012

I need VBA code for the following - I have a worksheet with seven colums of data (A to G) - I need to copy the first column (A) from the active worksheet then open master workbook called 'master' and paste the data in to column D - then save the 'master' as the name in cell Z1 of the 'master' workbook. Once this has been been completed I need to repeat the process but this time copying column (B) and so on.

View 4 Replies View Related

Date - Sheet To Copy The Info To Another Workbook, Save And Close

May 3, 2007

I have a sheet that users fill out and there is a macro on that sheet to copy the info to another workbook, save and close.

for some reason, the date, which originates from a cell with the value "=Today()", and when pasted in to the new book, it is pasted values.

the issue is, every so often, the date will say 09/26/10 and continue to do so for quite a few entries.

i have tried almost everything. I checked the dates on the computers, and all are fine. i even went through it step by step, and when the data pasted it, it was 09/26/10.

View 9 Replies View Related

Save Workbook Using Variable For Save As And For A New Folder Name

Jul 31, 2009

I have figured out how to save the workbook using a variable to saveas but I would like to create a new folder to place the new workbook into. I have tried a few things a am not having any luck. I recorded a macro and the code it produced is as follows.

View 6 Replies View Related

Save Single Sheet To New Workbook And Delete VBA Code From New Workbook

Jun 25, 2014

I'm trying to find a way to save a single sheet of an excel workbook and in the same process delete all vba code and shapes from the new single sheet workbook. I was looking around and found this code which does save only the single sheet to a new one sheet workbook but doesn't delete the vba and shape that I have used to assign macros to in the original.

Sub SaveSheetAsNewBook()
Dim wb As Workbook
Dim InitFileName As String
Dim fileSaveName As String
Dim wshape As Shape
InitFileName = ThisWorkbook.Path & Format(Date, "mm.dd.yy")


View 2 Replies View Related

Save Workbook To Same Folder

Jul 13, 2007

I have a workbook that is located in a folder and i need to copy this folder into multi locations, i need a save as macro that will save the workbook back to the same folder location it was opened from, i have got the workbook file name that i wish to use in N6 of the workshee

View 3 Replies View Related

Copy From One Sheet To Another Sheet Then Save In Another Workbook

Apr 9, 2014

have a basic knowledge of VBA. The task I am trying to perform with VBA is to

1. Copy from sheet1 A1 to sheet2 E9
2. Copy from sheet1 B1 to sheet2 E11
3. Save sheet2 in a new workbook file with the file name cell data from sheet1 A1 (511894.xls)
4. Copy from sheet1 A2 to sheet2 E9
5. Copy from sheet1 B2 to sheet2 E11
6. Save sheet2 in a new workbook file with the file name cell data from sheet1 A2 (097219.xls)
7. Repeat the process down columns A and B to the end of the columns.
8. Columns A and B will end at the same time but the data and the last cell will change with each scan added to the columns.

This code will accomplish this task with a single column of data but not two columns. Also column B will have a mixture of numbers and letters in its cells, Column A is only numbers.

Sub MoveData()
sName = "temp"
Const csPath As String = "C:Documents and SettingsmcgaulcDesktopTestFile"
'MyName = ActiveWorkbook.Name


Here are some images of the data sheet and the saved sheet

Sheet1.jpg Sheet2.jpg Note: sheet2 in image is "temp" sheet in code

View 7 Replies View Related

How To Copy A Worksheet, WITHOUT Linking It To The Original Workbook?

Sep 27, 2008

A simple one for you, but again my Google skills seem lacking. This must be common practice but until one knows the key combination...

I have created a summary sheet with lots of formulas which analyse other sheets in a workbook. I want to copy this summary sheet to a seperate workbook to use as a template.

Of course, the copying process always links the new (template) sheet to the original workbook it was copied from. How does one copy without forging this link? All I want are the formulas copied across totally unchanged; I really *don't* want to manually edit 50 or so formulas!

View 7 Replies View Related

Create Copy Of Workbook Without Closing Original

Dec 3, 2007

I would like to create a copy of an open workbook.

Workbook.SaveAs would seem to be a possibility, but this closes the original workbook - I need to keep the original workbook open as well as the workbook it has been "SavedAs".

I do not want to close the first workbook and repoen it as there are instances where it may be protected and I do not want the user to have to reinput any passwords etc.

View 9 Replies View Related

Create And Save Workbook In Folder

Apr 27, 2013

I want to write a piece of code where each day i run this code

I want add a workbook

Copy range ("A1:C5") to this new workbook

Save new workbook in a particular folder with the name customer data and todays date and save as CSV file

I run this this code every day so it should only add todays date at the end for filename

If for some reason I run this code again in the same day..

I want to have a msgbox stTing you have already ran this code today and exit sub

View 4 Replies View Related

Macro To Copy Dynamic Sheet In New Workbook - Save As Temp / Mail And Delete

Sep 15, 2014

Code to copy the sheet that get selected by if-then-elseif statement and paste in a new workbook, save the workbook as temp, email it and delete it,

sub sending_mail

Dim OutApp As Object
Dim OutMail As Object
Dim rng As Range
Dim DataWB As Worksheet

[Code] ......

View 2 Replies View Related

Create Copy Workbook With Cell Referencing To Original?

Apr 15, 2014

At my work we are using Excel as a CMS to produce SQL scripts for insertion into a database. The data is then turned into mobile web pages. This involves multiple workbooks - 1 for each main page with the sub-pages as extra worksheets in the book.

Much of the data is duplicated so we have created master workbooks then copied them and laboriously referenced the relevant cells back to the original. That way if any data in the master is changed, all copies will change simultaneously.

This is obviously not the most efficient way of publishing web pages but we are stuck with it for now. So I am wondering if it is possible to write a VBA code to create a copy of the master and then reference all required tables in the worksheets automatically? The tables are all named ranges.

View 9 Replies View Related

Save Workbook To Folder Chosen By Users

Aug 16, 2007

I have a summary workbook that I want to save into different folders designated by the input from my userform. My directory has folders named: "A051 - Watson", "A052 - Gila Bend", "A065 - Tompkins"... etc; but I want to designate which folder to put the summary workbook in by the first 4 characters in the folder names(A051,A056,A055...etc) Let me know if I made it clear enough.

View 4 Replies View Related

Save Workbook In Same Folder As Source Input Data

May 2, 2014

I currently have a macro to import user selected .Dat files into a new workbook, each on its own worksheet. My problem comes in trying to save this new workbook in the same folder as the imported .Dat files. I was thinking there should be a way to gather the file path from the imported files and use that in the Save As command.


View 2 Replies View Related

Save Workbook With Current File Name To Specific Folder

Dec 4, 2007

I recorded a macro to save a workbook to a specific folder using the current file name. However, I've tried to remove the absolute reference to a specific filename but it is not working for me. Am I close?

ActiveWorkbook.SaveAs Filename:= _
"H:Files and DocumentsPROJECTSSR 2 SS Phase Two Component ReliabilityWinTrac Files xt_conversionsexcel_version" & SheetName _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

View 2 Replies View Related

Copyrights 2005-15, All rights reserved