Is it possible to use a macro to create a new worksheet with a button on it, that has a macro attached to it, that i can send out to people? I've looked at the Template example, but i'm not sure this will work when emailed as i'm unsure whether the template will be sent to them.
View 3 Replies
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 ..........
I am at it again and maybe I am asking to do something that is not possible. I am trying to build a program that will allow the user some flexibilty down the road if I am no longer here to support my code. I have a workbook that contains several sheets. The sheets are labled by equipment name. The Sheets themselves contain specific data with regards to the equipment. What I want to do is as follows. Populate a user form with command buttons based off of the worksheet names. The reason for this would be if 2 years from now we add a new piece of equipment all the user will have to do is add a sheet in the workbook and the user form would reflect the new sheet with a new button. Like wise if I delete a sheet in the workbook the user form would refelect that change as well.
View 14 Replies View RelatedI'd like to write a macro to create buttons with the caption "Fix" in over 300 cells. Furthermore, I would like each button, when pressed to run a macro that would copy and paste the values (paste special) of the entire row in which the button is situated as well as copy and "paste special" the values in the fixed cells $J$2, $K$2,$L$2, and $M$2. I've attached a file to clarify what I'm sure is an extremely convaluted statement of my problem.
View 5 Replies View RelatedI am trying to create a worksheet that has multiple radio buttons. Each radio button is linked to a Macro. I need there to be many buttons running down one side of the worksheet. Each button needs to perform a macro that is relevant to the cells in the same row that it is on.
Essentially what i am trying to do is make a macro that when the button is pressed copys data from H6 and paste it into B6. The button is situated above I6. I need a button for each row from 6 to 110.
While this macro is easy to create (i use the record button and then assign the macro to the button), i would have to do this 104 times and assign a new macro to each button.
Is there a quicker way?
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 RelatedI 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.
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 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 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 would like to create the clock function.
This is my function name. - Clock()
If i put this function in Cell A1. Then from now onwards cell A1 will display system current time. It will keep showing the every seconds changes.
And clock will run itself independenly.
I was wondering how to create buttons such that when the user clicks on the button they are automatically taken to a different part of the workbook.
I'd like to do this because my spreadsheet is now large enough such that navigating just using the sheet tabs at the bottom is not ideal.
I can create a hyperlink, but I think a button just looks nicer.
Iam looking for macro to copy rows based on partial cell content of a column. I have an excel spreadsheet called "arc.xlsx" from which I would like to copy data to other few new excel files when certain criteria are met. The excel file contained location is C:Documents and SettingsxxxxDesktopCompany.
Below is a sample of arc.xlsx
GP CUST_NO BR CUST_NAME day mo year
I1 999999 1 SMITH 0 8 9
I1 999999 ab SMITH 4 8 9
I1 999999 cd SMITH 4 10 9
I1 999999 1 SMITH 4 1 10
[Code]...
I would like the macro to copy rows that have 'ab' in the column c (with title BR)and save it in a new excel file with name ab.xlsx in the same location folder.And the same for 'cd', '01' and '02' by saving the data in files with name cd.xlsx, 01.xlsx so on.
I am trying to write a simple macro to create a chart over the used range in a worksheet. The first part of the macro correctly selects the used range which in this case is A1 to F19 when I single step through the macro. A listing of the macro is shown below:
Code:
Sub AddChartObject()
'
Sheets("Sheet1").Activate
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
[Code]...
When I place the formula given in MyArea as an argument for the source data, I receive a syntax error, so obviously it is the wrong argument. Numerous attempts to correct it also failed. As a sanity check, I placed the used range in an argument for the source data and was able to create the expected chart. fix the syntax error in the macro
I have code that creates a textbox on a worksheet, copies the contents of a cell from another worksheet to that textbox. I want to be able to select that text and format its appearence ( center the text, bold and font style and size). This is what I have so far but I do not know how to select the text that was imported.
Sub textmove()
Dim bname As String
Sheets("cover").Shapes.AddTextbox(msoTextOrientationHorizontal, 96.75, 512.25, _
230.25, 120#).Name = "client"
bname = Sheets("data").Range("a3").Value
Sheets("cover").Shapes("client").TextFrame.Characters.Text = bname
With Selection.Characters(Start:=1, Length:=17).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
End Sub
Is that possibe to use the "templeate" sheet and lookup the "Master" sheet to create a new worksheet called "result"
View 2 Replies View RelatedI want to do is have a User form that allows a user to add a new column across several sheets (i.e. b2, then c2, then d2) where what the user has selected from 3 defined ranges (in this case commodity, pack_type and pack_spec) is concatenated. see the example attached. So for exmaple, I would like the choice of adding into cell B2 across all sheets (except sheet pack) gold solid 5kg. Then I can add the next into c2. I have tried searching few a few examples of userforms with various problems but I dont know how to code it correctly.
View 4 Replies View RelatedI am trying to creat a macro that loops all the named ranges in a worksheet named "Labels", and copy the data to a new worksheet for each named range it finds in the same workbook and name each worksheet with the named range name.
View 5 Replies View RelatedI have recorded a macro to create a pivot table. I thought I had it so that it would create the pivot from the active worksheet only. But looking at the code, it is picking up the sheet name from the one I recorded it from
Code:
Sub SalPiv()
'
' SalPiv Macro
' Macro recorded 20/06/2012 by imccormick
[Code].....
Can't seem to attach sample Widgets.xls. I need a macro to take the heading (note heading not sheet name) from each worksheet on a workbook (or from a range of nominated worksheets in case I don't want to index the first or last few sheets) and use it as the Description for a Index entry on a nominated sheet e.g. Sheet named 'Index'. In addition that each Description is also a hyperlink back to the sheet with the heading. In this way users can click between each entry on the index to go to the sheet and then click on the heading on the sheet to go back to the index.
Index Sheet
M21, M22 and M22-A BICYCLES1
Q21, Q22 and Q22-A BICYCLES2
R21, R22 and R22-A BICYCLES3
Taking this one step further I ideally want to save this workbook as a pdf and the hyperlinks to remain.
Is there a VBA code that will enable me to create a button and assign a certain macro to it everytime I insert a new worksheet?
how to insert a new worksheet with VBA, what I want is that when I insert that worksheet, there is already a button there with a specific macro(already made) assigned to it.
So I'm trying my hand at creating VBA buttons and functions that do the following.
Button 1 :
to add a column with every click. The button will create one extra column. So by design there will be at least 1 column. Example if the button is clicked twice it will create 3 columns. I just need a buffer col. (contains no data)
Button 2 :
to delete empty columns leaving only one empty buffer column. That is this button will purge empty columns except the buffer column. OR if ALL the columns are filled, Button2 will create the buffer column.
Eg. I click Button 1 three times. It creates four columns. I only fill two columns. When Button 2 is clicked it will delete all the empty colums except the buffer column. OR I fill in all four columns, there is no buffer column, when I click Button2 it will insert the buffer col.
Button 3 :
to add a row with every click. The button will create one extra row. So by design there will be at least 1 row. Example if the button is clicked twice it will create 3 rows. I just need a buffer row. (contains no data)
Button 4 :
to delete empty row leaving only one empty buffer row. That is this button will purge empty row except the buffer row. OR if ALL the columns are filled, Button4 will create the buffer column.
Eg. I click Button 3 three times. It creates four rows. I only fill two rows. When Button 4 is clicked it will delete all the empty rows except the buffer rows. OR I fill in all four rows, there is no buffer rows, when I click Button4 it will insert the buffer row.
In the sample file there are six columns filled with data. The 7th col is empty(the buffer column). The column after that calculates the average of data in filled columns. There are eight rows with data and the ninth row is the empty buffer row.
The "add" buttons must be able copy the format/formula of the column/rows BEFORE the buffer colums/rows and insert it before the buffer column/row.
I am after some code to create a new worksheet called 'xxxx' in my workbook, and then list all the sheets within the workbook (excluding the newly created 'xxxx' sheet.)
View 4 Replies View RelatedI need a macro that will create a new blank worksheet, move it to the last spot and paste the entire contents of the sheet named "master" into the newly created sheet.
I have tried to do this with the recorder but have been unable to get it to work.
The first sheet is the row data given to me, the second one is where I want my data to be analysed automatically by macros.
I managed to create a pivot table manually to give me the info I want but then I still have to copy all the results from the pivot table sheet to my analysis sheet.
How can I create a macro able to do all that by it self? I tried to record a macro using the recorder but then when I try to play it it gives me an error straight at the beginning in the definition of the pivot table I think.
An example of what I want the pivot table to do is:
in the first sheet, go in the small table in column K and L, take the value of L1, then in the row data in column A to J, in column C look for the value in L1, once found, look for the value 1 in column J then do the average on the values in column E and put the result in my second sheet in cell F5
then do the same but look for 2 in column J and put it in F8, then 3 in F11, ... until 7 found (the data is in ss.000 and so the results in the second sheet should be formated the same way)
then do the same all over again for value in L2
etc etc etc
this should be done for the 6 values in column L, each having values up to 7 in the J column.
Then I need to do the same for other columns and not only averages but minimums as well but I can adapt the code I think.
I want to find a way to create multiple worksheets and matching data to be placed on appropriate sheets.
Here are more details (Please check the attached sheet screenshot as well):
Excel_Macro_Requirement.jpg
In a workbook, there is a "Master-Sheet". This master sheet contains 8 columns.
I want to create as many new worksheets after master sheet as the values are there in column B (Column 2 i.e. Ad Group). In above attached screenshot, there are 8 values (B2:B9 or A-H). So, I would like to create 8 new worksheets after the master sheet. Also, I want to rename them based on their value from Ad Group column.
Each newly created worksheet should have same columns as the master sheet . Same 8 columns with their name intact.
Finally, I want all matching data of the Ad Group values to be placed on their respective newly created worksheet. For example, worksheet A should have A2:H2 data. Worksheet B should have A3:H3 data, and so on.
Please note that same ad group may have more than one row data. But I don't want to create multiple worksheet of same name ad group. The worksheet should be just one, but all matching data should be placed in that one sheet.
I know it is a bit complex task, but I am sure there would be a way to perform this automatically - probably a macro.
I have a userform that has one combobox at the top created manually. When the userform is opened, the user select an option in the combobox (these options are taken from a range on 1 worksheet). From the selection of the combobox, I use the comboxbox's change event to create and display 5 columns of textboxes and 2 columns of command buttons on the userform.
The number of rows of textboxes created depend on the option selected from the combobox since each option links to a different range of cells. Each of the 5 textboxes in each are set to be ".enabled = False" and display text as per the cell values within a range on another worksheet. 2 Columns of command buttons are created at the end of each row of textboxes - 1 is enabled and the other is not.
The creation of the textboxes and command buttons works as required. However, I am having problems with setting click events for each command buttons. When the 1st column of Command buttons are created, I need the click events to be created and filled out with 2 actions:
1. Enable all textboxes in the same row as the command button
2. Enable the other command button in the same row.
Here is the code I have so far that creates the textboxes and command buttons.
Each of the 5 textboxes and 2 command buttons have a unique name so the 1st row will have textbox and command button names of cTxtA1, cTxtB1, cTxtC1, cTxtD1, cTxtE1, CmdAmend1 and CmdConfirm1. The 2nd row will have the same names but with 2 on the end and so on. The bold sections is the code for the creation of the command buttons that I want click events for.
Code:
Private Sub CboTeamSelect_Change()
Application.ScreenUpdating = False
If CboGroupSelect.Value = "" Then Exit Sub
Dim cTxtA As Control, cTxtB As Control, cTxtC As Control, cTxtD As Control, cTxtE As Control
Dim CmdAmend As Control, CmdConfirm As Control
Dim iNum As Integer
Dim TxtTop As Long
[code]....
I'm trying to delete the buttons on the top of this worksheet, and for some reason am not able to do so.
View 3 Replies View RelatedScript that lists the caption names of all work sheet commandbar buttons, one script to list them on a worksheet and another to list them in a listbox.
View 5 Replies View Related
