Autofill a range

[clares]

I'm sure there must be someone that can help me. I posted a previous note but felt I needed to be more specific. My question is that I want to select a product from a range that I have exported from another system and I want to be able to print a set of labels, for example sometimes I want to print 5 labels containing the same text and other times I want to print 24 say. That I have no problem with. My problem is that I want to be able, by using a macro, to fill a range (row) with the same data. It's this that is used for exporting into word for labels i.e.
row1= AA4567 Pens
row2= AA4567 Pens
row3= AA4567 Pens
In the above example I will get three labels, but only want to enter data in row1, the macro will copy to the amount that I ask it to!

Can anyone suggest any ideas! Will be much appreciated

[Dave Hawley]

Hi clares

No too surte I have understood you but here 3 ways, 2 are that are static the other 1 is incremeting.



Sub DoThePenThing1()
Sheet1.Range("A2:A500") = "AA4567"
End Sub

Sub DoThePenThing2()
Sheet1.Range("A2:A500") = Sheet1.Range("A1")
End Sub

Sub DoThePenThing3()
Sheet1.Range("A2").AutoFill Destination:=Sheet1.Range("A2", Sheet1.Range("A500"))
End Sub

_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training


[ This Message was edited by: Dave Hawley on 2002-03-15 03:36 ]

[clares]

Thanks for that Dave, but using the last option I think will fill the range between A2 and A500 with the data I want, but sometimes I only want to fill to A4, how can I allow it to accept an input from the user?, then automactically fill the range?

Does that make sense?

If Ivan's suggestions are not what you're looking for, perhaps this will help.

I have assumed that your imported product number occupies one cell only. If this is not the case, post again.

In any event, try the following. First select a cell containing the product number required and then run the code. It should enter the product number in the number of rows in column A specified by the input box.

Dim nbr As Integer
If Selection.Cells.Count <> 1 Then
MsgBox "You must select one cell only"
Exit Sub
End If
nbr = Application.InputBox("Enter how many labels are required", Type:=1)
If nbr = False Then Exit Sub
Columns(1).ClearContents
Range("A1:A" & nbr) = Selection

[Dave Hawley]

Sure can, try this

Sub DoiT()
Dim rStopRange As Range
On Error Resume Next
Set rStopRange = Application.InputBox(Prompt:="Select the cell to stop at", Type:=8)

If rStopRange Is Nothing Then End
Range("A2").AutoFill Destination:=Range("A2", rStopRange)
End
End Sub

[clares]

These suggestions are really helpful. Thank you all for the responses. I do beleive I am able to make progress. If I have further queries as the small project moves on, I will post

[clares]

[ This Message was edited by: clares on 2002-03-15 09:02 ]