Macro To Add Additional New Rows Based On Number In A Call?
Jun 3, 2014
create a macro that will insert a certain number of blank rows to my spread sheet. The number of rows is determined by the #of tasks calculated for that day and is in column B. For example if in column A the date is 01-07-14 and in column B it has 7, I would like 7 rows inserted with the date in column A to remain the same in all 7 rows. Ideally I would like to use this macro for every row, so if the next row down has 02-07-14 and 6, I would like it to add 6 new rows with the same date.
I have attached a copy of the spread sheet for reference.
I've got a macro working that updates things in different sheets, the sheets have peoples names in (this can't be changed).
When running the macro its fine, but if a new person is added i would need to update the macro for every sheet (the macro can be run from every sheet within the workbook).
I've noticed if you right click the sheet button and go to properties (or something) that it shows (Name) Sheet2 and Name Joe Blogs.
Instead of the macro calling to Name i want it to call to (Name), is this possible?
The code that calls the sheet looks something like this:
Sheets.("Joe Blogs")
Instead of looking for Joe Blogs i want it to look for Sheet2
I have a spreadsheet with a list of names from cell A5 to A? - can be as many as 130 names or as few as 20. I have to create individual spreadsheets with the persons name as part of the filename. I have 3 subroutines which work manually ( CreateFile, LookUp and NewWorkbookSave ) so that I can generate individual files for those users who require them. However I need to be able on occasions to create a full teams worth of files with one click - to this end I need to be able to loop through cells A5 - A whatever and call the Subroutine CreateFile - I enclose the code I have so far
Const Fname = "AON Ver 3,0.xls" 'insert filename here like "AON Ver 3,0.xls" Const DirName = "\Ukplscs105central repositoryAOMTally SheetsTeam upload Tally sheets" Const TeamName = "AON" ' insert team Name here like "AON" Const FilePath = DirName & TeamName Dim count As Integer
I have a checkbox and a command button on my sheet. I want that, depending if the checkbox is marked or not, I can call two different macros. I don't know if this is possible at all or maybe can be done easier otheriwise, but this would more or less be my idea of what should happen:
Private Sub CommandButton1_Click () ' If CheckBox1 = marked Call MacroA
'Else (CheckBox1 = unmarked) Call MacroB ' End If End Sub
I have an excel file with a table in it. It contains 2051 records (attached). This is just a sample, the original file has around 30,000 rows.
When I start using filters, I run into problems:
Step 1: Filter by Unit, condition (e.g.) Unit_23 Excel shows in the status bar the following message: 437 of 2050 records found.
Step 2: If I scroll to the bottom of the table, the row numbers are colored in blue (normal for a filtered list) however the last row is not colored and it actually should not be shown since its unit is not what I filtered for (its unit is Unit_25)
Step 3: Clear the filter of Unit
Step 4: Sometimes (depending on what I filter for), one or more of the bottom rows are hidden!!!
I have the following code pasted into the worksheet module which used to work fine but no longer does. I didn't touch the code, it just stopped working.
Private Sub WorkSheet_Change(ByVal Target As Range) Select Case Target.Address Case "$A$16" Select Case Target Case "Custom Color 1": Call CustomColorInput1 Case "Custom Color 2": Call CustomColorInput1 Case "Custom Color 3": Call CustomColorInput1 Case "Custom Color 4": Call CustomColorInput1 Case Else: End Select Case "$A$17" Select Case Target Case "Custom Color 1": Call CustomColorInput2 Case "Custom Color 2": Call CustomColorInput2 Case "Custom Color 3": Call CustomColorInput2 Case "Custom Color 4": Call CustomColorInput2 Case Else: End Select Case "$A$18"
CustomColorInput 1-6 are the same except they input data into different cells F16-F21.
When I attach any of the CustomColorInput macros to a button it works fine. When I try to call it from the worksheet module, the data input box pops up but the data doesn't get entered into the cell. When I try to step through the CustomColorInput macros using F8, the input box pops up, I enter data and press ok. Then I continue with F8 and the debugger jumps to a custom function that I entered (below), which is not called for and is in no way related to the code.
Function PullAfterLast(rCell As Range, strLast As String) PullAfterLast = Mid(rCell, InStrRev(rCell, strLast) + 1, 256) End Function
When I close VBA, the data that I typed into the data input box is in the correct cell.
So basically, I'm getting three different responses from the offending code depending on how I call it up. Can anyone tell me what's going on?
More generally, in this and previous occasions, I've had problems with macros that cease to work for no reason that is apparent to me. What are some things that would cause a macro to stop working, without actually changing the code (other than the obvious, like referring to names or worksheets etc. that don't exist anymore)?
I have two combo boxes that both contain 7-12 separate search criteria for the user to choose from. The other includes months and the other value ranges in text form. Based on the selections, e.g. "August" from other and "increased by more than 5 %" from other, I'd like to have a command button to execute the appropriate macro. I've already compiled the macros for each occasion but I just can't figure out how to get the button to execute them. Can I use the Select Case statements? If so, how?
Oh, and whether it's relevant or not, the boxes and the button are from the control toolbar.
I am using a spreadsheet as a score sheet for a competition. One of the columns is the student's GPA. After entering all the scores there are duplicate final scores. I need a way to have it look at the final score and then use the GPA so that it will not put a duplicate value in the final column.
al Column N is the Total Column, Column O has the Names that correspond to the Total Column. Currently I am taking this total and putting it into Column Q (High Scores) in high to low order. Column R should have the names that match the scores. But with duplicate scores, it is only putting the first name associated with the score. I would like to use the GPA as a final determining factor for the duplicate scores. The higher GPA would come before a lower GPA. I have tried to put an additional column to bring the GPA over to correspond with the High Scores Column, but could not get it to work.There are actually more names for the competition and the top 10 will be moved to a different sheet and further judged. I have attached a sample with the exact formulas that I am using.
I'm not great anyways with VBA Macro.Effectively, here is my issue. I have a spreadsheet which is really badly designed.In one column I have multiple numbers separated by a comma. I need this data separated into new individual rows, but at the same time, copying the data in the other columns in that row to the new row.
Example of what I have:
Test 1 54 email1 Test 2 32, 343, 63, 34 email2 Test 3 4934, 5342 email 3
What I need:
Test 1 54 email1 Test 2 32 email2 Test 2 343 email2 Test 2 63 email2 Test 2 34 email2 Test 3 4934 email3 Test 3 5342 email3
I have have the following code below which paste everything into a new column and into a new row, but the problem I have is that it does not push the other rows data down, nor does it copy the 1st rows data (for that data set) into the new rows created.
Code: Option Explicit Sub Macro1() Dim fromCol As String Dim toCol As String Dim fromRow As String Dim toRow As String Dim inVal As String Dim outVal As String
[Code] ........
The amount of rows I have, so this manually (text to column, then transcoding etc) it out of the questions (i have 1000 rows of this!)
I have a VBA code that captures auto-filtering based on two criteria, but the problem is, the spreadsheet I am working on changes the amount of rows daily (ie: more rows are added onto it).
How can I change my code such that it doesn't fix the filtering area to a certain date's row #'s?
I have a macro attached to a command button to clear entries in my workbook for me however, I left out one tab that needs to be cleared.
I need to add the following tab and cells.
Add Tab "Time Off Glance", Cells (B1:P35) to the same command button.
The code I have now is:
Private Sub CommandButton2_Click() Dim rng As Range Dim x As Integer Dim y As String For x = 1 To 27 y = x Union(Sheets(y).Range("B9:O15"), Sheets(y).Range("Q9:T15")).ClearContents Next x End Sub
calculate the number of full rows in one data base located on one sheet X to determine how many rows the macro needs to extend on sheet B (sheet B is made only of formulas for data interpretation in sheet A.
I work on a daily basis with spreadsheets in excel. The number of columns is the same, but every single spreadsheet has a different number of rows. I recorded this macro in a table with 1196 rows and I would like to use this macro also in other tables with a different number of rows.
I have Sheet with 40 employees who each proposes their work schedule, so I have to give each Employee access to the same sheet and want highlight and unlock only those cells that specific employee can use.
Each employee has to login from a drop-down (sourced from Sheet.Employee Master), so their unique Employee Number is in "A13" of Sheet.LOGIN
Can I identify the ROW number and then use that ROW number in a macro to highlight and unlock specific Range of Cells in Sheet.PROPOSED SCHEDULE? ---where "Sheet.LOGIN("A13") = (the value in the cell Col A:"row" of Sheet.PROPOSED SCHEDULE)
I have attached a scaled down version of the Workbook.
Following code is scaled down-- this is for Employee 02 who appears on ROW 16 of the sheet. (macro is same for each employee, just uses a different row)
I need to build a macro which copies 3 rows every day and pastes the row data into an identical sheet. The three rows will have column "D" as =today(). As the days progress the three rows will change accordingly ( tag to the today's date)
e.g. 28/1/2014 28/1/2014 28/1/2014
I need the macro to recognize the date when pressed and copy the corresponding rows of data and paste them into an identical sheet with the same date. The second sheet is an archive sheet. The date will tick over as per the calendar.
I'm a macro novice and have been trying to teach myself how to write the correct one for a task I need to do, but I cannot seem to get it right. Basically, I have bunch of data and for one of the variables, different values are separated by commas. What I want is to create a row copying the info below for each piece of data after the comma.
Sheet1
A B C D
[Code].....
I suspect there is a fairly easy way to do this, but I cannot figure it out from searching the forums (or rather, I can't get it to work right).
I'm using a CALL Macro to split up a HUGE macro into different pieces:
Code: Sub RSLDASHBOARDV2() 'Macro recorded 12/14/2010 by Ryan R. Koleno, Pharm.D. 'Last Updated 7/10/12 by Ryan R. Koleno, Pharm.D. 'Do Not Modify Code Unless Given Proper Privileges to do so. Dim APPSPD As Worksheet With Application .ScreenUpdating = False .Calculation = xlCalculationManual
[code]...
The first few macros dealing with page setup and what not work fine but when it hits the Pivot table code for the STATSPIVOT macro it errors out stating: "Run-time error '1004': Unable to get the PivotItems property of PivotField class' at this point in the code:
Code: objField2.PivotItems( _ "TRC").Position = 1 objField.PivotItems( _ "MEDCO MAIL OR AOB").Position = 2
When this macro is not split up it worked fine as written. Am I overlooking something in the Call Macro's code or is there a variable I'm not aware of. I have included the Pivot Table code that errors out as well.
Code: Sub STATSPIVOT() 'STATS PAGE BASED ON STATS DATA TAB Sheets("STATS DATA").Select Dim objTable As PivotTable, objField As PivotField ActiveWorkbook.Sheets("STATS DATA").Select Range("A1").Select
This is the code I use to call a macro when the macro Im calling is in the same workbook.
[Code].....
However, I would like to call this same macro when using another workbook. I copied the macro "Clearformating" and pasted into a personal macro workbook module. However when I add this code to the sheet tab it will not run the macro.
I have a Sheet sheet1 and I want to run a macro when the cell D2 in Sheet1 is equal to 10,7,5,and 3. I only want this macro to run when those values are reached the macro then puts the data onto a sheet called wps. The macro is run as a module and is a sub macro.
As you can see in the attached excel doc, I have calculated a column for "Number of Claims Per week" and also "Dollar amount Per claim". This was done using VLOOKUP and distributions. That is not the issue.
The issue I am having is creating the "TOTAL" for the week. So for example, if week one generates 4 claims, I need to be able to add up claim amount #1-#4.
It seems as though this would be a basic function, but I can seem to find a way to add up a certain number of cells, based on a variable generated from a random number.
If you take a look a my excel sheet, I am trying to solve for the "Claim Total" column on the far right. This column should be the sum of (Number of claims * The individual amount per claim). Note that if there is 4 claims in that week, I would have to add up claim 1-4 to get that weeks total.
My worksheet has data in column A. A formula is placed in cell B2 (col headers are in the first row). What is the code to copy it down so it coincides with the rows in Column A? If I use the following:
I will need to duplicate (1) 38 times with the location name and building name. Similarly, I need to duplicate (2) 3 times. How can I simplify it programmatically?
I have a spreadsheet which contains data needing to be re-formatted into a suitable format for my database. So far I have been doing this by hand, but want to make it quicker as I have over 2000 lines to sort out. I am trying to write a macro that determines whether there is data in the column next to the active one (D2), and if there is counts how many columns there is data in. Once it has done this it needs to insert the same number of rows as there are columns, then copy the column data and transpose it into the empty rows. My macro seems to work at first, but it exits after around the 4th row, but I can't work out why.
Sub split_For_Database() Dim No_Of_Cells As Integer Dim Start_Cell As Range Dim Cell As Range Dim LastRow As Range Dim i As Integer Dim lRows As Long Set LastRow = Range("C65536").End(xlUp) i = 0 Set Start_Cell = Range("C2") Set Cell = Start_Cell Do While Start_Cell.Address <= LastRow.Address Do While Cell.Offset(0, 1) > 0 i = i + 1 Set Cell = Cell.Offset(0, 1) Loop...................................
I want to be able to call one user form from multiple ActiveX command buttons. The problem is, the number of command buttons depends on user input on another worksheet, so it's variable.
I've renamed all of the command buttons so they are named "CommandButton" & i, where i is an integer between 1 and, say, 200. I want each of these buttons to direct to the same UserForm where additional information can be entered.
I can't think of a way around the event-handler procedure name.
Sub [Command Button Name]_Click
to call the User Form. I won't know the command button names, because I won't know how many there are (max i) until the user inputs.
Basically, I want to create a For loop through the max i and have the event-handlers call the user form
I have a work book with 3 sheets. Sheet 1 is the main sheet and sheets 2 and 3 will use (I hope vlookup) to update 3 columns from info in sheet 1. my attempt at a vlookup call in sheet 2 is: =VLOOKUP($b3,[master_AoJ_2.xls]Sheet1!$B$3;$B$65,false)
my understanding is that: - $b3 is the cell in sheet 2 that will be updated as a result of the vlookup call. - [master_AoJ_2.xls]Sheet1!$B$3;$B$65 refers to range $B$3:$B$65 on sheet1 of master_AoJ_2. it does not work... infact I get nothing at all. when I type the call into cell $b3 of sheet2 excel thinks it is stariaght text. this is how I coded the function call =VLOOKUP($b3,[master_AoJ_2.xls]Sheet1!$B$3;$B$65,false)
I have two different modules in my automation - to be specific one module to convert file from LH to RH and another module to do the reverse - convert RH to LH.
I have put up a radio button choice for the user and based on which radio button is selected would like to run that particular module.
Below is my code. It always throws an error "expected variable or function not module"
VB: Option Explicit Public Sub MAIN() If ThisWorkbook.Worksheets("FRONT").OptionButton1.Value = True Then Call LH_to_RH
[Code]....
I am following the correct procedure to call modules. A few minutes of search said the modules have to be public sub routines
