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
ADVERTISEMENT
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
View Related
Dec 22, 2009
I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.
Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?
i.e.
First dynamic range: called "Milestones" at A11
Second dynamic range: called "Activities" at A25
& make a Validation list that will list content of both
View 9 Replies
View Related
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
Sep 30, 2013
I have this code which looks information in a table. The problem is that the table is getting every time bigger.
Here the code:
VB:
With Worksheets("Sheet10").Cells(9, 3).Resize(, LastColumnf - 2)
.Formula = "=IFERROR(CONCATENATE(VLOOKUP(Sheet10!C2,Sheet12!$A$10:$C$550,3,FALSE),""."",
VLOOKUP(Sheet10!C2,Sheet12!$A$10:$C$550,2,FALSE)),"""")"
.Value = .Value
End With
How can I write $A$10:LastNewRow... Another problem rises here, LastNewRow might end up being the oldLastNewRow, i think. But maybe since it is getting bigger, there is no problem, I don't know.
Instead of having a static reference like $C$550 I would like to have a dynamic one.
View 3 Replies
View Related
Apr 17, 2014
I have never used charts in excel unfortunately. I just finished watching a few tutorials but still a little confused. I have a WS with a list of items I want to make various charts of how do you make a chart that adjusts when new items are added? (thus dynamic data) Do you need a macro to do this or is it just standard by defining a range within that sheet?
View 7 Replies
View Related
Jan 1, 2010
why this isn't working?
=SUM(OFFSET(A4,0,3,1,(LOOKUP(9.99999999999999E+307,4:4))))
View 9 Replies
View Related
Sep 17, 2007
I need to compare two tables in two different worksheets (A and B) and update the weekly changes (income and sales) from B to A. I have been using an Index(Match) formula which has been working perfect. I am however forced to update to a macro.
The worksheets are identical and each item is identified by a unique ID.
Any ideas in exchange for a 6-pack Budweiser?
View 4 Replies
View Related
Oct 3, 2007
I would like to update a chart after I have updated the underlying data just by clicking a button and displaying the updated date in the chart. Does anyone know how to do this? Currently I am having to manually click on the graphs line in the chart, which highlights the columns data, then manually extend this for the chart to update. And then I am manually typing in a date cell in the chart. There MUST be a cool way to do this.
View 6 Replies
View Related
Aug 18, 2014
I have attached a workbook.
I have a calculated value in H2. This value will correspond to values in the table provided. This value is dynamic and will change based on criteria that will be added.
The value H2 is a combination of to "letters" D1 and A2 or A4 and B1 i.e. trace back where the intersection of H2 occurs within the table
In Cells A8 and A9 I am trying to represent which combination of letters will result in an intersection of the value H2. In the example provided it is C and A.
What i would like to do is have Cells A8 and A9 be more dynamic. So if for example the value in H2 changed to say R12,06 then the intersection would be B and B. Therefore Cell A8 = B and Cell A9 = B
The formula is cell A8 and A9 doesn't cater for this as the range is fixed. I need a dynamic range for the index and match function based on the where the intersection of H2 happens to be in the table.
View 4 Replies
View Related
Jan 19, 2010
It mentioned something about getting comments which display dynamic data as if something metioned in the cells will appear in the comment and on changing the data in the cells, the data in the comments also changes with it..
Unfortunately, I could not save that file or remember the website as my Internet went bonkers...
So can someone please help me with the same....
View 8 Replies
View Related
Sep 25, 2013
I am trying to send my field reps a spreadsheet that will allow them to pick the proper location for each building. The problem I am having is that there are ~45,000 buildings, each with anywhere from 1 - 92 locations. Here is how my spreadsheet is set up.
Sheet 1
Column A has the Buildings THIS IS NOT A DROP DOWN. THIS IS A STATIC CELL PRE FILLED OUT
Column B is where I want the drop down to be available for the rep to pick the location
Sheet 2
Column A has a list of the buildings
Column B has a list of the locations
I understand that normally I would need to create a named range for each building and its locations. However, there are more Buildings than columns in Excel. Is there a way I can do this using Index/Match, or Offset, or Indirect? I have a sample spreadsheet that can be found in my dropbox account using this link [URL]
View 6 Replies
View Related
Feb 8, 2014
I would like to repeat a formula and make its dynamic depending on the request.
The formular that I would like to repeat is:
=IF(ISERROR(INDEX(Data!$A$2:$G$14997,SMALL(IF(Data!$A$2:$A$14997=$D$2,ROW(Data!$A$2:$A$14997)),ROW(985:985))-1,3)),"",INDEX(Data!$A$2:$G$14997,SMALL(IF(Data!$A$2:$A$14997=$D$2,ROW(Data!$A$2:$A$14997)),ROW(985:985))-1,3))
View 2 Replies
View Related
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
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
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
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
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
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
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
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
Jan 25, 2010
I have this logic that clears cells in all WS in WB.
The logic is using each sheet name to reference the logic to clear the contents.
Look:
Private Sub CommandButton1_Click()
ActiveSheet.Unprotect Password:=""
With Application
.ScreenUpdating = False
.EnableEvents = False
Response = MsgBox("This Action Will Prep For A New Week. Do you want to Continue?", vbYesNo)
If Response = vbNo Then
Exit Sub
End If
Range("H7") = Now()
Sheets(Array("Wednesday", "Thursday", "Friday", "Saturday", "Sunday", "Monday", _
"Tuesday")).Select
Sheets("Wednesday").Activate
Range("B9:H25").Select
But I also want to change each sheet tab name to reflect a cell that has the date. But how to do this with the sheet reference in the logic? Couldit be dynamic?
View 9 Replies
View Related
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
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
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
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
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
Nov 21, 2013
i currently have a workbbok that i have a very simple worksheet event macro that will filter a seperate sheets contents to the corresponding entry number. is there any way to make a dynamic event macro that will filter my second sheet depending on what cell is selceted on the first.
for example record 1 on sheet 1. if cell L1 is selected it will filter sheet 2 to to record 1, record 2 on sheet 1, if cell L2 is selected it will filter sheet 2 record 2 and so on. this list of records will continue to grow and i cant think of any way other than what i currently have, which is to make each filter macro for every cell.
is ther a better way example of CODE HERE
VB:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Address = "$L$5" Then
Call Filter1
[Code]....
View 2 Replies
View Related
Apr 22, 2009
Is it possible to have a list in a summary sheet, containing the names of all other worksheets in the workbook, which will automatically update when a worksheet is added or deleted? I use Excel 2003.
View 9 Replies
View Related
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
