Copy Worksheet Need To Make A 2nd One As Well

Dec 5, 2006

I need to make an identical copy with the name I choose from the input box with (wk2).
For example:
If I enter Tommy in the input box, it would make 2 worksheets:


Dim NewSheetName As String
Sheets("NewPerson").Copy After:=Worksheets(Worksheets.Count)
NewSheetName = InputBox("What would you like to name this sheet?")

On Error GoTo Err_Trap
ActiveWindow.ActiveSheet.Name = NewSheetName

Err_Trap:
If Err.Number = 1004 Then
Application.DisplayAlerts = False
Sheets("NewPerson (2)").Delete
Application.DisplayAlerts = True
MsgBox ("A new sheet was not created." & Chr(10) & _
"Please choose a different name."), vbInformation, "Sheet Creation Error"
Exit Sub
End If

View 9 Replies


ADVERTISEMENT

How To Make A Dynamic Copy Of A Worksheet

May 16, 2013

I'm trying to make two identical worksheets in a workbook. I would like the second sheet reflect all the changes made in the first one. I thought I would simply use = but it doesn't work correctly if I add or delete rows. If I add a row between row 1 and 2 in the first sheet the formula in row 2 in the second worksheet changes from =sheet1!A2 to =sheet1!A3. The result is the same if I use absolute references which suprised me. I want the formula in row 2 to stay =sheet1!A2 whatever happens on the first sheet.

I don't care about formatting, just data are important.

The reason I want the same data on two worksheets is that the second sheet will contain some more data that should not be visible to everyone. I want to protect the second worksheet and require a password to unhide it.

View 2 Replies View Related

Daily Worksheet Macro - Make Copy Of Template For Each Day Of Month?

Dec 22, 2011

We have a workbook that we create each month that has one worksheet per day of the month, labeled 12_01_2011, 12_02_2011, 12_13_2011...etc. The pages are an empty template with formulas and fields in place that we simply copy and paste the results of an SQL query into. Presently, we are copying the page manually several times over, and then manually renaming the pages with the new dates for the upcoming month.

So, here is my question. Macro that I might use that would:

1) Make a copy of the template for each day of the month.

2) Label each page in sequence with the dates for the upcoming month.

View 4 Replies View Related

Formatting Worksheet: Re-adjust The Worksheet To Make It More User Friendly Based On Number Of Rows In Current Sheet

Oct 14, 2008

I have a worksheet with 30,000 rows. But sometimes even if I have fewer records in this worksheet(lets say 1000) worksheet shows the same 30,000 rows.And its annoying when you try to navigate using vertical scroll bar. Is there any option to re-adjust the worksheet to make it more user friendly based on number of rows in current sheet.

View 2 Replies View Related

Copy Between Worksheets Doesn't Work (look For A Certain Value In Worksheet A And Copy That Row Of Data To Worksheet B)

Apr 13, 2009

look for a certain value in worksheet A and copy that row of data to Worksheet B.

However, it seems to be only copying the row in worksheet A and pasting it. Is there something that a noob VBA scripter has missed out?

PHP Private Sub GetInfo_Click()
    Dim r As Long, LastRow As Long, Status As Integer
    Dim Message As String, Title As String, Default As String, MyValue As String
    Application.ScreenUpdating = False
    
    MyValue = Range("A4").Value
    Workbooks("invoice.xls").Worksheets("A").Activate
    LastRow = Range("C65536").End(xlUp).Row
    For r = LastRow To 1 Step -1
        If Cells(r, 1).Value = MyValue Then
            Rows(r).EntireRow.Copy
            Workbooks("invoice.xls").Worksheets("B").Activate
            Rows("8").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
            Status = 1
            Workbooks("invoice.xls").Worksheets("A").Activate
            Rows(r).EntireRow.Delete
            
            Exit For
        End If
    Next r
    Application.ScreenUpdating = True 

View 2 Replies View Related

Worksheet Copy: Method 'Copy' Of Object 'Worksheet' Failed

Nov 22, 2006

I have written code that allows a user to copy, via a button, a certain sheet any number of times. They can choose to copy that sheet 5 times and then 10 times, etc.

When the sheet tries to be copied for the 17th time, it fails with the following message: "Run-time error '1004':
Method 'Copy' of object '_Worksheet' failed"

