Fill Down based on count in another sheet

I am using the following in Excel to copy a formula:

ThisWorkbook.Worksheets("Results 4").Range("3:2000").FillDown

I need to only copy the formula for a certain number of rows up to 2000.  
The number of rows I need is based on the number of rows I have in worksheet 
"Results Data" in column A.  So, if "Results Data" has data in column A2:A16, 
I will need to copy down the formula for all columns in worksheet "Results 4" 
to column 17.
0
Utf
4/27/2010 3:27:01 AM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
1290 Views

Similar Articles

[PageSpeed] 50

Sub FillFown()
Dim lStop As Long

With Worksheets("Results Data")
   lStop = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
Worksheets("Results 4").Range("3:" & lStop).FillDown
End Sub



-- 
Regards
Dave Hawley
www.ozgrid.com
"LostInNY" <LostInNY@discussions.microsoft.com> wrote in message 
news:5383DC34-ED60-4868-8AD5-C589640B635C@microsoft.com...
>I am using the following in Excel to copy a formula:
>
> ThisWorkbook.Worksheets("Results 4").Range("3:2000").FillDown
>
> I need to only copy the formula for a certain number of rows up to 2000.
> The number of rows I need is based on the number of rows I have in 
> worksheet
> "Results Data" in column A.  So, if "Results Data" has data in column 
> A2:A16,
> I will need to copy down the formula for all columns in worksheet "Results 
> 4"
> to column 17. 

0
ozgrid
4/27/2010 3:42:57 AM
Works great Dave Thanks!!

"ozgrid.com" wrote:

> Sub FillFown()
> Dim lStop As Long
> 
> With Worksheets("Results Data")
>    lStop = .Cells(.Rows.Count, "A").End(xlUp).Row
> End With
> Worksheets("Results 4").Range("3:" & lStop).FillDown
> End Sub
> 
> 
> 
> -- 
> Regards
> Dave Hawley
> www.ozgrid.com
> "LostInNY" <LostInNY@discussions.microsoft.com> wrote in message 
> news:5383DC34-ED60-4868-8AD5-C589640B635C@microsoft.com...
> >I am using the following in Excel to copy a formula:
> >
> > ThisWorkbook.Worksheets("Results 4").Range("3:2000").FillDown
> >
> > I need to only copy the formula for a certain number of rows up to 2000.
> > The number of rows I need is based on the number of rows I have in 
> > worksheet
> > "Results Data" in column A.  So, if "Results Data" has data in column 
> > A2:A16,
> > I will need to copy down the formula for all columns in worksheet "Results 
> > 4"
> > to column 17. 
> 
0
Utf
4/27/2010 4:18:01 AM
Reply:

Similar Artilces:

Move Outlook data to another computer
Purchased a new laptop. How do i backup and move outlook 2003 from my desktop to my laptop. Take a look here, it may help: http://www.slipstick.com/config/backup.htm --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, J.J asked: | Purchased a new laptop. How do i backup and move outlook | 2003 from my desktop to my laptop. ...

Sheet to close
Why won't this work? oldjayo Sub test() Dim wsShtToSave As Worksheet Set wsShtToSave = ActiveSheet If wsShtToSave= "Master" Then ActiveWorkbook.Save ActiveWorkbook.Close End If End Sub Activesheet.name= try this simpler version Sub CloseifMaster() If ActiveSheet.Name = "Master" Then With ActiveWorkbook 'one dot only .Save .Close End With End If End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "oldjay" <oldjay@discussions.microsoft.com> wrote in message news:...

Setting random number range based on query, not table
I have a "quiz" form that randomly pulls up questions from my table. It works fine, and I don't mind that it repeats questions. This allows me to sit and review for as long as I want. The problem is, that I want to use criteria in a query to limit the available questions. If I base this form on a query and limit the number of questions, the code I am using is still setting the maximum number as the number of records in my table. In the past, I had a similar database (which I have lost) that would open the form, set the selector to the last record available to the form,...

Form Based Auth.
Hi All, I have checked the option to use form based authentication but I don't get the web page login - still the pop up login box. I have restarted IIS. This is all internal at the moment till i get the forms working. Any ideas. Regards Joe. Do you have "require SSL" checked on the Exchange (and Public) virtual directories in IIS? Have you tried going to the https:// site manually? Forms-based auth only works with SSL. If you haven't required SSL and you go to the http page, it will still work just fine and will pop up the login box. -- Ben Winzenz Exchange MVP ...

Edit the Auto Fill in the To: field
On the Send new message window, is there a way to edit the auto fill list for the To: Field. when i start to type in a name in the To Field the auto fill will provide a list of names to choose from. the one i most use is on the bottom of the list. How do i edit the list to get the one i use most to the top? Do you use the others as well? Editing the list will remove them from the list. If you again use the names, they will be added back to the same position. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the Swen virus, all e-...

