Create Copy Of Active Sheet & Convert Original To Values Only
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Create Copy Of Workbook Without Closing Original
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 Replies!
View Related
Copy Sheet To Same Workbook Without Overwriting Original
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 Replies!
View Related
Search To Find Matching Cells And Copy/transpose Adjacent Data To Original Sheet?
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 Replies!
View Related
Create Macro To Chart Data With Location As Object In Active Sheet
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
View Replies!
View Related
Macro Copy Of The Active Sheet And Then Rename The New Copied Sheet
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("O4").Copy ActiveSheet.Range("B3").PasteSpecial Paste:=xlPasteValues End Sub
View Replies!
View Related
Copy/Paste From Active Sheet To Another Sheet
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...
View Replies!
View Related
Copy Sheet & Create New Monthly Sheet From Present Sheet
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 Replies!
View Related
Automatically Re-name Sheet To Cell Value & Create Copy Of Hidden Sheet
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 Replies!
View Related
Save Active Sheet As New Workbook, Formats & Values Only
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 Replies!
View Related
Copy Range Of Cells To Active Row In Second Sheet
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 Replies!
View Related
Convert Sheet In Another Workbook To Values Only
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 PvtDest.Cells.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False PvtDest.cells.copy works fine PvtDest.Cells.select 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 Replies!
View Related
Create Copy Of Sheet For Each Name In List
The following URL has a great solution to this problem, I was unsuccessful in implementing it. Excel VBA: Create Worksheets for Each Item in an Excel Table of Data Need A macro to copy a pre-set sheet, we can call it a "template sheet", for each name in a pre-set range, a list. This list will have empty cells and names from F9 to F190. For each name i need it to copy the template sheet and place the sheet name within the new sheet. Should a user delete a sheet, activating the macro again should re-create the missing sheet and not just crash (was told there could be an issue).
View Replies!
View Related
Create Copy Of Sheet & Name From List
I am trying to create a copy of the sheet "template" using a list of department numbers from the sheet "list". The following code works sometime but other times it stops without copying a sheet for all the names in the list. Sub CreateNewSheets() Dim NewSht As Worksheet, Crow As Integer, NewName As String Dim c As Range, wks As Worksheet, TempName As String, BaseName As String Application. ScreenUpdating = False Set wks = Sheets("List") 'list of department numbers to be copied for a sheet.
View Replies!
View Related
Find Method To Search For The Active And Non Active Values
I have a range of amounts in Sheet 1 from F7:Q13 and im using the find method to search for the active and non active values in the cell. Which means that if there's a value in the cell it will transfer the value in Sheet 2, if nothing is found in the cell the cells in Sheet 2 will return as nothing or null. I think the problem lies on the FindWhat variable. Im getting a compiled error which im not sure what is it. I've attached the spreadsheet so you get a better idea of the problem that i encountered.
View Replies!
View Related
Create New Sheet For Each Group & Copy Needed Data To Each
I have just inherited an Excel spreadhseet that displays job titles and what types of documents each one should be trained on. What I am trying to achieve is a formula, or something to that sort, that will look at each job individually and tell me only those documents that they need to be trained on and list it on a seperate sheet. If you look at the attached document you will see that the first 2 columns contain a document number and description. Across the top you see each of our job descriptions. In the middle you see an X that indicates that person should be trained on that document. So once more, I would like to take each Job Description (seperately) and list only the documents that have an "x" in their column on another sheet. I already have the sheets created, I just need to know if there is some kind of formula or macro that can do this for me.
View Replies!
View Related
Vb Return To Original Sheet To Paste
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 Replies!
View Related
How To Return To Original Sheet The Name Varies
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 Replies!
View Related
Return To Original Sheet After Print Array
The code prints a series of sheets. Afterwards these sheets are "group"ed together. The code can be accessed from 2 different sheets. I need to figure out how to get rid of the grouping and return to the original sheet. Private Sub CommandButton1_Click() Sheets( Array("Pipe", "Pipe Flow")).PrintOut UserForm1.Hide End Sub
View Replies!
View Related
Sort Unique, Then Copy From Original List
I have a CSV spreadsheet full of data: many rows and many columns. I want to sort based on values in some column. Then, I want to divide my large worksheet into smaller worksheets based on these sorted column values. For instance, if column 4 was 20,000 items long and contained 6 unique values I would want to create 6 smaller spreadsheets. Each spreadsheet would still have a column 4. Now, however, all the column 4 values would be the same for a particular spreadsheet. It would be nice to have a pop-up window query me for which column to use to divide the data with, instead of hardcoding a value in (like 4). I am trying to crank this CSV file through MATLAB and it is just too big. My PC has 1GB memory and it still craps out. I am guessing that a dozen or so smaller files will be easier for MATLAB to digest. Any help is appreciated! For the record, I am doing the following to sort a column, then copy it to a scratch worksheet and then determine all the unique values. I have hardcoded in column lengths and would really like to know how to make these variable expressions: Sub divide_and_conquer() Dim lngSheet As Long Dim strName As String Range("A1:HR778").Sort Key1:=Range("D2"), Order1:=xlAscending, Key2:= _ Range("B2"), Order2:=xlAscending, Key3:=Range("E2"), Order3:=xlAscending _ , Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _.............
View Replies!
View Related
How To Copy A Worksheet, WITHOUT Linking It To The Original Workbook?
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 Replies!
View Related
Save Workbook Copy To Same Path As Original
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 Replies!
View Related
Extraction Sample Of Emails From A Sheet Proportionate To The Original
I have been set a task to do and I wonder if you could point me in the right direction. Task - extract 2000 emails from a 6000 email database The 2000 emails have to be proportionate to the original database. e.g. The main database has the emails plus town and employee size ranges Column A - Emails Column B - Town Column C - Employees So if Column B states that 50% of the entire database is from one town, then my extracted emails must also have half from that town (1000). Also there are around 5 employee ranges and so they need to also be proportionate to those percentages too in the final extraction.
View Replies!
View Related
Macro To Save Sheet With Same Page Breaks As Original
I am trying to sort a macro out to save my sheet the same as the original when the button is clicked. When i try it from one location it is fine but from another location i get a print error, the error says prit quality is not right, when i debug it highlights: Print Quality = 600 I have tried changing it but keep getting the same error. Their maybe an easier way to do this but not being very good on VB i dont know what to do. This file is on a server and will be accessed from different locations.
View Replies!
View Related
Save CSV As New Copy & Delete Original
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 Replies!
View Related
Macro To Copy Formula While Keeping Original Cell References
I'm trying to create a VBA macro that will allow me to copy a formula from one sheet to another whilst keeping all the original references. E.g. If the formula on Sheet1 is: = sum(A1:B6) then the copied formula on Sheet2 would read =sum(Sheet1!A1:Sheet1!B6) You can do this by cuting the cell, but I don't want to do this, I want to leave the original cell unchanged. I'm sure there is some simple VBA code to do this, but I can't seem to figure it out.
View Replies!
View Related
How To Copy Certain Values For A Cell To Another Sheet
I would like to be able to copy the values of certain cells to another worksheet if a certain condition is met. In return - I would also like to delete the information that was copy to the new worksheet if the condition has changed. I realize I can just filter out the data to get the information that I need but, I am creating this spreadsheet for several sales people to use and some are not to diverse on excel.
View Replies!
View Related
Adjacently Copy Values To A New Sheet
a code that will allow me to copy values from one sheet and adjancently paste them into another sheet? I would like to have theses values be pasted right under the next available blank line. Specifically, I would like a code that will select a particular range and pasteonly the cells with values to the Sheet1 on the next available blank line. And select another range and adjacently paste only the cells with values to the Sheet1 on the next available line, and so on.
View Replies!
View Related
Concatenation-copy Values To Another Sheet
I have a huge list that i need to copy over to another workbook but have a problem. the data i have is stored on 2 separate columns, A & B. I need both values to be combined into one cell and separated by a '/'. The CONCATENATE functi0n works perfectly, but when i copy these values across to the other sheet, of course i loose these becasue the original look up values for concatenation are elsewhere. How can i copy these concatenated values across to my other sheet?
View Replies!
View Related
Copy Trendline Values To New Sheet
I need VBA to copy the values from a treadline output box on a chart to a new sheet. The macro recorder produces the code below, but I need to make the 5th line generic so that the code will apply to any workbook. The code below will not run without the "Windows("Test.xls").Activate" statement, which restricts the code to a workbook of a specifc name (Test.xls). How can I make this run for any workbook name? Sub CopyTreadlineData() Sheets("A").Select ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select ActiveWindow.Visible = False Windows("Test.xls").Activate Sheets("Beta").Select Range("B15").Select ActiveSheet.Paste End Sub
View Replies!
View Related
Create Hyperlink In Active Cell
I'm running Excel 2003 and I'm looking for assistance with programmatically creating hyperlinks. I have to update a Shared Workbook on a weekly basis. The cells in column D contain a catalog id and I need to create a hyperlink that includes this id in the url. What I want to do is insert a new row, enter the catalog id and then create a hyperlink that contains the id. Additionally, the text to display has to be the 8 digit id. Here is an example of the hyperlink.
View Replies!
View Related
Use Active Cell To Create Hyperlink
I am trying to use an active cell (i.e. "Sheet2!E7") which always changes, to create a hyperlink on a different sheet to that particular cell. What is happening is I have a master list of current open POs. On a separate sheet I have the PO listed in greater detail. I want the PO number on my master list to have a hyperlink that brings you to my detailed PO on another sheet. Simply, I need a way to turn my active cell into text (not the contents, but the cell itself).
View Replies!
View Related
Copy Values From Multiple Worksheets To Summary Sheet
I have attempted to use a table of contents macro and then Hlookups to pull corresponding data from each worksheet, but haven't had success Issue: Excel workbook contains 50+ worksheets formatted the same, with data located in the same cells. Worksheets are constantly added to the workbook so the formula needs to scan the entire workbook How the data is organized: Subject headings are as follows: Cell A1 is "Loan ID"; A2 is labeled "Deal Name"; A3 is "Property Name"; A6 is "Loan Amount"; and E4 is "Asset Manager". Cells B1,B2, B3, B6, and F4 contain the corresponding data. Goal: I would like to automatically pull all of this information onto a summary page (much like a table of contents, but with the subject headings running across the top of the page) and the text data running down the page. I would also like to be able to click on the property name and have it direct me to the corresponding tab<br> <br> I have attached an example of what I am looking for, see "summary" tab for end result and other tabs as make-up of the data.
View Replies!
View Related
Copy To Another Sheet, Rename And Paste Special Values
i would like to copy a sheet to another sheet, rename, copy and paste special values. but after the sheet is copied to another, the macro stops working...? Sheets("Proposal").Copy After:=Sheets("Proposal") 'rename... ActiveSheet. Name = "ProposalEmail" ActiveSheet.Copy ActiveSheet.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _ :=False, Transpose:=False
View Replies!
View Related
Error # 429 Active X Can't Create The Object
I am having trouble with the following code... I get error # 429 Active X can't create the object... Dim blnIOpened As Boolean Err.Number = 0 On Error GoTo failed blnIOpened = False Set MyXL = GetObject(, Excel.Application) failed: If Err.Number = 429 Then Set MyXL = CreateObject(Excel.Application) blnIOpened = True End If If Err.Number 429 Then MsgBox Err.Number End If
View Replies!
View Related
Macro: Create Directory Based On Active Workbook And Save To It
Read “My Documents” Path And Use Result Problem: Note: Typical user OS will be Windows XP Pro / Win 2K Excel version : 97 / 2002 / 2003 1. Corporate network security settings will only allow directory/subdirectory creation in the “My Documents” section of customers individual computers. 2. Per customer request, VBA application needs to save extracted files for future use. 3. I can specify an initial “My Documents” subdirectory be made and the VBA application file be loaded/copied into that location – i.e. – “My DocumentsCat”. 4. When VBA application is opened from that specified directory, (first time), the application needs to make an additional subdirectory tree to save future files. I can read the opened from location via VBA with the following: Dim filepath As String filepath = ThisWorkbook.Path As an example – this code would produce a string definition of “filepath” – such as the following:............................
View Replies!
View Related
Keep Hidden Sheet Made Visible As Active Sheet
I have a button on a sheet that runs a macro to unhide another sheet. That works, but I want the sheet made visible to remain forward. Instead, the button unhides the sheet and the sheet the button is on comes forward again. I am unable to figure how to keep the sheet made visible forward. Here is the macro 'as recorded'. Sub UnhideSheet1() Sheet2.Visible = True End Sub
View Replies!
View Related
Find Related Data From Another Sheet & Copy Values From Left
Im going to try to make this as clear as possible. I cant use my actual data because it wouldnt make any sense to anyone so Ive made up an example problem. Here goes... Lets say in Sheet 1 I have two descending columns of data. Column A is MODEL of Vehicle (Civic for example). Column B is vehicle identification number (xxx for example). Sheet two has 4 columns of data, but only one is really required for this example. Cell A1 is the MAKE of vehicle (Honda for example). Directly below that in Cell A2 is the MODEL of the vehicle (Civic). There are then a few rows of empty space until it gets to the next vehicle MAKE and MODEL. So in Sheet1 there is a long list of MAKE's in ColumnA and VIN's in ColumnB. Sheet2 Has a long list of MAKE's and MODEL's in ColumnA and random data in other columns. What I want to do is assemble a Macro to start in Sheet1-A2, read the MODEL then copy the corresponding Vehicle Identification Number in B2. I then want it to go to Sheet2-A2 and start searching downward until it comes across a matching MODEL. Once it finds the match I want it to step downward 2 cells and paste the Vehicle Identification Number. Then return to Sheet1-A3, and repeat the process until EOF.
View Replies!
View Related
Copy Rows Based On Criteria, Paste As Values To New Sheet & Sort
I have spent a few days searching through forums but cannot find examples that i have been able to successfully adapt ( because they are to complicated for my limited knowledge). I have a workbook with 5 sheets, sheet1 (current), and sheet 4 (archive) are the important ones. I need a macro to A) copy rows from "current" to "archive" ( to the 1st empty row) if column S of "current" contains "Closed" ( the word is generated by formula). B) The paste needs to paste special values and number formats ( want to lose formula but not conditional formatting). C) data sort "archives" based on col A - this puts the newly archived data into correct sequence.Data sort lowest number 1st D) delete the copied rows from "current". e) give me a count of how many rows it deleted, (I have a macro to insert rows so will run that manually to recreate the correct number of emtpy rows (with formula and formatting) to bring the current sheet back to usual size. I tried modifying a macro by RPaulson (based on cells on one sheet to cells on another), to work with entire rows but couldn't get it to work. Found that paste special uses PasteSpecial xlPasteValues, but , and thats about it.
View Replies!
View Related
Convert Absolute Values To Negative Values Based On Criteria
is it possible to write a macro that will convert absolute values into negative based on criteria? Data gets downloaded from in house system which comes out as all absolute values, now i need the macro to covert absolute values to negative if the amount is either "Our Reciepts" or "Our Delivery" Below is a small sample of data, real data is around 100 rows Col DCol EOur Receipts1231Our Delivery1231Delivery Settlement1323
View Replies!
View Related
|
TRACKER
