I'm not sure if this is possible. But here it goes. Here is how my spreadsheet stands. I have a sheet called "Dates." When I input a date into a cell it creates a copy of a template and titles it with the date (eg 2009.02.15). What I then need is for some of the cells of this newly created sheet to be linked to cells in other sheets in the workbook. These other sheets serve as databases so to speak and will collect, tabulate and analyze the data from the various sheets created for each day in the Date column. I hope that is clear.
I thought that by creating this Dates sheet I would be able to link the cells in the database sheets to the new sheets that will be created. When I tried this, however, it did not work. The sheets I want to link will have the name of the text that will be entered into the Dates cells. For example. If I put 2009.02.15 in A2 I will want the corresponding cell in my database sheet to have the formula =2009.02.15!C12. Is it possible to make this link before there is text entered in A2 and before the new sheets even are created. I have not been able to do so yet.
I was tryin to think of another way and I came up with this. Here is the code I use to create the new sheets.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsNew As Worksheet
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Sheets("experiment template").Select
Sheets("experiment template").Copy After:=Sheets(Sheets.Count)
ActiveWorkbook.Sheets("experiment template (2)").Tab.ColorIndex = 5
Sheets("experiment template (2)").Select
Sheets("experiment template (2)").Name = Target.Text
End If
End Sub
I was thinking it would be easy for this code to link the cells from the new sheet to the existing database. However, I then ran into a problem down the road. For each new sheet created (each new date entered) I want it to enter the values into the subsequent row in the database. Not keep on overwriting the same row. It seems as though there should be code for this but I am not sure how to do it.
I am trying to create a file which will contain several thousand links to files which will not be created for some time. It's purpose is to trend KPI's and budget compliance over time. Is there a way to create these links in Excel 2007 without having to cancel the annoying "Update Valeus" window? Here is an example of that is looking for a file that will be created in 2 weeks.
=IF(AND(ISNUMBER(MATCH(B12,'V:MaintenanceWork ManagementPublicWork Management KPIsSAP SchedulingJobs With Operations In The Past[Jobs with Operations In The Past 11-4-09.xlsx]Plant Section Sort'!$B:$B,0)),(ISNUMBER(MATCH(I12,'V:MaintenanceWork ManagementPublicWork Management KPIsSAP SchedulingJobs With Operations In The Past[Jobs with Operations In The Past 11-4-09.xlsx]Plant Section Sort'!$I:$I,0)))),"Duplicate","New")
for my kind of business we start off with 1 worksheet that has the original plan. We name this sheet "R(0)" Every time the plan is revised (modified) it is only done by first creating a new worksheet then saving the revisions there. When we create a new worksheet it is automatically named "R(1)" , as in Revision 1.
My request is the following; i have placed cell number "D86" to reflect the unique date that the sheet is created, so that every time a revision is done when creating a new sheet, the idea is to have this cell automatically update, inorder to reflect the date & time the revision was created. So at the end of the week when you have 5 revisions (5 worksheets), each worksheet will reflect the unique date & time of the revision.
I am completely new to Macro's and VB and the macro below has been generated using the "Record Macro" function in Excel 2013 with a couple of very minor modifications based on some research I have done (hyperlink & input box). My ultimate goal is to make a copy of my "TEMPLATE", which is hidden and the copy could have a variety of names, then create an entry in my "SUMMARY" table that references cells on the newly created sheet. The new entry on the SUMMARY page should be entered in the next available row ... at the moment I need to make sure I have my cursor in the right place before I run the macro. I also want the first cell in the new "SUMMARY" row to create a hyperlink to the newly created worksheet.
The macro does what I need it to do, as long as I name the new sheet "Test", what I would like is for the Macro to recognise the name of the new worksheet and create links to that name. The rows and columns in each new sheet will remain the same, hence the R##C## part will always work.
The "SUMMARY" and "TEMPLATE" worksheet names will not (ever) change.
My program(which is an add-in, or will be when I've finished writing it) creates a new worksheet called "Groups Summary" I would like this sheet to be locked so that the user can not modify anything on it. I would also like that if they double click a cell this will launch a userform if based on certain conditions.
I believe this code would have to go in the worksheet itself, so the question is how do I get the macro to enter code into a worksheet that it creates?
Is there a way to write it in a standard module and then automatically copy it when the worksheet is created?
I am creating an invoice form in Excel 2003. I like to know if it is possible to increase the value of cel A1 by one everytime a new sheet is created.
For example i created my first worksheet and gave an invoice number 001. Now I create a new worksheet by right clicking on the tab and choose copy/move. I like the second work sheet change the invoice number to 002.
I've written a little VBScript that generates several hundred Excel 2013 worksheets. In order to protect the users from themselves I protected certain cells. Everything works as expected, except when I discovered that the password I used to protect the worksheet doesn't work to unprotect it. If it matters, I'm not trying to unprotect programmatically, just opening Excel and going that route.
I have a FOR loop which is supposed to loop through all the worksheets in my workbook, create a named range, then add some data to the right of the range. However, when I run my code, all the named ranges refer to the last worksheet in my workbook. The loop only seems to be partly working, and I just can't work out why.
Sub maxLifData() ' for each worksheet in the workbook, do dmax formulae and add results to new sheet. bhole id on left, depth across top. Dim ws As Worksheet Dim wsName() As String Dim i As Integer Dim mCount As Integer Dim nr1 As Range Dim nr2 As Range Dim head1 As String Dim head2 As String Dim wsRangeName() As String Dim maxF As String Dim fRange As Range Dim rng As Range Dim mRng As Range head1 = "STCN_DPTH" Set fRange = Range("G3") Set mRng = Range("A2") Redim wsName(Worksheets.Count) Redim wsRangeName(Worksheets.Count).............
I have a database sheet with a ticket list which has various columns like business area, ticket number, location, status etc. I am creating a new sheet where i want to highlight only the tickets that are in pending status and I dont want all the columns from the main sheet but just a few. How can I link it so its automatic.
I was trying to develop an excel based calendar schdule for the material dispatches being done from our warehouse. have been able to cook up something using the vba, ingeniously from resources over net and some help from here, but it has not given the desired results. actually the code is not doing anything.
I'm looking to link/embed a summary sheet from one work book to be displayed in another. When i update this summary sheet i wish for it to update the linked work book.
I have a workbook with 6 tabs/worksheets. Each of the first four is a different product or quarter. Tab 5 is a consolidated data report that links data from individual tabs 1-4 in rows and columns for comparison purposes. Consider Tabs/Worksheets 1-4 to be quarters (ie. 1Q, 2Q, 3Q, 4Q). What I want is for the Column Headers in Tab 5 (the consolidated data report) to be pulled from the NAME of the Tab/Worksheet it is pulling the data from. Not an individual cell, but the name of the worksheet itself so that others can change their worksheet labels and those changes are reflected in the consolidated report.
I'm using MS Excel 2003 Pro. I need to link data from one source worksheet to a target worksheet in the same workbook when only one variable changes in the source worksheet e.g.:
A. From source worksheet:
When the data under the "Expected Date" column is set (it normally is blank)
B. Populate target worksheet with:
1. Name 2.aaaa 3.bbbb 4.cccc 5.dddd 6. expected date (the actual date that is set from the source worksheet)
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!
I have some VBA code that crates a PDF saves it in a direcory gained from Cells in Sheet and i now wanto to email that saved PDF but it keeps coming up with file path not recognised or found.
Code: Sub saveandclose2() Dim MyPath As String Application.DisplayAlerts = False
I have created a pop up calendar using VBA with the help of VBA Tips at www.fontstuff.com/vba and it works very well. However it has the default date of 12 March, In the tutorial that i used there was some code to use todays date if the cell (in which i was going to put a date) is empty. Which is as follows
Private Sub UserForm_Initialize() If IsDate(ActiveCell.Value) Then Calendar1.Value = DateValue(ActiveCell.Value) Else Calendar1.Value = Date End If End Sub
yet when the calendar does pop up, the date remains at 12 March (the calendar in the visual basic editor reflects the date correctly). I've tried to change numerous amounts of properties, without success
I've created a macro which copies the current tab to a new workbook (Book X):
[Code]....
I then manipulate the copied data in Book X. Once this is done I move the manipulated sheet back into original file
[Code] .......
The problem i'm having is I'd like to close the newly created Workbook X file once the macro has run but Workbook X is usually called Book2, Book4, Book7, Book32 etc.
Workbook X will always be the previous file if i use the ALT TAB shortcut.
I am trying to create a function in VBA that will return a range, but I don't want the range to point to cells on a spreadsheet. I want the range to return a vba created range. Is this possible or impossible? Is a variable of type range required to point to cells on a spreadsheet?
I originally posted this in the "Excel New Users forum" - i guess that was an error, but I'm very new (second post) and very new to VBA in general - so please be gentle!!
I've created a macro which opens a workbook, creates and renames a new worksheet, and moves it to the end of the workbook.
I then need to paste into this new worksheet a selection from another workbook.
How would I specify in the code that the selection needs to be pasted into the newly created tab?
I've tried declaring my object variable (TabOrder) as module-level within a Sheet Module and also as local within a Subroutine in the Sheet Module. In both cases, when the debugger steps through the code, the object is destroyed almost as soon as it is created.
I'm attempting to enter a formula into cells on a spreadsheet through a macro.
I want the formula to read:
=if(isblank(G1),"",month(G1))
but because this is being created within a macro it doesn't like the double quotations.
I can get it to display "0" but i want those cells to be NULL. Right now my only idea is to make an IF statement in the code that doesn't insert a formula in those instances, but I'd like to keep them all as formulas for later changes. Anyway around this dilemma?
Example Code (only a piece):
Code:
With Range("F1") .Value = "=if(isblank(G1),,month(G1))" .AutoFill Destination:=Range("F1:F" & lastrow) End With
I'm working on a Tool, where every change should be saved in a new Version. So if the user changes any number, he should click my selfcreated save-button, which takes todays date in the filename (no problem so far).
I tried to disable the "normal" save-button with:
Code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Cancel = True MsgBox ("saving cancelled!") End Sub
Unfortunately this code won't allow my own button to save too. Is there any other way? Maybe sth like if "normal" saving is used, then automatically activate a makro (which is also on my button) instead of just saving?
I'm a novice at Excel and need some help. We have a local area Dollars for Scholars high school chapter auction coming up and I need to sort the information that has been entered into an Excel spreadsheet (there are several hundred contacts). I need this sorted to create labels to send out donation letters. Last year we used ACT, but the chapter board wants to use Excel so everyone can have the info at their fingertips (since everyone doesn't have ACT). The information was entered in column A like the following (in rows not columns):
Name Address 1 Address 2 City, State telephone #
Name Address 1 Address 2 City, State telephone #............
I've created and excel spreadsheet that has code like this in a Module named Print w/ in the workbook: Sub Print_wsPrint() Sheets("Print").PrintOut End Sub
I have saved it as an MS Office Excel add-in.
I added the add-in to my excel environment via Tools, Add ins...and let it copy it to my C:Documents and SettingsjohnApplication DataMicrosoftAddIns directory.
But yet when I create a menu button and assign the Print_wsPrint to a custom menu, it complains and says "The macro Model.xls!Print_wsPrint cannot be found," where Model.xls is the workbook I am in. It's in my add-in, not in the current wb.
How do I get a random name from a list that I have created? The random feature only deals with numbers. I want to be able to push F9 or click on something and have a cell randomly pull a name from the list.
I've created an add-in that runs a simple macro. The problem I have is that when I run the add-in it opens the workbook I originally created it from - is this what I should expect it to do? How do I stop this from happening?
Also, do I need to have the workbook from which I created the add in located in the same place on every machine I want to use the add in on? At the moment if I move the original workbook the add-in won't work.
How to put a formula into my userform created in Excel.
What I have is 4 Combobox's which can select either 0,1,3 or 9 then each box has a weight which that number must be timed by this is the excel formula:
=SUM(L48*10,M48*10,N48*8,O48*6) so that was 9x10,3x10,9x8,3x6 Which gives me a sum of 210.
Can this be added to the userform so when the user selects the number from the dropdownbox it will calculate it into the total score?
This is a screen shot of the userform : Capture.JPG
