Populate A Given Number Of Rows Starting At A Specific Location
Jun 2, 2014
I have a spreadsheet where I track resources needed on a project. Each resource that is selected in the resource column has a corresponding % avg and an average number of months where the resource is used. % Avg and # of Months are found in a different worksheet.
What I am looking to do is, for example: if resource A will be on the project starting in May 2015 and assigned at 30% for 7 months, I want to populate 30% starting in the colunm labeled May 2015 and copy the 30% so that a total of 7 months are populated. Also, if any of the values change (i.e: the Resource Name or the Start Month) I want to erase all the percentages that were entered in the row. How can I achieve that with a VBA code? I have formulas that retrieve the # of Months and the % Avg already.
These are the columns that I have in my spreadsheet:
Resource NameStart MonthJan-15Feb-15Mar-15Apr-15May-15Jun-15Jul-15Aug-15Sep-15
View 2 Replies
ADVERTISEMENT
Mar 10, 2014
I would like to create a vba script when ran an input.box comes up and asks you to "enter number you want to start numbering from" and when the number is entered in the input box. e.g "7654", excel starts numbering each cell in column A from 7654 until there is an empty cell in column B.
View 7 Replies
View Related
Jun 19, 2013
I use the following code to copy entire rows from one sheet to another and this is pretty standard.
VB : rng.EntireRow.Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Assuming that Sheet2 is blank, this of course copies the rows to Sheet2 starting at Cell A2 and down column A. I would like to modify the code so that I can specify at which row the copy should start. For example, I might want to start the copy at A1 instead of A2, or at A10 instead of A2.
View 5 Replies
View Related
Feb 1, 2008
1. When text is entered into a specific cell in the Master Sheet a new Tab is created named after this text and a second string of text i.e the date.
2.At the same time as this TAB is created I would like only the rows with checkboxes ticked in the Master Sheet to be copied to the new TAB(Not the Checkboxes themselves) [possibly using TRUE/FALSE in "B" row].
3.I would like the formulas, formats of these checked rows to be copied too.
4. Additionally the first 12 rows(all headers) and the last row(subtotal row)should be copied too.
The idea of the Workbook is to have an overall options sheet that is customised on a per customer basis with a record saved on a new tab in the same workbook.
View 14 Replies
View Related
Jan 20, 2009
I have a form pop up when user runs a macro and it populates the form beginning with ActiveCell. How do I always populate the form beginning from column A (row based on ActiveCell)? It would be really helpful if I could just set cl to take the ActiveCell and back it up to column A.
View 7 Replies
View Related
Sep 4, 2010
Let's say I have a long macro and I want to test some code a ways down in the macro, but I don't want to have to run through all the code to get there, because I already have my Excel spreadsheet set up at the point I need to check and I just want to start checking the code a ways into the procedure.
Short of commenting out all the code above where I want to start, is there another easier way to do this?
View 8 Replies
View Related
Jun 20, 2008
I'm trying to write a macro that will find a value beginning "ber" and paste it across to a different column. I have had a go at it but I keep getting the offset flagged up.
It needs to move 15 columns across and 7 rows down if that is of any importance.
View 9 Replies
View Related
Jun 12, 2008
I have a worksheet that will have tabs added as time goes by and a macro within the workbook currently selects all of the sheets and copies and pastes formulas within all of the sheets simultaneously. Right now, if a tab is added, the macro needs to be updated to select that specific tab name as well.
I would like to simply have the macro select all of the sheets starting with "IL" until the sheet before "reference", or all sheets except the first 3 and the last 2 if that is easier. Any ideas? And thank you for your help, you people really amaze me sometimes!
View 9 Replies
View Related
Feb 3, 2014
I need to add something to the code below to have my first row of data entered into cell "Q8". As it is now the data is starting on row Q9 instead of Q8, but the rest of it is filling in correctly.
[Code] .....
View 1 Replies
View Related
Oct 9, 2009
Here is what i am trying to achieve. If the date 2/20/2010 is located at F53 & the cell next to it at H53 is populated with a number between 1 & 16, then i want the cell at J11 (42 rows further up) to auto populate with the number 1. When this occurs the cells beneath this, from J12 to J52 should also auto populate with the with consecutive numbers from 2 to 42. Would also like to see the cells with numbers 1 to 28, automatically format to orange & the cells containing numbers 29 to 42 automatically format to yellow. I plan to have this condition repeat several times later in the year, at dates that are to be decided. When these dates are decided i want to be able to enter a number from 1 to 16 & next to the date & all of the above automatically occurs.
View 14 Replies
View Related
Aug 20, 2014
I need to unhide 2 rows at a time starting with rows 28 and 29 IF cell N28:O28 > 0. I need this to be for rows 30 and 31 IF cell N30:O30 > 0, rows 32 and 33 IF cell N32:O32 > 0, rows 34 and 35 IF cell N34:O34 > 0, rows 36 and 37 IF cell N36:O36 > 0, rows 38 and 39 IF cell N38:O38 > 0, rows 40 and 41 IF cell N40:O40 > 0.
There is currently a formula in each of the selected cells that is why I was going for greater than zero.
View 1 Replies
View Related
Jul 18, 2013
I have a code which is copy/pasting the selection specified number of times
VB:
Sub CopyNtimes()
Dim i
For i = 1 To Application.InputBox("How many times do you want to copy the selection?", "", 1, Type:=1)
With Selection
.Copy .Offset(i * .Rows.Count)
End With
Next
End Sub
How to modify this code so that it can paste the selection specified number of times leaving specified number or rows
For Example:
Selection is A1:Z10
need to copy paste 2 times...
Rows to leave: 3
So it should paste in the range A14:Z24 and then A28:Z38
View 5 Replies
View Related
Jan 25, 2006
I need to number some rows starting at row 5. Need to place the number in column AB of each row.
Needs to quit numbering at last record (column F will have information in the last record)
i need this to be a macro, never done an autonumber macro before....
View 9 Replies
View Related
Feb 13, 2013
I want to count the number of rows in a specific column up to an empty cell and assign this value to a cell. I don't want to count the total number of rows but instead I want the number of the first group of rows.
For example, column A may have cells ranging from row 2 to 10 and then from row 12 to 20, so I only want to count the first group.
The below code counts the total which is not what i need.
Code:
Sub test()
Dim Mycount As Single
Mycount = Application.Count(Range("A:A"))
Cells(1, 4) = Mycount
End Sub
View 2 Replies
View Related
Nov 12, 2008
I am trying to generate a folder within a specific location (For now lets call it C:Jobs) that will be named as per the adjacent cell (Column H on attached)
Obviously if the folder already exists I want the code to stop.
But this code will have to generate a different folder for each row within the spreadsheet.
View 8 Replies
View Related
Oct 14, 2009
I've been searching for ages trying to work out how to do this but have so far only managed to confuse myself. i have office 2007 and I have found some code to convert an excel sheet to PDF, however I need it to saveas the contents of cell (e6) and save to a location on our network drives (C:TEMP).
View 2 Replies
View Related
Aug 11, 2006
I have an Excel file to distribute to users. This file must contain Hyperlinks to a Word file which i will enbed in a sheet of the Excel file itself. My question is this: Is it possible to produce Hyperlinks that go to the specific part of the Word document? So far, i am only able to Hyperlink to the first page of the Word doc, by doing a macro.
View 2 Replies
View Related
Apr 10, 2014
Not something I've ever had to do before as i generally work with data sheets, i'm creating a document to print but need the page number to start from a set value and not default "1" of "10" etc,
I need it to start from say "5" of "15" rather than the "1" of "10",
Its simple to do in Word, but like i say not something I've ever had to think about in Excel.
View 3 Replies
View Related
May 22, 2006
Using an external UNIX program that quieries a database, I export all the data found based on my query and export the report as a text file. THe text file can be up to 1 MB of raw text.
Selecting all text and pasting the data into collum 1 using PASTE SPECIAL -->(TEXT)
All Data is in Collum A
Each record returned has between 40-50 values each on a seperate row, only 10 of which I need.
I am looking for a way to find the rows that begin with the unwanted field, and then delete the entire row.
Otherwise ignore the row.
IE:
AUDFI : (data path loc here)
BILLNUM : 060606
CUTNUM : (0000)
SD : 120012
ED : 121259
CC : 123456789 MATERIAL LISTS AND ITEMS
COMMENT : Multiple lines of text here
with continuing free form data, numbers, operators,
then the next data file would repeat:
ADUFI : (data path loc here)
...
...
...
etc.,
In the above example, I want to delete the rows AUDFI, & CUTNUM and move the below items up to take the empty rows place. The BILL NUM, SD,ED, and COMMENT are filed that are always kept (as well as others). Each record has roughly the same data, each tailored to an event or object.
The number of rows of data dumped into excel can easily exceed 50000, about 200 - 1500 records in the report.
After wards, the data is dumped to a text file for easier analysis.
In BASIC my line might look like
5 START
10 if A:$="AUDFI :" then delete row, move below contents up.
20 if A:$="CUTNUM :" then delete row, move below contents up.
30 END
(I think there needs to be a loop since the vaules AUDFI and CUTNUM (as well as other unwanted fields can occurr 200 - 1500 times each.)
View 7 Replies
View Related
Aug 23, 2012
I am running a macro in Excel which automatically generates a PDF of my worksheet. Currently it saves in the default location but i want to modify it to a specific location - P:Emergency Services|Procative ContactForms PDF.
The current code is;
VB:
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("E7").Value _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
Range("A1:E43").Select
View 5 Replies
View Related
Jun 24, 2014
I know Excel is not the best tool for formatting sentences, but is there a way to insert a Line-Break based on a condition? (Either using Formula or VBA).
The situation is:A fixed-width cell, is a concatenation of 5 strings. Each of these 5 strings is a small sentence of 3-6 words; individually much smaller than the cell-width.2 of these 5 are fixed (one at start, one at the end). The other 3 are results of their respective "IF" formulas.Hence, the final sentence wraps over 2-3 lines.I need the 3rd & 5th sentence (the whole sentence, all 3-6 words of it) to start in a new line, only if they are going to be wrapped into two lines.
So If the wrapping results in:
String1 String2 Stri
ng3 String4 Stri
ng5
Then change it to:
String1 String2
String3 String4
String5
View 6 Replies
View Related
Jan 21, 2009
I would appreciate some ones help to correct a macro I'm trying to write. The macro copies 3 columns from Sheet1 to Sheet2 in a selected location. The 3rd column copied needs to be pasted in a different column in Sheet2.
View 6 Replies
View Related
Jan 22, 2014
I have code that has the user identify a file and then it puts that file location in a cell within the workbook
"Y:MastersProPricerENG WBS BOEXYZ.xlsx"
I want to copy a tab from the active workbook ("Resources (Spread or Load)") and move it to the workbook listed above.
View 3 Replies
View Related
Jun 28, 2008
I would like to use the attached "menu maker" file I found on Ron De Bruin's site using a Sheet to build the menu, yet be able to place it before the Help menu as suggested by this site [url] by finding the position of Help.
I have amended the code yet receive a "Compile error: Variable not defined" error.
View 6 Replies
View Related
Mar 8, 2014
I am having trouble setting up my cycle count spread sheet. For instance if my starting number is 1000 and when it reaches 0 or below it starts to count in minus when I just want it to automatically reset to 1000
View 3 Replies
View Related
Mar 13, 2009
I saw this macro posted by Fengore back in 2006, and it works beautifully. Now my question: Is there a way to continue using that string but have it start from a certain row? Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
View 2 Replies
View Related
Jul 21, 2013
Im struggling to get a workbook to a specific location. I have copied 3 worksheets to a new workbook, which im trying to save to an archive folder. Here is my code.
Code:
Sub atest()
Dim strFileName As String
Dim Archivepath As String
[Code]....
The problem is i keep getting an error (runtime 1004) saying the file could not be found?? well, im trying to create it !..
View 2 Replies
View Related
Mar 14, 2014
I have a User form with a combo box that is populated with numbers (1 thru 50) and four text boxes for first name, last name, email & cell number.
It all works fine. However: I would like it to transfer the data to (Sheet3) in numerical order... In other words, If the user picks number 5 his data would be entered in the fifth row.(or sixth counting header). Or if he chooses number 37, his info would be entered into row 37 (38 with header) of (Sheet3)
It currently populates the next empty row.
My code is below, How would I modify it to accomplish this?
Code:
Private Sub EnterButton_Click()
'Populates GetNumber Combo Box
Dim w As Worksheet, x As Long
Set w = Sheets("Sheet2")
x = w.Columns(19).Find(Me.GetNum.Value, lookat:=xlWhole, LookIn:=xlValues).Row
w.Range("S" & x).Delete
[Code] ............
View 2 Replies
View Related
Nov 19, 2009
I have a excel sheet which is completely formula driven and no macros in that.
I want to macro which can save that excel sheet to a specific location.
View 9 Replies
View Related
Aug 7, 2006
I have been trying to work this out by looking at other posts (mostly concerning Binary Access) but can't figure it! The source text files I am using can vary in length from 4,000 characters to well over 100,000 characters. However the data I am looking for always starts 40 characters from the end of the file and is 10 characters long! I need my macro to pick out this data and store it as a string (so it can be added to an array and exported to a worksheet later)
View 2 Replies
View Related