Here is the code that I have. The second line is the line that is failing.

Worksheets("RoedForm").Select
Worksheets("RoedForm").Copy After:=Worksheets(iCount)

This always fails on the 17th copy regardless of how many different combinations of copy sheets the user tries. There are several sheets before the sheet that is to be copied and I have deleted several of those to see what happens and it still fails on the 17th copy. I also have 1GB of memory, so I don't believe that memory is an issue.

View 3 Replies View Related

Make A1 Display Name Of Worksheet

Aug 31, 2009

I'm working on a project, and I need a cell on a worksheet to produce the name of the worksheet, such that if the worksheet changes, or is copied [Sheet1 renamed to Sheet(1), for example] the cell will automatically update [now displaying Sheet(1) in cell A1].

View 2 Replies View Related

Copy Worksheet In Workbook With All Formulas On New Worksheet Referencing Previous Worksheet

Apr 21, 2012

I have a workbook that contains 50 worksheets named 1-50. I need to add more worksheets. all the formulas in the worksheets always refers to the previous worksheet.

How can i make a copy of the worksheet named 50, name it 51 and have all the formulas in worksheet 51 refer back to worksheet 50?

View 1 Replies View Related

How To Make Worksheet Cover All Of Paper

Jan 17, 2013

CK Nursery and Finish Inventories.xlsxAttachment 207447

I would like the "Finish Summary by Age" tab worksheet to look like the "Nursery Master Sheet" and "Circle K 1 - CD Farms Nursery" tab worksheets. I'd like the "Finish Summary by Age" tab worksheet to cover the majority of the paper like worksheets on the other tabs.

View 1 Replies View Related

Drop Down Lists: How To Make Source From Different Worksheet

May 13, 2009

When creating a drop-down list using Validation, is there any way to make the source a different worksheet in the workbook? Right now when I click on Source and select my list, it will not allow me to go to another worksheet.

If I manually enter a reference to cells in another worksheet, such as 'PCP'!$A$2:$A$250 it only shows that cited reference, not the actual list.

View 3 Replies View Related

Make Rows Hide In One Worksheet Based On Value In Another?

Apr 30, 2012

I have a worksheet "Feature Segments" that has a value in cell B40 and C40, if the value in these is "Off (Default)" then I need rows 22 and 23 on Worksheet "Summary" to hide.

I have put this code on the features segment tab but it is not working.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Address = "B40" Then

[Code].....

View 3 Replies View Related

How To Make 2 Cells On Worksheet Be Required To Be Filled In Before Printing

Dec 19, 2011

We have a form that has a dropdown list of selections but many people forget choose the reasons. How can I make these required fields that HAVE to be filled in in order to print. These are internal forms, nothing on the web. I just need to find a way to make sure that there is a way to stop someone from printing if these 2 fields are not chosen.

View 5 Replies View Related

How To Run Countif Function On Worksheet To Make Table Of Raw Data

Jan 11, 2013

I am trying to run a countif function on another worksheet to make a table of raw data.

Look on sheet SC-01 in Column H for "No" and return the count...pretty simple

Code:

=COUNTIF('SC-01'!$H:$H,"No")

What I need it to do is determin the sheet name based on the string in the column A

Control Countif Function
SC-01
4
SC-02
SC-04
SC-05
SC-06

I have tried various combos of Indirect and concatenate, but I keep getting a ref error.

View 2 Replies View Related

Can You Make A Copy Of A Userform

Jan 11, 2007

I am simply trying to make a copy of a userform in my project.

Unless I am missing the obvious, how are you supposed to perform this operation ?

View 9 Replies View Related

Make Array Reference Cells On Another Worksheet Of Active Workbook

Feb 27, 2014

My company has complicated time sheets because we have several tasks that are billed differently to different people. Once a month we have to sit down and compile everything from several forms and so forth. I have created a worksheet that pulls all the numbers together so that they can simply be copied and pasted into our reports. The hope was to simply copy this worksheet into a time sheet workbook and it will pull out all of the correct numbers. Although all the time sheet workbooks are set up the same way, whenever I copy the file into another time sheet workbook the program keeps its references from the workbook it was in. Make sense? is there a formula that I can insert into an array to tell it to pull the information from the worksheet with the same name, but in the active workbook?

