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.
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.
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
And the following a sample of sheet 2 :
Nsvcode Item Division Speciality Qty
12 OLANZAPINE CLINICAL CS 10
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.
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
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
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:
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.
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!
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 Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlDown).Select ActiveCell.Offset(0, 1).Range("A1:B1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Offset(0, -1).Range("A1:C24").Select Charts.Add 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
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').
ActiveSheet.Copy Before:=Sheets(Sheets.Count) ActiveSheet.Name = Range("O4").Value ActiveSheet.Range("O4").Copy ActiveSheet.Range("B3").PasteSpecial Paste:=xlPasteValues End Sub
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.
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.
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".
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.
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".
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 Selection.Copy Sheets("monthly").Select ' I want the upper left cell of the pasted area to be A11 Range("A11").Select ActiveSheet.Paste
I get an error saying that I should select one cell and then paste. But I thought that that is what I did...
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.
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.
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
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.
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..................
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)