lookup and match from another excel file
Curious if someone can help. I have a dispatch log that I made for m company, I would like to set up a payroll system that will automaticl pull all rows that meet a certain name and date range. and put it o another new sheet for that weeks payroll if anyone has some ideas le me know, I can post an example of my dispatch log if needed. Thanks Sorry Sort of newb to this. Actually here is the file, I have macros on it for sorting Attachment filename: new daily log.xls Download attachment: http://www.excelforum.com/attachment.php?postid=63792 -- Message posted from ht...

Filtering, Counting and Denying
Sounds like a strange subject name, but here's what I want to do. I have a table called BOOKINGS. In it are all the daily car bookings - past, present and future. Each booking is identified by DEPARTMENT (1=DA, 2=Finance, 3=Exec, etc), and BOOKINGDATE. I want to be able to deny any department - other than 1-DA - the opportunity to book out one of our vehicles if the total external department count of bookings exceeds two on any given day. Not sure how to go about this, but I think I need to filter out the daily bookings by date, then count all records that are not department 1, and t...

How to make Unique coloumn in Excel sheet ?
I want to make one coloumn in excel sheet as unique, so no entry is repeated in that particular column. See http://www.contextures.com/xlDataVal07.html on this. HTH. Best wishes Harald "V JHANJI" <V JHANJI@discussions.microsoft.com> skrev i melding news:5D9465E8-EE69-4D59-8BD7-B0115D09145C@microsoft.com... > I want to make one coloumn in excel sheet as unique, so no entry is repeated > in that particular column. Hi check out http://www.cpearson.com/excel/NoDupEntry.htm for detailed instructions on how to do this. Cheers JulieD "V JHANJI" <V JHANJI...

Automatically display set text based on users composition
Hi, im trying to do something really simple, trouble is i dont know what the feature's called to be able to search for tips on how to do it. Basically in outlook messages, when a user begins writing a sentence e.g. "in the terms of" i need a tag to pop up that allows the user to press enter and then the remainder of what they will want to type in will be inserted in, its a yellow tag that comes up above the words. i dont know where it needs to be created and enabled. Cheers, Rhys. ...

To auto-fill using names no longer in address
Is there a way to remove the names for the auto fill feature that are no longer in the address book or contact list? If they are selected the properties for the entry states name is no longer valid Address Book entry. Thanks. State your Outlook version and what you mean by "autofill." Outlook has two different functions this could be: autoresolution or autocompletion. -- Russ Valentine [MVP-Outlook] "Ernie C" <anonymous@discussions.microsoft.com> wrote in message news:04e601c4913a$f07a15c0$7d02280a@phx.gbl... > Is there a way to remove the names for the au...

Detailed Time Sheet (overtime, comp time, vacation used)
I need to track in time, out time, in time, out time, overtime, vacation totals (received/used), comp received, comp used, and so. I would recommend to write all the headers on row 1, and start entering the data on row 2. Any column that is a calculation, enter a formula (for example, if column E is the difference of column D and C, enter "=D2-C2") and format the cells as you want the data to be presented (date/time, number, etc.) Hope this helps, Miguel. "Robert D. Sandersfeld" wrote: > I need to track in time, out time, in time, out time, overtime, vacation ...

Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook?
Wow! I tried this out, and it seems to work beautifully. It's odd, though, that I haven't seen this technique mentioned in any of the several Excel references that I've looked at. Basically, I have a workbook with several worksheets...one worksheet contains a large list with all the records. I wanted to set up the other worksheets to have certain functions...I wanted them to use only certain columns from the main list, and to contain only certain records from the main list that met specified conditions. This is the best solution I have found so far - i.e., creating database q...

Hide Picklist based on value of other picklist.
On the accounts form i have made a new picklist named Partner. In the picklist Customertype i have made a new entry called partner. When someone opens the form i want the picklist named partner to be hidden. When someone selects Partner in the Customertype picklist i want the picklist partner to be visible. If an other value is selected the fields needs to stay hidden. I know i have to make some code on the onchange of the field customertype and i need to make code on the onload of the page. I have tried multiple codes but i cant get it to work. Can someone please help me out please. Hi,...

Counting Null Values in a Report
I have a report that is grouped by people, then by Reason Closed. I want to count how many entries do not have a closed date. I tried the previous posts but could not get it to work. I have a group header for each person to group their categories together. Thanks. Hi Dea, Try: =Sum(-IsNull([ClosedDateFieldName])) Note the minus sign just after the first paranthesis. IsNull() returns a -1 when the item is null, otherwise 0. So by summing up the negative of each (-1)s you are in essence counting 1 for each null item. Alternatively you could move the minus...

create price list from excel sheet
I have been using Excel 2003 to manage a list of approximately 200 items. This is a very simple list comprising of four or five fields one of which is the price. I prefer to use Excel to manage this data because it then becomes very simple for me to use formulas in order to calculate prices after changes or additions to the items. I would like to produce a simple A5 flyer listing all the items together with their prices. What will be the best way to do this? I have explored the possibility of exporting the Excel sheet into something like Microsoft publisher but I am looking for a fa...