here is one of the equations I am working with:

{=TRANSPOSE('1st week'!A10:L48)}

so it would look something like this maybe

{=TRANSPOSE('[active workbook]1st week'!A10:L48)} but this doesn't work of course.

View 3 Replies View Related

Make Worksheet Calculate Event When Range Of Cells Change?

Feb 15, 2014

I have the following code that I would like to trigger when cells B26:U26 change to something other then 0 due to a formula? How was I make this happen?

[Code] .....

View 4 Replies View Related

Make A New Sheet And Copy A Column?

Jul 13, 2009

I need to write a macro that will create a new sheet called "Export Format" and it's A Column needs to be exactly the same as the A Column in another sheet called "Master". I'v been trying to look it up since I have never used VB before, but my boss needs this done soon so i thought I'd ask. This is just a small part of what i need to do but I think it will give me the start I need.

View 2 Replies View Related

Can A Workbook Make A Copy Of Itself Without Macros

Sep 10, 2008

I would like to place a command in a macro to have a workbook replicate itself WITHOUT replicating its macros. Is that possible? Is there a command? I am using Excel 2003.

View 9 Replies View Related

Find A Value In Column A And Then Make A Copy Of The Whole Row Right Below It

Sep 1, 2009

I have the text value "Ball, Red" somewhere in column A. I don't know which row it's in because it will never be in the same row twice. I need a macro to find this value, then, copy that whole row and "insert copied cells" right below that row and change the value in the newly created cell from "Ball, Red" to "Red, Ball".

I tried macro recorder to do this and had no luck...

