Create Copy Of Active Sheet & Convert Original To Values Only

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


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

Looking Up Value In Second Sheet And Returning Multiple Values To Original Sheet

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



And the following a sample of sheet 2 :



[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

How To Copy Sheet And Place After Original

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

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

Convert Cell Values In Active Range To Text Format

Jul 15, 2014

How can I convert all cell values in active range to text format without losing the actual value.

For Ex.

0123 will remain 0123
07/12/2014 will remain 07/1/2014
3453 will remain 3453

regardless of there previous format. Currently I am doing this manually for every column before creating a load file for SQL database.

View 7 Replies View Related

Excel 2007 :: Alternative To Convert Formulas To Values That Keeps Hyperlinks Active?

Oct 16, 2013

I'm using Excel 2007 and I'm a VBA novice.

Problem: The macro will be assigned to a command button and will be used by laypersons when they finish filling in data on a worksheet in Workbook 1. The sheet contains maybe 30 columns and 50 rows with a mix of fixed values and values generated by Vlookup and Indirect formulas. I need to copy the sheet from Workbook 1 to Workbook 2. Workbook 2 will be for archival purposes so I want to convert all formulas to fixed values. The catch is dealing with some cells that contain hyperlinks to PDF files...

Current Solution: I currently do this with a macro that moves/copies the sheet from Workbook 1 to Workbook 2, it then selects all cells in the new sheet in Workbook 2, copies all cells, then pastes-special "as values" to the exact same cell locations. This works great for me since the cell formatting and data in the sheet are VERY irregular and I have merged cells all over the place. This method keeps the exact formatting I need maintain:

ActiveSheet.Copy After:=Workbooks("Workbook2.xlsx").Sheets(1)
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

The problem is that a few of the cells have hyperlinks with "friendly names" and I lose the hyperlinks when I convert to values. The hyperlinks are not inserted directly, they are created by a formula, =HYPERLINK("N:Filepath"&C16&".PDF", "Click_For_PDF"), and the row and column that contains the hyper link will vary for each sheet I want to migrate from Workbook 1 to Workbook 2 using this macro. I want to keep the hyperlink active with the clickable friendly name in Workbook 2.

Possible Solution: I'm open to all types of solutions, but is there a way to essentially use my existing macro but AFTER converting to values with paste-special, go back to the original sheet in Workbook 1 that still contains formulas (or maybe a temporary duplicate sheet I migrate to Workbook 2?), search for all cells with a "value" of "Click_For_PDF", copy ONLY those cells and paste (normal) into the corresponding cell locations in the sheet in Workbook 2 that now contains fixed values? ALL of my hyperlinks have the friendly name "Click_For_PDF" so it should be an easy way to identify the hyperlink cells. The cell location of the hyperlink copied in Workbook 1 needs to carry over to Workbook 2 and I said before, the row and col vary with every sheet I want to archive with this macro.

View 9 Replies View Related

Search To Find Matching Cells And Copy/transpose Adjacent Data To Original Sheet?

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

Create Macro To Chart Data With Location As Object In Active Sheet

Oct 11, 2006

I have been trying to create a macro in excel to chart a selection of data and to output the chart on the active sheet where the data was taken (as opposed to a named sheet). So basically, I have about 300 worksheets with data, and I would like to have a button on each page that automatically charts that data when clicked, and outputs the chart to the page where the macro was clicked. However, I have not been able to figure out a relative reference that will allow me to make the LocationasObject reference simply the ActiveSheet as opposed to a specifically named sheet. See my code below, which references an output to a worksheet called "Charts". Right now, all of my charts are outputting to the sheet called "Charts", as opposed to the active sheet.

Sub ConsDiscChart()
ActiveCell.Offset(29, 11).Range("A1").Select
ActiveCell.Offset(0, 1).Range("A1:B1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Offset(0, -1).Range("A1:C24").Select
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Where:=xlLocationAsObject, Name:="Charts"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

View 5 Replies View Related

Macro Copy Of The Active Sheet And Then Rename The New Copied Sheet

Jul 30, 2009

I'm trying to create a copy of the active sheet and then rename the new copied sheet to what's in cell O4, which is a formula (see below) and then paste value cell O4 in B3 of the copied sheet. However, when I run this macro it doesn't seem to like the second line where I am renaming the sheet (run time error '1004').

"O4" =DATE(YEAR($B$3),MONTH($B$3)+1,DAY($B$3))

Sub NewMonth()

ActiveSheet.Copy Before:=Sheets(Sheets.Count)
ActiveSheet.Name = Range("O4").Value
ActiveSheet.Range("B3").PasteSpecial Paste:=xlPasteValues
End Sub

View 9 Replies View Related

Copy Cell From Specific Sheet & Paste To Active Sheet

Mar 31, 2008

I would like a macro that will go to a fixed sheet, copy the format, go back to the previous sheet and paste the format. My problems arise going back to the previously activated sheet rather than just a fixed sheet.

View 2 Replies View Related

Copy Ranges From Active Sheet And Post To New Sheet

Aug 20, 2014

I have a workbook that I want to copy two separate ranges from and then post to two different sheets. Range one on the active sheet from A4:K(no of rows will vary) to sheet Savoury Year, and range two A64:K(no of rows will vary) to Site Services Year. When pasting I want to paste it to the next empty row down.

Copy of workbook attached : Weekly Accounts.xls‎

View 4 Replies View Related

Delete Rows That Contains Zero And Copy Active Sheet To New Sheet

Oct 2, 2009

I want DELETE the rows that contains 0 (zero or -) in column F (SALDO BRUTO), when I click the Button (Clear). And How I can copy from this sheet to a new sheet in this workbook, but the names of new sheet automatic rename to next date or 2, and next when I click the Button "Copy to New Sheet".

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

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

Copy Active Sheet And Another Sheet To A New Workbook

Jun 10, 2013

I am wanting to create a macro for excel that when run it it will copy the active worksheet and worksheet named "Timesheet" and copy them to a new Excel workbook named the same as the original file + "JobBrief".

View 3 Replies View Related

Copy/Paste From Active Sheet To Another Sheet

Sep 11, 2006

I want to copy data from the current sheet, and past it in the "montly" sheet.

'i and j were defined above, with a Case statement
Range(" Ai:Cj").Select
' I want the upper left cell of the pasted area to be A11

I get an error saying that I should select one cell and then paste. But I thought that that is what I did...

View 2 Replies View Related

Need To Create A Button That Can Copy Cells, Create A New Sheet And Then Paste There

Jan 14, 2009

So I've got Sheet 1 with say


I would like to create a button that can create a new sheet and paste A1 to C3 at the same location on the new sheet

and I need this to create a new sheet and do that everytime the button is pressed.....

View 11 Replies View Related

Copy Active Range To Another Sheet?

May 9, 2014

Looking for a macro that will copy a highlighted range to another sheet. I can get an row to move but not 3 rows.

View 5 Replies View Related

Copy Range From Active Row To New Sheet?

Jun 3, 2014

My VB looks like this at the moment


What I want to do is when a button is clicked, the range of data in columns 2-9 of the active row gets copied. Then a new sheet is created and the data is then pasted to a range in that new sheet.

The VB I have done so far works fine but its specific to a single range - it doesnt take account of the active row.

View 7 Replies View Related

Copy One Sheet From Active WB To Another WB For Update

Jun 30, 2014

I have 2 excelfiles. The macro should copy the Sheet "SDH" from file "New file.xlsm" and paste it to the file "FileforUpdate.xlsm".

"FileforUpdate.xlsm" has already a Sheet called "SDH". This have to be deleted before past the sheet "SDH" from "New file.xlsm" .

After Copy, delete and past it should save the "FileforUpdate.xlsm" in it's folder and close the WB.

View 9 Replies View Related

Copy Range Of Cells To Active Row In Second Sheet

Sep 6, 2006

Sheet 1 has data entered into it, it is then printed out as a jobsheet, saved and the data cleared. There are certain fields on this sheet that are eventually manually replicated onto sheet 2. The row in which they must go on sheet 2 will always be the 'activerow' on that sheet from a previous operation. It would make life so much easier and save lots of time if I could incorporate copying cells C10,C12,K8,K12,M2,C27 and C29 from sheet 1 to respective cells H,I,J,M,N,R,S of the active row on sheet 2 before I carry out the clear data process.

View 6 Replies View Related

Save Active Sheet As New Workbook, Formats & Values Only

Mar 26, 2008

I would like to link a macro to a button on the active sheet, that saves the active sheet In a New Workbook, with Format And Values Only. Preferably saved in the same location as the origonal workbook. This is my main goal.

If possible I would like the new workbook to be named from a chosen cell (E19) on the active sheet with date added.

View 8 Replies View Related

Use ADO To Copy Data From Closed Book To Active Sheet

Mar 6, 2012

I have managed to use ADO to copy data from a closed book to my active sheet. Problem : it copies only the text, and not the dates or figures...


Sub TestReadDataFromWorkbook()
' fills data from a closed workbook in at the active cell.
GetDataFromClosedWorkbook "H:P&LYE TempDiv P&LP&L Report 020312.xls", "A1:Z1000", Range("A1"), False
End Sub


Sub GetDataFromClosedWorkbook(SourceFile As String, SourceRange As String, _
TargetRange As Range, IncludeFieldNames As Boolean)
'GetDataFromClosedWorkbook "C:FolderNameWorkbookName.xls", "A1:B21", ActiveCell, False
'GetDataFromClosedWorkbook "C:FolderNameWorkbookName.xls", "MyDataRange", Range("B3"), True


View 4 Replies View Related

Copy Sheet & Create New Monthly Sheet From Present Sheet

Jan 12, 2010

I want to create a macro button that can create copy, insert, paste and rename the new sheet in next month's name, like if the active sheet's name is January, I want to copy the whole sheet of January, insert new sheet, paste the new sheet and rename the new sheet to next month like February?

Also rename the new sheet (February) cell B3 the same as new sheet's name (February)

So if month of February is near end, the macro button in February will create the same way as Jan did which means the next sheet will be named March and so on.

View 9 Replies View Related

Macro Run Copy Text From Active Cell And Find That Value In Another Sheet

Dec 16, 2008

I would like to create macro, where it would on macro run copy text from active cell and find that value in another sheet (in column H) and select that cell.

What I did is this:

View 5 Replies View Related

Macro That Copy Address In Cell And Send Active Sheet To That Email ID

Apr 15, 2014

Is it possible for a Macro to read a cell say D:11 and copy that email address and send the active sheet to that email?

View 9 Replies View Related

Automatically Re-name Sheet To Cell Value & Create Copy Of Hidden Sheet

Aug 10, 2008

I have a workbook with a hidden sheet ("Template") and a visible sheet("New Job"). I need code so when cell F1 in "New Job" is populated:

1-the sheet is renamed to the value of F1,
2-a new tab is made (a carbon copy of the hidden sheet "Template")
3-the new tab is named "New Tab" and marked as unhidden.

Public Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Name = Range("F1").Value
End Sub

Function WorksheetExists(SheetName As String, _
Optional WhichBook As Workbook) As Boolean
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)
End Function..................

View 2 Replies View Related

Convert Sheet In Another Workbook To Values Only

Mar 20, 2008

I am trying to be a good programmer and not do a whole bunch of activate workbooks and worksheets. So I am 99% complete with my subroutine and stuck on 3 lines. I need to copy my entire pivot table from PvtDest (which is Superdatabase.xls sheets f2 pivot)

Set PvtDest = Workbooks(SSRname).Worksheets(CarrPivot)

PvtDest. Cells.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

PvtDest.cells.copy works fine Fails due to (select method.range class failed

finally Selection.Paste special is supposed to paste on PvtDest but I have not been able to accomplish this.

I think I have seen copy paste special in the same command line is that the solution

View 9 Replies View Related

Copyrights 2005-15, All rights reserved