can the fill colour of a bar be tied to the data font colour data
...

How to type subscript and superscript in excel sheet
Hi Can anybody suggest me how to type the subscript and superscript in excel sheet?????????????????? Hi select the characters in the formula bar and goto 'Format - Cells' "Desh" wrote: > Hi > Can anybody suggest me how to type the subscript and superscript in excel > sheet?????????????????? > IF you do this a lot, John Walkenbach's free SuperSub add-in makes it much easier (it's included in his commercial PUP v5 add-in, as well): http://j-walk.com/ss/excel/files/supersub.htm In article <E060E171-0622-4DB1-8734-3B4727A3FF4E@microsoft.com&...

vendors and Items replicated to another company.
My customer has 2 companies under GP The 2 companies shared similar vendors and items. they wanted the system which allows vendors and items created in 1 company to be replicated to the next company. Anyone know any 3rd party module can manage! Example: A create a new item01 and it will automatic replicated to company B. Chris, There are 'master trigger' tools sold by Microsoft that can do this for Vendors, Customers and GL accounts - they're part of the Professional Services Tools Library. I don't know of a tool for inventory items. -- Victoria Yudin Dynamics GP MVP...

Move OL 2007 and all files to another computer.One on XP one on Vi
Is there a detailed "How Do I file" covering moving OL 2007 with BCM from one computer (that has a damaged or correpted C: Drive ) to another or new computer. I have have some very helpful advice from an earlier post a few adys ago but if a How do does exist that would be very helpful. Thanks in advance. Andy To backup and restore your Outlook data see; http://www.howto-outlook.com/howto/backupandrestore.htm For backing up and restoring BCM data see; http://office.microsoft.com/en-us/outlook/HP011134881033.aspx -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsof...

PaperSizes.Count returns 0
Hi All I have a Hp printer 1280 that is installed in windows 7. when I use following code to get Paper count. System.Drawing.Printing.PrinterSettings p = new System.Drawing.Printing.PrinterSettings(); MessageBox.Show(p.PaperSizes.Count.ToString()); it reutrns 0.I can't print from my programm but printer directly works fine. System.Drawing.Printing.PrinterSettings p = new System.Drawing.Printing.PrinterSettings(); p.PrinterName is returned correctly except PaperSize.Count. my program unser winXp works correctly but Win7. Is it possible it's caused by Printer ...

move page from one document to another
I need some "fast" help! I have never worked in Publisher before, but need to get a project done for an organization I work for. I need to take several pages from one document and paste them into another document. Both documents are already set up in Publisher. I want to basically "copy" from one document to another. HELP!! kdramirez <kdramirez@discussions.microsoft.com> wrote: >I need to take several pages from one document and paste them into another >document. Both documents are already set up in Publisher. I want to >basically "copy&qu...

Date and Auto Fill
I'm trying to auto fill a column with dates. Everytime I I use the auto fill command it changes the year rather than the day. I do have "day" selected in the fill series command. What am I doing wrong?? Sounds as though you're talking about Excel, not Access, the database product that's part of Office Professional, and the focus of this particular newsgroup. You'd be better off reposting to a newsgroup related to Excel. When you repost, it would probably help the responder to know what version of Excel you're using, and possibly the operating system on ...

Lock sheets automatically
Hi everyone, I have 26 fortnightly timesheets in a workbook for every staff and would like to lock each timesheet after some days.Can you please help me to fix my code as the follows: Select Case s_worksheet Case "TS1", "TS2", "TS3" If (DateDiff("d", B12, Date) > 6) Then s_worksheet.Unprotect "admin" s_worksheet.Range("C6:C12,D6:D12,F6:F12,G6:G10,H6:H10,I6:I10").Locked = True s_worksheet.Protect "admin" End If Case Else: End End Select Man...

Change text name based on file name
On my spreadsheet in cell A1 I have a text label containing the word "Department" What I want is when I save the file as Schedule 1 (Division).xls I want cell A1 change from "Department" to "Division" based upon what is within the parentheses in this example Any tips will be appreciated. Thank you. If I understand you, you want to return the portion of the wb's name that is within parentheses. If so, try this formula in cell A1. After doing a "Save As..." you will have to press F9 to update. Minimal testing: =MID(CELL("filename&quo...

Human Resources Mass roll-up of Age Based Life Insurance
Product Suggestion in brief: Age based insurance Premium need to roll-up on Jan 1st following their birthday, NOT on their birthdate. The below details the situation. Thanks. Girish ------------------------------ Hi, I have a question on Age-based Insurance Premium calculation for Life Insurance Set-up (Tools > Setup > Human Resources > Benefits and deductions > Life Insurance). Right now it is age based with age buckets. The premium per 10,000 for age bracket 1-29 is 0.24 and for age bracket 30-34 is 0.28. My birthday is 08/26/1975. My premium increased on 08/27/2005. ...