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
ADVERTISEMENT
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
Oct 25, 2007
I have a workbook within which i have a worksheet that contains a lots of macro code (coded by me). As the workbook gets used by various people, i need to copy the worksheet and the macros across to the updated workbook, which doesnt contain the macro worksheet at all.
I have tried to copy it across by clicking on its tab and using the move or copy facility. This copies the sheet across as required. But for some reason, the macros all reference the old workbook. A small bit here for example for some reason opens up the old workbook and then performs the code in the old worksheet:
Sub SelectAll()
For i = 12 To 20
Set curcell = Worksheets("Form Generator").Cells(i, 3)
If curcell = False Then
Cells(i, 3).Value = True
End If
Next i
End Sub
As curcell is equal to worksheets...() i would have expected it to use the local worksheet, ie the one that the macro is attached to. So why is excel proactively hunting out the old workbook and sheet? is the method i used to copy across the sheet with the macros incorrect? If so, how should i go about it?
View 3 Replies
View Related
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
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
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
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
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
Aug 14, 2013
I'm looking to link/embed a summary sheet from one work book to be displayed in another. When i update this summary sheet i wish for it to update the linked work book.
View 7 Replies
View Related
Apr 21, 2012
I have a workbook that contains 50 worksheets named 1-50. I need to add more worksheets. all the formulas in the worksheets always refers to the previous worksheet.
How can i make a copy of the worksheet named 50, name it 51 and have all the formulas in worksheet 51 refer back to worksheet 50?
View 1 Replies
View Related
May 24, 2006
I have created a workbook that imports data from another workbook which is used frequently on a network drive. After I import the data to my new workbook, it locks the original workbook for editing. Is there a property that will allow me to disable this 'locked for editing' read only mode or any other way to get around this?
View 6 Replies
View Related
Mar 23, 2009
I have code that i use to copy a worksheet of information in one workbook to a worksheet in another workbook. All i need is some guidance on how to copy a second worksheet from workbook 1 to a second worksheet in workbook 2. Should be fairl straight forward.
In the below i am copying the sheet Phone_data to a second work book sheet also called Phone_Data, i would like to include in the same proccess a sheet called Sur_Data from workbook 1 copy to a sheet called Sur_Data_R in the second. All the other features like find next empty row also apply.
View 2 Replies
View Related
Aug 21, 2014
I have an open workbook (A) and this is where the code should reside. I want to use VBA to copy the content of an entire worksheet from a closed workbook (B) to an existing worksheet in workbook A. How would you accomplish this?
View 7 Replies
View Related
Feb 7, 2014
I am looking for a code that would copy the data from each worksheet in a given workbook and then paste to just one worksheet within a different workbook. The Sheet names are auto generated when I run this canned report but the naming structure is always the same...the first worksheet is named Repair Details and then the next sheet is named Repair Details_1, the next sheet is named Repair Details_2 and so on for every sheet in workbook. So I would like to copy all of the data(Headers to last cell) and then paste in a worksheet(ex: Master Repair Report.xlsx and the worksheet could be titled Master Repair Details) on a different workbook, then the next sheet would copy from the one under the header to the last record and paste to the same workbook. This process would repeat for every worksheet in the Repair Details Workbook and paste to Master Repair Details worksheet in the Master Repair Report workbook.
View 4 Replies
View Related
Aug 12, 2009
I need to copy a range of values (actually two ranges). The ranges are of fixed size. Le't say A2:D20 and E2:H20.
Both workbooks have the exact same layout, and they both have the same named worksheet (in this case it's called Entry). However the actual filename of the source workbook is unknown. I know numerous users have changed the filename of the xls file.
I want to instruct them to open the old and new workbook, and open a third workbook containing the copy macro, they run the macro and it copies the data.
How can I reference a known worksheet name but of an unknown workbook name esp when the source and destination share the worksheet name?
View 3 Replies
View Related
Feb 19, 2014
I have a sheet named sheet9 and i want to copy the data from this workbook-sheet9 to a workbook called import data and sheet named "database".
View 5 Replies
View Related
Mar 16, 2007
I recorded a macro in a workbook called masterschedule. The macro works only if I run it when I am using the masterschedule. Each week I open the masterschedule workbook and name it the current week, for example 3-26-07 schedule is the name of the most current schedule. I then open 7 other spreadsheets and paste information from the current schedule 3-26-07 (this week) onto 3 different sheets in all of the other spreadsheets. It works fine if I am using the masterschedule. Others have access to this workbook once it is named something else. I do not want to allow others to have access to the master workbook. Can someone help me with this? I've attached part of the macro below. I need it to work in whatever the masterschedule is renamed to.
Windows("MASTERschedule.xls").Activate
View 9 Replies
View Related
Aug 21, 2014
I'm setting up a payroll for several employees in one workbook. I start from the particular employee's worksheet and then I need to go to another sheet where various information is stored and then come back to the original employee's sheet. The current macro works fine if the various information is stored on each employee's worksheet but I can't figure out how to go to another sheet and then come back to my active cell in employee sheet.
[Code] .....
View 14 Replies
View Related
Dec 21, 2008
I am looking to create a macro button which will reset the sheet to its original state.
I have locked the cells users should not imput data into, and unlocked where they add their data.
At the end of every school year, they will need to be able to reset ALL the workbooks back to their original state with all the reference formulas.
I am thinking I will need to tell the macro to create a hidden copy of the workbooks and then upon hitting reset it will use the backup to override the current. But they will need to be able to reset at the end of every year.
I would like to have one reset button that resets ALL the workbooks at once, but if not, I could put a reset button on each workbook.
As I have just started my VBA training, I have a code that will create a backup and hide it, but I have no idea how to do the reset portion. And again, since they need to be able to reset it each year for x amount of times, I don't know how to get it to keep having a fresh backup and get everything to its original.
View 9 Replies
View Related
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
Sep 5, 2012
The problem is when I highlight a row with some color the original color of the row is gone, so I tried this code, and again, it's removing the original format and color for the row This is the code from McGimpsey & Associates : Excel : Highlight row with background colors
Code:
PrivateSub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Const cnNUMCOLS AsLong=256
Const cnHIGHLIGHTCOLOR AsLong=36'default lt. yellow
Static rOld As Range
Static nColorIndices(1To cnNUMCOLS)AsLong
Dim i AsLong
IfNot rOld IsNothingThen'Restore color indices
[code].....
How can I retain the range's historical color so that when I deselect the row it reverts properly?
View 4 Replies
View Related
Oct 28, 2008
I have been struggling with trying to find a way to copy worksheets from one workbook to another. The workbooks change each month. As I was searching the forum for previous threads on this topic, I found this great code, but I need to modify it just a bit and I do not know how to do that. The code allows the user to select the worksheet to import, but it imports it to a new workbook and I need to be able to not only select the sheet to import, but the workbook to import it to.
View 8 Replies
View Related
Sep 27, 2009
Currently i am using this simple code to copy the relevant sheet in workbook 1 to workbook2.
View 14 Replies
View Related
Jan 25, 2012
Though VBA I am trying to copy one worksheet into a new workbook. This worksheet contains Graphs which point to other datasheets in the same workbook.
I first copy the data worksheets, then I copy over the worksheet with graphs. However the graphs link to the original worksheet name- not to the datasheets in the same workbook as the newly created workbook. This is a problem since I am going to send this other workbook to users and they won't have access to the orginal datasheet.
I copy the sheets using the code:
Set wsheet = inbook.Sheets("Division")
wsheet.Copy after:=gobjOutBook.Sheets("Executive Summary")
Where the worksheet DIVISION is the worksheet containing the graphs and the data sheets are already copied there.
View 1 Replies
View Related
May 8, 2012
The macro below copies certain sheets from one workbook to another, however the worksheets are protected in the original but when they are copied to the new workbook the protection is removed. Is there any way to keep them protected once copied?
I also have a couple of buttons with macros assigned to them one of which I would like to remove when the worksheets are copied to the new workbook, is this possible?
Code:
Sub REN_REPEAT()
' Rename
Dim myName As String
'Copies the sheets to a new workbook:
Sheets(Array("NEW_ORDER_", "CP_NOTES", "DATA")).Copy
[Code] ........
View 4 Replies
View Related
May 6, 2008
I am trying to copy a worksheet called "Survey Extract Agents" from one workbook into another called "Raw Test Data". "Raw Test Data" is also the name of the sheet in the workbook of the same name. I want the "Survey Extract Agents" to be inserted after the "Raw Test Data" sheet. My macro keeps choking on it....
View 9 Replies
View Related
Feb 10, 2010
i've been programming VBA code but it seems that i can no longer copy or move a worksheet within the workbook i was working on...
View 9 Replies
View Related
Feb 13, 2007
1. When the workbook to be closed ("Workbook before close"), add a new workbook ( saving the filename as Range("D6") & Range ("D5").xls).
2. Save the first workbook as in folder "Range("D6")" with filename of Range ("D5").xls
3.Copy the sheet called "Workbench 1" (values only) from the first workbook to the second one.
4. Save the new workbook again, then close both.
View 4 Replies
View Related
Oct 15, 2012
I need to copy the sheet being worked on, and place it behind the original sheet.
This is going to be in a Macro enabled template that other users will be rename when they save it.
It will be activated by a button on the original worksheet other users may need to rename the worksheet before copying so I am using ActiveSheet.Copy I don't want to put the sheet after a counted sheet, because other sheets may be inserted before the one being worked on. I am not proficient at VB, I basically search for a macro that does what I need and copy it.
This is what I am working on.
Sub CopySSR()
'
' CopySSR Macro
'
'
ActiveSheet.Copy After:=Sheets(7)
End Sub
View 2 Replies
View Related
Apr 20, 2008
I was wondering if it is possible to have a macro running that will automatically save a .csv file to specific location and close the file. I would like to have the macro running so that as the files are opened they will be automatically saved to this location.
View 2 Replies
View Related
