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


ADVERTISEMENT

Save The Tamplate In Original Place

Aug 27, 2008

I have VBA program that collect the data from database and make a report.(I will mention I open the browser to look for database )
Here is a problem:
My original file is " Daily Report.xlt(template) " .At the begin when I open this file ,the file name appear as a " Daily Report1 "...This is not big deal
When I run macros I need to keep some info on the original file.
After I done, I have following code to save as " Daily Report.xlt " .
but the actual problem is ;the code save this file on the database path that I don't want it.Actually I want to overwrite on original file to keep that file up to date.Here is code
ActiveWorkbook.SaveAs Filename:= _
"Daily Report.XLT", _
FileFormat:=xlTemplate, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Note:I know I can add the path in front of the file name and save it in the that directory .Different customer different path I can not use fix directory.

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

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

Rows Copy Over To A Different Sheet And Place The Next Available Row

Apr 4, 2008

I have is 3 seperate jobs listed, what i want to do is have each one of these rows copy over to a different sheet, and place the next available row. What I would like to drive this is the number in column "n", for example, "n3" is 1, so I would like that row to copy to the sheet named "1", and entered in the next available row under any previous lines that have been copied over.

******** ******************** ************************************************************************>Microsoft Excel - BETA SCHEDULE.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCDEFGHIJKLMNOP1Date EnteredTime Ent'dCUSTOMERJOB #LINE #MATERIALMATERIALMAT'L   QTYPAPERPAPER QTYFinish QNTYSIZEMach.RUN DATEDue Date233/318:58 AMCUST. A2585271HSS5260 15 SH  15 / 1530X65 .02013/313/3143/318:58 AMCUST. B2585241PC 8256 7 FT   3 / 624X26 .01023/313/3153/318:58 AMCUST. C2585161TFS1010 3178 FT  615 / 61532X61.5 .00833/314/1MASTER [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

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

Macro To Copy And Place Data To Specific Place

Feb 22, 2007

I am after a macro to do the following, my visual basic skills are very limited (non existant):- Look at the date in cell A1 on Sheet 'Live Report' and err 'remember it' Copy a range of cells from A3 to A10 on 'Live Report' Go to sheet 'Monthly Summary' and find the date that had been remembered previously (this date will be in column A on 'Monthly Summary' which will probably be a mixture of values and formulas). After the date has been found paste special and transpose the 'values only' copied range from 'Live Report' (A3 to A10) in column B on 'Monthly Summary' next to the date that has been found in Column A.

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

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

Fill In The 3 Criteria Fields, Calculate The Result, Copy The Result, And Place It Into The Correct Place On The Table

Oct 19, 2006

I have a macro that imputs data from an external database and puts it into a temporary worksheet. This data has 3 columns (ID, Date, Amount). I am then making another sheet which has X number of tables (one for each ID), with the years being the column headings, and months being the row headings. ie.

| ID X
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | $100 | $250 | $300 |
+------+------+------+------+--->
| Feb | $200 | $300 | $200 |
+------+------+------+------+--->
| Mar | $300 | $250 | $100 |
+------+------+------+------+--->
| Sum | $600 | $800 | $600 |
| +------+------+------+--->

| ID Y
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | $100 | $250 | $300 |
+------+------+------+------+--->
| Feb | $200 | $300 | $200 |
+------+------+------+------+--->
| Mar | $300 | $250 | $100 |
+------+------+------+------+--->
| Sum | $600 | $800 | $600 |
| +------+------+------+--->

Currently I have a few hidden fields for the DSUM Criteria. I start making the tables. And then filling in table based off of the month and year. Doing so I need 3 criteria:
>= First Day of the Month
<= Last Day of the Month
= ID #

The problem is it takes Excel too long to fill in the 3 criteria fields, calculate the result, copy the result, and place it into the correct place on the table. Is there another way to get this data into the correct tables faster? Instead of using DSUM?

View 8 Replies View Related

Running Userform From More Than One Sheet And Return To Original Sheet

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

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

Highlight Entire Row When Cell Selected Without Losing Original Formats And Color Of Original Row

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

Save CSV As New Copy & Delete Original

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

Cut And Paste (row On The Original Sheet Shrinked)

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

Vb Return To Original Sheet To Paste

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

How To Return To Original Sheet The Name Varies

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

Loop Through Each Sheet And Place Value Of Sum On Its Respective Sheet In Same Position

Nov 1, 2013

How can i get this code to run through each sheet and place the value of the sum on its respective sheet in the same positon .....

Sub maths()
lr = Cells(Rows.Count, "E").End(xlUp).Row
Range("E" & lr + 1).Select
ActiveCell.Formula = Application.WorksheetFunction.Sum(Range("E2:E" & lr))
Selection.NumberFormat = "[h]:mm:ss"
End Sub

View 1 Replies View Related

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

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

Sort Unique, Then Copy From Original List

Mar 1, 2004

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

Save Workbook Copy To Same Path As Original

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

Save Copy Of Workbook Without Closing Original

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

Return To Original Sheet After Print Array

Jun 14, 2006

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

Extraction Sample Of Emails From A Sheet Proportionate To The Original

Nov 10, 2009

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

Macro To Save Sheet With Same Page Breaks As Original

May 14, 2009

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

Adding Columns And Copy Function Without Messing Up Original Code?

Dec 19, 2013

I have attached a copy spread sheet. This has been working great but i have been asked to add some items and i dont want to screw up the working functions.

I now want to add incert two columns so the actual costs of a first and second service can be added to the contracts and used contracts sheets, this information allong with data from a,b,e,g h needs to be copied over into a new sheet (report sheet) which will have the budgeeds costs in column i,j starting from row 3 and finding the last row so as not to overtype so that a report can be sent showing profit/loss.

The costs will be put in at diferent times so it only need to up date a changed cell

If i just add columns will this effect the auto archive coding? Could the data be copied over to the new sheet using the original code on start up? (so customer etc copied then as cost are put in these would be added to the respective rows on each start up.

I have had to remove some of the sheets to up load this so my not work correctly, but you can see the funtion in the code

View 3 Replies View Related

Macro To Copy Formula While Keeping Original Cell References

Feb 17, 2009

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

Copy Selected Cells And Paste As Formula With Original Cell Reference

Nov 20, 2012

Wanted to know if there is a macro that can copy the selection of cells and paste it as a formula with original cell refernce.

For Example :

Copy Selection Cells - Say Cells A1 B1 & C1
and Paste It as formula In Cell D1 as =A1+B1+C1

View 7 Replies View Related

Copy Worksheet And Place It At The End?

Aug 6, 2012

I'm trying to copy a worksheet and place it at the end. I will rename it afterwards. I'm using the following code but it doesn't work.

Private Sub Copy_sheet()
Dim wbk As Workbook
Dim wsh As Worksheet
Set wbk = ActiveWorkbook
wbk.Worksheets("Template").copy After:=wbk.Worksheets(wbk.Worksheets.Count)
Set wsh = wbk.Worksheets(wbk.Worksheets.Count)
wsh.Name = "New Sheet"
End Sub

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved