I have seen an Excel spreadsheet someone made that has a kind of interface built into it. There are areas where you can type in variables and buttons to clink on that do calculations - and a box that shows the result.
View 3 RepliesI need to reference the projected and actual expenses from the total on worksheet 'expenses' So i did so, however, if I change the drop down on the expenses worksheet to only display housing data, then the projected and actual expenses on my budget worksheet changes as well to the new data portrayed on the expenses worksheet.
I need to reference the cell, without it changing when I change the category display, but I need the cell to change accordingly if I enter new data in the overall tablet on the expenses worksheet.
Or do I need to create a seperate worksheet that has the data in and reference my cells on my budget worksheet to that new worksheet?
The new worksheet is created to the left of the existing source worksheet.
View 2 Replies View RelatedI have code in a worksheet that creates a new worksheet when clicking a button:
View 3 Replies View RelatedI am trying to write some code that will check a range of names within a worksheet and if there is a sheet with a name from the range excel should ignore it and move on to the next range. If no such worksheet exists it should create it. When the code encounters a blank range, it should stop the code.
I have tried several different variations, and either I can't make the "check if exist" statement to work, or I am having problems with it not working for more than one loop. After reading previous posts on this forum, I have tried with err.clear, next ws in worksheet and all types of codes but I can not make it work.
When I am running the code, it will stop on second loop at [If (Worksheets(rangename).Name "") Then] and give me a runtime error 9 - subscript out of range.
This is the code:
Sub CreateSections()
Dim i As Integer
Dim rangename As String
Dim Newsheet As String
Dim Nextrow As String
Application.ScreenUpdating = False
Sheets("Example").visible = True
[Code] .........
how do I copy the last worksheet (e.g. sheet3) using the name keyed in the textbox1 as a sheet name? I got the following code but it creates 4 worksheets instead of just one with the new name.
Worksheets("invsetting").Activate
ActiveWorkbook.Sheets.Copy after:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = Ucase(Textbox1.value)
If I got 10 names in column "C", can I use the following code to create the worksheets with names in column "c"?
For k= 1 To 10
ActiveWorkbook.Sheets.Copy after:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = Ucase(range("c" & k).value)
Next
When inputing data into a cell on the main tab I want it to automatically create a worksheet, copy the contents of "Master Tab" onto the new worksheet, and name the new worksheet/tab the same as the value in the same cell on the main tab. The below link initially seems to work except that it only copies the active worksheet instead of another unactive worksheet of my choosing like "Master Tab".
[URL]
im currently working on contact details for each of our client. Attached here is the sample worksheet im currently working on. As the title suggest is it possible if i click the company name i will be directed to its contact details on the other sheet?we need a worksheet that functions like this: if we click the company name, it will direct the user to its contact details or will display ONLY its contact details.
View 14 Replies View Relatedcreating a button (using VBA code) in a excel worksheet that can initiate a range of output data in a col (say (C1:C100)) by activating the therein formula of each cell (same as what F2 does) and then entering the cell.
Actually I have to do manually each time for each output cell after opening the worksheet that I don't want.
I have a userform which collects fuel data from a pump and populates 2 worksheets (pump1) and (pump2). What I am tyring to accomplish is the following:
When fuelling is finished for the day, the operator selects the (Shutdown) button. When this happens I need the userform to perform the following.
1. For each worksheet (pump1) and (pump2) create new worksheets named (pump1_date) and (pump2_date).
2. Populate these new worksheets with the entire data from the original worksheets.
3. Clear out all but the last row of data from the original sheets. The last row contains the meter readings for the fuel pumps and needs to stay as an opening ballance for the next day. This last row to become the top row of the original sheet.
I need to create new worksheet with all the rows which has qty (column A) value of 1 and above by clicking on a submit button....
View 9 Replies View RelatedBasically i have a list of 24 invoice #'s with cost/pounds/etc.
I need a macro that would look at a list from a sheet ( Temp Sheet ).
count the number of invoice
create a tab named the invoice numbers.
So basically, if there are 24 invoice #'s. I would have 24 tabs created that are named the invoice numbers.
Can an index of all worksheets names be created without using VBA?
View 3 Replies View RelatedI'm trying to loop through a worksheet where each row is an individual record. I need a macro to read the row and create a chart on a separate worksheet for that row, then move on until a chart has been created for each individual row. In English the problem is:
Read Row 1
Create chart based on row 1 data in new worksheet 1
Read Row 2
Create chart based on row 2 data in new worksheet 2
And so on
Can you have a function create a new worksheet? i know this is possible with a Sub but i haven't been able to do this with a function. Example is below. I'm planning to call the function from an Excel cell (i.e. =CreateNewSheet())
Function CreateNewSheet()
Sheets.Add
End Function
I have a workbook to track clients served. The first sheet has all of the data entered into it. The name, age, demographics, services. The second sheet is a template of the individual client's pages, summarizing their information and services received. I want to code the sheet so that once a new client is entered into the table on sheet 1, the template will automatically be copied to the end, renamed, and the basic data for the new client will be pasted into the new sheet.
I've been working on this project and done a ton of googling. And also am very new to VBA. I have found a solution that works:
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
Sheets(2).Copy After:=Sheets(Sheets.Count)
[Code]....
But I really don't want to do 50 "else" clauses for each line where there could be a new client. So I was thinking, if i could find a way to do a relative reference, such that it selects the cells to copy relative to the one that was changed within the range of that column, then I would only need one "if" statement. Is that possible? Is there some way to change the "B3" in target_address to "any cell in column B" and then range "A3" a few lines down would somehow be "1 cell left of the one changed" and the range "A3:K3" to copy would be "1 left:9 right" of the one changed. I know this may be impossible or just not how VBA language works, but I thought it'd be a lot easier than 50 repititions of if..then...else....
I am trying to figure out how to create a drop down list on one page of a workbook with the list of items for the drop down list located on a separate worksheet within the same workbook.
I have a workbook with one spreadsheet with a list of items on it (sheet1/ingredients) and the rest of the worksheets would have the drop down list of items from sheet1/ingredients.
I have been trying to use the validation method with no luck - I just can't seem to get the source path right.
If possible I would also like the drop down list of ingredients to be self updating - the ability to continue to add items to the list and show up in the drop down lists.
When the value in the drop down window is selected I would really like / need to have the data populated in the rows to the right of the drop down window.
While asking is there any sort of auto complete so that one can start typing in the box and the ingredients that start with those letters show up to select from? (Such as if type EVA or EVAP --- then all words starting with EVA or EVAP appear instead of the entire list?)
I am attempting to write a small code for the following scenario.I have a workbook with worksheets GP1, GP2, GP3 etc & a template page as the last tab.
All the worksheets column names are same but have different data.Last worksheet is the template for all of them. How do I create a command button in template page... upon clicking the button,
it should create a tab just before the template worksheet incrementing previous tab GP3 +1-> GP4. GP4 should be a copy of the template page along with all the vb codes associated with the template page.So tabs looks like=> GP1, GP2, GP3, GP4, template
I need a code to sort worksheets in a huge workbook. Is this technically possible!?
The information containing the worksheet order is available in worksheet "Aux", column A contains the rank (1,2,3,...etc.), whereas column B contains the corresponding worksheet names.
I have a macro that using a list of values in a range, checks if there is a corresponding worksheet and if not, use the Template to create one, rename the worksheet and add the value into a cell.
I now have 2 types of template and based upon on the value in the range, I want it to create a worksheet using 'Template 1' for all values < 1000 and 'Template 2' for values > 1000.
Is it possible to amend my code for this?
HTML Code:Â
Sub CreateTimeline()
Dim rng As Range
Set rng = Application.Range("Projects[No.]")
[Code]....
I need a macro to create the following worksheets and charts from an Excel data set:
Three (3) worksheets (already created manually in attached Excel file):
1. Chart Data.
(a) Column A in Chart Data is always numbered 1 - 600 (50 years x 12 mos/yr).
(b) Column I and column Y data sets (from Prod_Month) created in Chart Data. Each data set can be identified and collated with column F in Prod_Month (API) which is unique for each dataset.
2. Rate vs. Month - plot of Daily Gas (col. Y in Prod_Month) vs. Months (col. A in Chart Data).
3. Rate vs. Time - plot of Daily Gas (col. Y in Prod_Month) vs. Calender Time (col. B et. al. in Chart Data)
At a minimum, could someone help me create the Chart Data worksheet from the data in PROD_MONTH? Charting all the columns takes time from Chart Data but any data manipulation macro(s) help.
I have a worksheet that I receive as an Excel File in this format:
Account # (10 digits), Blank, Amount (Various), Date, Number(6), Location(blank), Tran Code (2) and Description (5)
Im trying to create a document that creates a new workbook when certain functions are selected and have written a macro to assist with this. Basically im working on a comprehensive checklist with all possible scenarios that will need to be tailored for use, so you can create a document with just what is applicable to you and not the whole checklist. I have attached a sample of what i mean, along with clearer descriptors of what the document is designed to do.
View 5 Replies View RelatedI tried recording a macro to add to a new worksheet, but it gives error while running.
Issue as I understand is, by default excel gives a new worksheet a name (Say Sheet 4), and when u run macro worksheet, new created name may be different.
I am trying to do payslips. Basically I have one worksheet with about 7 columns. I have a 2nd worksheet that is like a template payslip.Worksheet 1 has hours worked and pay etc.
I want to find a method to loop through worksheet 1 and using worksheet 2 as a template create more worksheets and have the values come from worksheet 1 in the new worksheets.
I need to have many users press a button within the excel sheet that duplicates the form from the current page, opening a new tab (copying the form) that also prompts to input a specific name e.g. "Enter date and day or night shift" to name the tab itself.
I have just worked out how to put a Command Button in and used the above code to create the new page assigning the code to it.
I need the "my sheet" to be prompted and customisable.
I don't know VBA code but i am familiar enough to know that a code can be written to separate out information into different tabs on a worksheet.
For example, I have a standard worksheet with say 20 columns going across and 20,000 rows going down. At each "name" change in row 1, I would like a macro to copy into a new tab. Basically the end result will have a tab for each name and the corresponding columns that go with the name.
.How do you create a popup windows which shows cells on another worksheet that you can change the values based upon a index / match or vlookup?
I select a cell that has a vaule of 102.
then Popups a box which matches that 102 range in another worksheet and shows pulldown (yes no). (Theres about 5 cells (side by side) of yes no pulldowns.
I want the user to then select the values they want and close the popup box.
User selects the following cell with 103. and then goes thru the same process.
I need to create a Worksheet within a macro that has 10 tabs. I had something running, then I made some changes and I got Application-defined or object defined error.
Sheets("Sheet1").Name=Sheetname(1)
Sheets.Add after:=Sheets(Sheets.Count)
I was looping thru this up to 10 now its not working. This isn't too complicated,
I'm trying to create a macro that creates new worksheets with buttons on them that have macros attached. I've been able to produce one worksheet with a button and macro, but when the function that copies the code is looped it crashes out of Excel.
I realise that the usual method of doing this would be by templates, but this code will be run on other people's computers so the template wouldn’t exist in the location that i had specified (I think with my limited experience with VBA - Please correct me if I'm wrong). I've also tried to copy the page with the button on, but this takes too long (even with screenupdating = false) as there are many sheets to be created.
This is a stripped down version of the code that I have created for the purpose of this forum. To recap, the function that creates a new page, with button, with macro attached works. It's when it's looped that it crashes out. I've stepped through the program, and it reaches the second message box, then it gives up on life ..........