Cells.Find(what:="Ball, Red", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("A21").Select
Selection.Insert Shift:=xlDown
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Red, Ball"
Range("A22").Select
That's is all there is to it on this one.

View 9 Replies View Related

Make Copy Of Master Then Rename From Cell?

Mar 7, 2014

What I'm looking to do is have a button that will be able to make a copy from my "MASTER" sheet cells A1:M48, then be placed at the end. The copied will then need to be renamed based off of the data in cell C4.

The following is the sample recorded macro:

VB:
Sub Copy_Rename()
'
' Copy_Rename Macro

[Code]....

View 5 Replies View Related

Copy Paste Sheet Name To Make List

Mar 2, 2009

I would like to create a list of all the sheet names I have in a given workbook. Is there a simple way to do this through VBA?

View 6 Replies View Related

Make Exact Copy With Only Values And Layout?

Nov 20, 2013

I have a file and i want to make an exact copy with only values and the layout.

I can do copy paste each sheet in the new file one by one. with first the values and then the layout.

But the file has lot of sheets so it would be great to find a faster way then do it one by one.

View 1 Replies View Related

Looping Macro (copy And Paste It To The End Of Row, And Make It Value)

Feb 27, 2010

I've tried to record macro, placed formula in row 4 only, copy and paste it to the end of row, and make it value. My database begin form column AM to BG.

Sub UPDATE_DATABASE()
'
' UPDATE_DATABASE Macro
' Macro recorded 2/22/2010 by FAUZAN
'
Sheets("database").Select
Range("AM4").Select
Selection.Copy
Range("AM5").Select.........

View 9 Replies View Related

Make New Copy Of Entire Workbook Macro

Mar 1, 2010

I've created an Excel 2007 workbook with about a dozen worksheets that are full of formulas and links to other workbooks, as well as conditional formatting that isn't backwardly compatible with Excel 2003. However, many people who want to see this workbook are still on Excel 2003. I need to recreate an Excel 2003 compatible workbook with the appearance and formatting of the original and have all the formulas and links converted to values. So, the final product will "look" just like the original without the conditional formatting, links or formulas. It will just have the look, colors and values displayed for all cells.

Essentially, I want a macro that will copy however many worksheets (1-n) are in an Excel 2007 workbook to a new Excel 2003 workbook. I'd like the worksheets to retain their label names as well.

View 9 Replies View Related

Save Copy Of Open Workbook And Make PDF For Archive

Apr 27, 2012

Code, couldn't make it work with VB HTML Maker. I'm new with VBA Basically my idea is when someone open default file, insert data, try to save file, macros save workbook with inserted data as another file (*.xlsx) and make pdf in another folder. Pdf part is working fine, but with second part just can't find right option.

Pdf part

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Dim Name As String
Dim i As Integer, j As Integer
Name = ThisWorkbook.Path & "PDF Archive" & ActiveSheet.Range("F6") & " Invoice " & ActiveSheet.Range("B11")

[Code]...

Excel part

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ActSheet As Worksheet
Dim ActBook As Workbook
Dim CurrentFile As String
Dim NewFileType As String
Dim NewFile As String

[Code]...

View 1 Replies View Related

Make Copy / Paste Values Macro More Efficient When Calculating

Feb 17, 2014

I got a macro to copy and paste values onto another tab within my worksheet. I have a lot of data and currently takes about 30 seconds to calculate and paste. Not sure if its an issue with my macro or with my computer (Mac - Excel 2011).

Here is an example of my macro:

Sub SimulateWeek()
If Range("AdvanceWeek").Value = "Week 1" Then
Range("Week1B").Copy
Sheets("Schedule - Results").Range("C2").PasteSpecial Paste:=xlPasteValues

[Code]....

(this continues on until 'ElseIf Range("AdvanceWeek").Value = "Week 31"....etc). So you can see I have the same code repeated 31 times.

View 2 Replies View Related

Input Box To Make Copy Of Master File And Rename It Automatically

Mar 14, 2014

I want a macro. when i run this macro pop up a input box and ask for copy of master copy and asked for rename the file...

E.g. I have a master file in "z:42766decmasterfile.xlsm

When I click macro then macro do a copy of masterfile and rename it according to choice...

View 4 Replies View Related

Copy And Paste (not Reading The Totals In Row 2 To Make The Formula Complete)

Jan 21, 2009

I am trying to get the formulas to work on Sheet 3, the rows formated to percentages correctly, but they are not reading the totals in row 2 to make the formula complete. I also want this to run more than one number, I would prefer a range for example value >= 1.29 so that Sheet 3 shows all items less than 1.29.

View 4 Replies View Related

Search Keyword In Worksheet / Copy And Paste Adjacent Cells To New Worksheet

Sep 24, 2012

I've been tackling this data capture/paste issue for a week or so. I found the string below which does provide a good foundation for my challenge. But, my basic level of understanding macros limits my modifications to meet my needs.

[URL] ......

I have 20 worksheets in my master file corresponding to Excel files individual associates will update weekly. After the associates have updated their individual files for the week, I want to capture the data entered and paste values into a master file containing a worksheet for each associate (sharing the same name as the individual associate file). All of these files are housed on team SharePoint sites.

I need a macro to perform several steps after clicking a "Run Update" macro button in the master file:
Open individual associate fileIn master file, search for each Initiative listed in column B (starting cell B3) in the individual associate file (in column B starting at cell B11)If Initiative is found in individual associate file, copy adjacent data in columns D:J for the respective rowIn master file, paste values to the corresponding Initiative row for the corresponding week's worth of dataIf Initiative is not found in the individual associate file, move to the next Initiative listed in the master fileRepeat these steps for each individual associate file

Linking would be the easiest way to accomplish this if I wanted to have a multitude of weekly individual files for the associates. However, I'd rather each associate have one file for them to update (basically overwriting their previous week's entries).

I need to ensure the paste values corresponds to the appropriate day of the week. In simpler terms, if the date in the individual associate file in cell D9 reads Oct 1, 2012, the data captured from that row needs to be pasted to the corresponding row/column in the master file that reads the same date.

View 2 Replies View Related

Make Cell Result Copy Itself Into Subsequent Cells X Number Of Times?

May 9, 2013

Say I have (cell A1) 10 salespeople and they each sell a (A2) $100/month membership, so I have (A3) $1,000 monthly revenue.

(A4) is an assumption about how many months the average person will stay a member.

So if A4 = 6, (6 month membership assumption) how would I get Excel to output $1,000 (the result from A3) into cells B3 through B8

but then for instance if A4 = 3 (3 month membership assumption) it would only output the the $1,000 result from A3 into cells B3 through B5

So like A1 through A4 of...

10
$100
$1,000
6

would output $1,000 into B3 through B8

but

10
$100
$1,000
3

would output $1,000 only to B3 to B5

View 1 Replies View Related







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