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
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
Jul 1, 2014
The two sheets are...
1. Sheet1.xlsx (regular excel sheet as the extension is .xlsx)
2. Consolidated.xlsm (macro enabled sheet as its extension is .xlsm)
1. Copy both the files on your system under a particular folder.
2. Now make 9 replica's of Sheet1.xlsx and name them as Sheet2, Sheet3, Sheet4, .......... Sheet10
3. Open the sheet Consolidated.xlsm and see the button i have added called "Pull Data".
Now write down the code by adding a Module in that excel sheet from the code window.
4. Write a code that will open each of these sheets (Sheet1, Sheet2, Sheet3, Sheet4, .......... so on) one at a time and will copy the data from these sheets to the Consolidated.xlsm sheet.
5. Make sure that the data is appended(and not overwritten) from the multiple sheets (Sheet1, Sheet2, Sheet3). that is once you paste the data from sheet1 to Consolidated.xlsm then the Sheet2 data will be pasted at the end and after that sheet3 data will be pasted and so on..
6. In this way at the end we will have all the data from Sheet1, Sheet2, Sheet3...in the consolidated sheet.
7. The Division column in the Consolidated sheet will have the value of first row in these multiple sheets. So after making the replicas of Sheet1, please change the value in first row (Range A1) to any other value to avoid the confusion.
8. I have highlighted the data for two sheets in yellow and grey color in the consolidated sheet.
View 8 Replies
View Related
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
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 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
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
Feb 11, 2009
I need to copy the Selected Sheet (Sheet name will be different each month) on a spreadsheet and paste the copy to the left of the selected Sheet. Then I need to copy and paste values the entire sheet of the sheet that the copy was made from (the one on the right). I am very new to macros, and I tried recording and manually editing the macro with no success. The number of sheets will be different always as I will be adding this to different workbooks and also because new sheets may be added to any workbook at any time. I attached my code that I came up with, as I am not familiar with code enought to "[code]" my code.
View 3 Replies
View Related
Nov 12, 2009
I'm trying to find a way to search a second sheet in a workbook for specific criteria outlined in a first sheet (in my attached example, from A3 downwards within the 'list of search criteria' sheet), and then to copy any secondary data found against a successful search match to the original sheet, transposed against its corresponding matched search term.
As you can see in the example, the search term 'bindi' (A4 in the 'list of search criteria' sheet) appears in the 'data' sheet 3 times - the secondary data for these occurences ('feathery', 'Fibonacci', 'glassy') is copied to the 'bindi' row on the first sheet and is offset with each copy to produce a transposed-esque effect of copy and paste.
If it's any help, there are a maximum of 9 matches for a single search term in the real document.
Thanks in advance for your help... I tried to adapt a previous solution given to me for a similar question but failed miserably. I bow humbly to your expertise!
View 9 Replies
View Related
Jan 19, 2008
I need to create a macro that will insert copied cells(cell range) from the clipboard into a cell on a different workbook and "shift cells down". The cell range in the clipboard will always be different and the position of the cell I select to insert the data into will also vary.
View 4 Replies
View Related
Aug 11, 2014
I have a large spreadsheet which I have extracted the data I need, but I am now having a problem tiding it up.
To simplify. I have 2 columns, both containing data formatted as text, each column has some data, but on different rows.
I need to merge the 2 columns without overwriting the data on the second column, with blank data from the first column, and without the column shrinking or growing.
Here is a section of my spreadsheet that needs merging : Excel-1.PNG
I have tried copy > Paste Special > Skip Blanks, but it just copies the column to the other - no merging.
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
Apr 7, 2013
I have created a code in which the information from Sheet 1 copies to Sheet 2 correctly. Every time I input new information for my click event (ticket sales/amount received/change due) I want this information to be displayed in Sheet 2. However, every time I start a new submission, the information overwrites on the second Row so that only one submission is displayed. I need to create a history of all submissions from Sheet 1. Here is the code that I have to copy Sheet 1 to Sheet 2:
VB:
intRow = intRow + 1
If intRow = 1 Then
intRow = intRow + 1
End If
[Code] .....
View 2 Replies
View Related
Apr 25, 2014
I need a code that will copy any cells with data in range I3:I41 from sheet2 and paste it in sheet1 starting at cell B3. Then copy any cells with data in range I3:I41 from sheet3 and paste it in sheet1 starting at the next empty cell.
View 9 Replies
View Related
Sep 19, 2012
I have a copy and paste macro below, that copies the selected rows and pastes them into a different sheet called Blank BOM. Each time they are pasted, it just writes over the previous items at the top of the list. I would like it to paste in the next open row, so I can go back and forth between the sheets and add things. Here is the code:
VB:
Sub CopyRow()
Selection.Copy Sheets("Blank BOM").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
End Sub
View 9 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
Dec 12, 2012
I've got a User Form that works perfectly. It's activated on Chart Month sheet, you enter data into it and it switches to Comments sheet and copies the data, before returning back to the Chart Month sheet.
I have now added the same functionality to Chart Week sheet. Both Chart sheets use the same comments data, so it doesn't matter if you run the User Form from Chart Month or Chart Week. However, when I run the User Form from Chart Week then it switches to the Chart Month sheet once it copies the data, as I have Chart Month in the code. How do I get it to return to the sheet that it was originally on ie either Chart Week or Chart Month?
Code:
Private Sub CommandButton1_Click()
Dim emptyRow As Long
'Make Sheet30 Active
Sheets("Comments").Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("D:D")) + 1
[Code] .........
View 4 Replies
View Related
Mar 11, 2013
Possible to look up a value from one excel sheet in another sheet and then return multiple values in the first sheet
For example the following is a sample of sheet 1
NSV Code
Item
12
OLANZAPINE
54
ROPINIROLE
And the following a sample of sheet 2 :
Nsvcode
Item
Division
Speciality
Qty
12
OLANZAPINE
CLINICAL
CS
10
[Code] .....
I want to lookUp the NSVCode from sheet 1 in sheet 2 and return the speciality and qty values in sheet 1 as sheet 2 contains a lot of info I do not require and sheet one also contains various pricing calculations I also require.
View 7 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
Mar 6, 2014
My requirement is as follows......
I want to get a copy of worksheet from specific workbook to active work book in which i want a copy of sheet get moved.
View 2 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
Mar 6, 2014
I have a Picture in a workbook kept open. ( workbook 1 )And i have some X workbook open....i want a copy of sheet from workbook 1 To X workbook which is currently active.
View 1 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
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
Sep 18, 2009
When i cut a row and paste it to another sheet, that row on the original sheet is shrinked. I don't know if my vba code has something to do with that
so just in case will put it here:
View 2 Replies
View Related
May 1, 2008
If I ask in a macro to open sheet containing a fax header, copy it, then how do I tell Vb to return to the original sheet to paste. There many sheets that require this from a button click on the sheet and instead of storing the graphic in all sheets I just want 1 copy so file closes faster. I would normally just name the original sheet, but this will change with every sheet.
View 13 Replies
View Related
Oct 2, 2008
I need to know how to select the sheet the user was on at the time they ran the macro. The macro has to select (because I'm not yet smart enough to avoid all the selecting...) cells on other sheets, but I want to return to the sheet they started from at the end of the macro.
Unfortunately, they create these sheets themselves, and I have no idea what they will be named, or where they will be, or what their code numbers will be. They could delete them at any time and rearrange.
Sorry if this is a really dumb question. I suspect it is, but I can't find out how to fix it, and most people are smart enough to avoid the selects so I suspect it isn't an issue for them.
View 9 Replies
View Related
