I also tried...

Set SourceRange = Worksheets("Power Units").Range("A3:AV3")
Set fillRange = Worksheets("Power Units").Range(Cells(4, 1), Cells(i, 48))
SourceRange.AutoFill Destination:=fillRange

With an error in the middle line. Apparently I'm having problems using a variable in the Range.

Jim

Jim,

You'll get this error if Power Units is not the active sheet. Try

With Worksheets("Power Units")
Set fillRange = .Range(.Cells(4, 1), .Cells(i, 48))
End With

Note the leading periods before 'Range' and 'Cells'.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"Jim Berglund" wrote in message
news:5n5Qd.388476$Xk.289660@pd7tw3no...
I also tried...

Set SourceRange = Worksheets("Power Units").Range("A3:AV3")
Set fillRange = Worksheets("Power Units").Range(Cells(4, 1),
Cells(i, 48))
SourceRange.AutoFill Destination:=fillRange

With an error in the middle line. Apparently I'm having problems
using a variable in the Range.

Jim

What is i? I assume and integer. If it is not an integer this will crash...
This code is definitely a lot closer to correct than your last code...

HTH

"Jim Berglund" wrote:

I also tried...

Set SourceRange = Worksheets("Power Units").Range("A3:AV3")
Set fillRange = Worksheets("Power Units").Range(Cells(4, 1), Cells(i, 48))
SourceRange.AutoFill Destination:=fillRange

With an error in the middle line. Apparently I'm having problems using a variable in the Range

Try

With Worksheets("Power Units")
Set SourceRange = .Range("A3:AV3")
Set fillRange = .Range(.Cells(3, 1), .Cells(i, 48))
End With

SourceRange.AutoFill Destination:=fillRange

The fillrange has to include the source

From help:
Destination Required Range object. The cells to be filled. The destination
must include the source range

--
Tom Ogilvy

"

Jim Berglund" wrote in message
news:5n5Qd.388476$Xk.289660@pd7tw3no...
I also tried...

Set SourceRange = Worksheets("Power Units").Range("A3:AV3")
Set fillRange = Worksheets("Power Units").Range(Cells(4, 1), Cells(i, 48))
SourceRange.AutoFill Destination:=fillRange

With an error in the middle line. Apparently I'm having problems using a
variable in the Range.

Jim

Jim,

The fill range must include the source range, so the following works...
'---------------------------------------
Sub FillTest()
Dim SourceRange As Excel.Range
Dim FillRange As Excel.Range
Dim i As Long
i = 10
Set SourceRange = Worksheets("Power Units").Range("A3:AV3")
Set FillRange = Worksheets("Power Units").Range(Cells(3, 1), Cells(i, 48))
SourceRange.AutoFill Destination:=FillRange
End Sub
'---------------------------------------
Regards,
Jim Cone
San Francisco, USA


"Jim Berglund" wrote in message news:5n5Qd.388476$Xk.289660@pd7tw3no...
I also tried...
Set SourceRange = Worksheets("Power Units").Range("A3:AV3")
Set fillRange = Worksheets("Power Units").Range(Cells(4, 1), Cells(i, 48))
SourceRange.AutoFill Destination:=fillRange
With an error in the middle line. Apparently I'm having problems using a variable in the Range.
Jim

Thanks, Jim

I plugged it in and it doesn't work, either.
Here's the current Code...
Private Sub CommandButton1_Click()
Dim response
Dim newWO, i, j As Integer

response = InputBox("Enter the number of WO's you want to create")
ScreenUpdating = False

Sheets("Power Units").Activate
i = (response - 2) * 0.4

Set SourceRange = Worksheets("Power Units").Range("A3:AV3")
Set fillRange = Worksheets("Power Units").Range(Cells(3, 1), Cells(i, 48))
SourceRange.AutoFill Destination:=fillRange
....
End sub

It gives a 1004 error at he Purple Line: (Set fillRange = Worksheets("Power Units").Range(Cells(3, 1), Cells(i, 48))

I can't figgure it out...

Jim



"Jim Cone" wrote in message ...
Jim,

The fill range must include the source range, so the following works...
'---------------------------------------
Sub FillTest()
Dim SourceRange As Excel.Range
Dim FillRange As Excel.Range
Dim i As Long
i = 10
Set SourceRange = Worksheets("Power Units").Range("A3:AV3")
Set FillRange = Worksheets("Power Units").Range(Cells(3, 1), Cells(i, 48))
SourceRange.AutoFill Destination:=FillRange
End Sub
'---------------------------------------
Regards,
Jim Cone
San Francisco, USA


"Jim Berglund" wrote in message news:5n5Qd.388476$Xk.289660@pd7tw3no...
I also tried...
Set SourceRange = Worksheets("Power Units").Range("A3:AV3")
Set fillRange = Worksheets("Power Units").Range(Cells(4, 1), Cells(i, 48))
SourceRange.AutoFill Destination:=fillRange
With an error in the middle line. Apparently I'm having problems using a variable in the Range.
Jim

Jim,

The problem most likely lies in the fact that PowerUnits is not
the active sheet. The problem this causes is that your Cells
reference points to the active sheet, not the Power Units sheet.
You need to qualify your Cells references with the worksheet
name.

Set fillRange = Worksheets("Power Units").Range(Worksheets("Power
Units").Cells(3, 1), _
Worksheets("Power Units").Cells(i, 48))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Jim Berglund" wrote in message
news:Ah6Qd.388958$Xk.342554@pd7tw3no...
Thanks, Jim

I plugged it in and it doesn't work, either.
Here's the current Code...
Private Sub CommandButton1_Click()
Dim response
Dim newWO, i, j As Integer

response = InputBox("Enter the number of WO's you want to
create")
ScreenUpdating = False

Sheets("Power Units").Activate
i = (response - 2) * 0.4

Set SourceRange = Worksheets("Power Units").Range("A3:AV3")
Set fillRange = Worksheets("Power Units").Range(Cells(3, 1),
Cells(i, 48))
SourceRange.AutoFill Destination:=fillRange
....
End sub

It gives a 1004 error at he Purple Line: (Set fillRange =
Worksheets("Power Units").Range(Cells(3, 1), Cells(i, 48))

I can't figgure it out...

Jim



"Jim Cone" wrote in message
...
Jim,

The fill range must include the source range, so the following
works...
'---------------------------------------
Sub FillTest()
Dim SourceRange As Excel.Range
Dim FillRange As Excel.Range
Dim i As Long
i = 10
Set SourceRange = Worksheets("Power Units").Range("A3:AV3")
Set FillRange = Worksheets("Power Units").Range(Cells(3, 1),
Cells(i, 48))
SourceRange.AutoFill Destination:=FillRange
End Sub
'---------------------------------------
Regards,
Jim Cone
San Francisco, USA


"Jim Berglund" wrote in message
news:5n5Qd.388476$Xk.289660@pd7tw3no...
I also tried...
Set SourceRange = Worksheets("Power Units").Range("A3:AV3")
Set fillRange = Worksheets("Power Units").Range(Cells(4, 1),
Cells(i, 48))
SourceRange.AutoFill Destination:=fillRange
With an error in the middle line. Apparently I'm having
problems using a variable in the Range.
Jim

That did it!
Here's the final code...

Private Sub CommandButton1_Click()
Dim response
Dim newWO, j As Integer
Dim SourceRange As Excel.Range
Dim FillRange As Excel.Range
Dim i As Long

ScreenUpdating = False

response = InputBox("Enter the number of WO's you want to create")
i = (response - 2) * 0.4

Sheets("Power Units").Activate
With Sheets("Power Units")
Set SourceRange = .Range("A3:AV3")
Set FillRange = .Range(.Cells(4, 1), .Cells(i, 48))
SourceRange.AutoFill Destination:=FillRange
End With
End Sub

Thanks a lot Chip - and the rest of you.

Jim


"Chip Pearson" wrote in message
...
Jim,

The problem most likely lies in the fact that PowerUnits is not the active
sheet. The problem this causes is that your Cells reference points to the
active sheet, not the Power Units sheet. You need to qualify your Cells
references with the worksheet name.

Set fillRange = Worksheets("Power Units").Range(Worksheets("Power
Units").Cells(3, 1), _
Worksheets("Power Units").Cells(i, 48))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Jim Berglund" wrote in message
news:Ah6Qd.388958$Xk.342554@pd7tw3no...
Thanks, Jim

I plugged it in and it doesn't work, either.
Here's the current Code...
Private Sub CommandButton1_Click()
Dim response
Dim newWO, i, j As Integer

response = InputBox("Enter the number of WO's you want to create")
ScreenUpdating = False

Sheets("Power Units").Activate
i = (response - 2) * 0.4

Set SourceRange = Worksheets("Power Units").Range("A3:AV3")
Set fillRange = Worksheets("Power Units").Range(Cells(3, 1), Cells(i, 48))
SourceRange.AutoFill Destination:=fillRange
...
End sub

It gives a 1004 error at he Purple Line: (Set fillRange =
Worksheets("Power Units").Range(Cells(3, 1), Cells(i, 48))

I can't figgure it out...

Jim



"Jim Cone" wrote in message
...
Jim,

The fill range must include the source range, so the following works...
'---------------------------------------
Sub FillTest()
Dim SourceRange As Excel.Range
Dim FillRange As Excel.Range
Dim i As Long
i = 10
Set SourceRange = Worksheets("Power Units").Range("A3:AV3")
Set FillRange = Worksheets("Power Units").Range(Cells(3, 1), Cells(i,
48))
SourceRange.AutoFill Destination:=FillRange
End Sub
'---------------------------------------
Regards,
Jim Cone
San Francisco, USA


"Jim Berglund" wrote in message
news:5n5Qd.388476$Xk.289660@pd7tw3no...
I also tried...
Set SourceRange = Worksheets("Power Units").Range("A3:AV3")
Set fillRange = Worksheets("Power Units").Range(Cells(4, 1), Cells(i,
48))
SourceRange.AutoFill Destination:=fillRange
With an error in the middle line. Apparently I'm having problems using a
variable in the Range.
Jim

Jim,

Your final code doesn't work for me.
For one thing, "ScreenUpdating" should be Application.ScreenUpdating.

Here is my reponse to your previous message, I think it still applies...

In addition to Chip Pearson's recommendation,
the following two changes allowed it to work for me...

1. Change the Button to one from the forms toolbar.
Why?
2. Change the SourceRange to two rows from one row.
Unless you do that you just get the same data repeated.
3. Make sure the i variable is at least 6, or you won't have
enough rows in which to place the filled data...

'---------------------------------------------
Sub FillTest2()
Dim SourceRange As Excel.Range
Dim FillRange As Excel.Range
Dim response
Dim newWO, i, j As Integer

response = InputBox("Enter the number of WO's you want to create")
Application.ScreenUpdating = False

Sheets("Power Units").Activate
i = (response - 2) * 0.4
If i < 6 Then i = 6

With Worksheets("Power Units")
Set SourceRange = .Range("A3:AV4")
Set FillRange = .Range(.Cells(3, 1), .Cells(i, 48))
SourceRange.AutoFill Destination:=FillRange
End With

Application.ScreenUpdating = True

End Sub
'-------------------------------------------

Regards,
Jim Cone
San Francisco, USA


"Jim Berglund" wrote in message
news:DR6Qd.389081$8l.150868@pd7tw1no...
That did it!
Here's the final code...

Private Sub CommandButton1_Click()
Dim response
Dim newWO, j As Integer
Dim SourceRange As Excel.Range
Dim FillRange As Excel.Range
Dim i As Long

ScreenUpdating = False

response = InputBox("Enter the number of WO's you want to create")
i = (response - 2) * 0.4

Sheets("Power Units").Activate
With Sheets("Power Units")
Set SourceRange = .Range("A3:AV3")
Set FillRange = .Range(.Cells(4, 1), .Cells(i, 48))
SourceRange.AutoFill Destination:=FillRange
End With
End Sub

Thanks a lot Chip - and the rest of you.

Jim


"Chip Pearson" wrote in message
...
Jim,

The problem most likely lies in the fact that PowerUnits is not the active
sheet. The problem this causes is that your Cells reference points to the
active sheet, not the Power Units sheet. You need to qualify your Cells
references with the worksheet name.

Set fillRange = Worksheets("Power Units").Range(Worksheets("Power
Units").Cells(3, 1), _
Worksheets("Power Units").Cells(i, 48))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

Setting the "Take Focus on Click" property to False,
allows the activeX CommandButton to work in XL97.



"Jim Cone" wrote in message
...
Jim,

Your final code doesn't work for me.
For one thing, "ScreenUpdating" should be Application.ScreenUpdating.

Here is my reponse to your previous message, I think it still applies...

In addition to Chip Pearson's recommendation,
the following two changes allowed it to work for me...

1. Change the Button to one from the forms toolbar.
Why?
2. Change the SourceRange to two rows from one row.
Unless you do that you just get the same data repeated.
3. Make sure the i variable is at least 6, or you won't have
enough rows in which to place the filled data...

'---------------------------------------------
Sub FillTest2()
Dim SourceRange As Excel.Range
Dim FillRange As Excel.Range
Dim response
Dim newWO, i, j As Integer

response = InputBox("Enter the number of WO's you want to create")
Application.ScreenUpdating = False

Sheets("Power Units").Activate
i = (response - 2) * 0.4
If i < 6 Then i = 6

With Worksheets("Power Units")
Set SourceRange = .Range("A3:AV4")
Set FillRange = .Range(.Cells(3, 1), .Cells(i, 48))
SourceRange.AutoFill Destination:=FillRange
End With

Application.ScreenUpdating = True

End Sub
'-------------------------------------------

Regards,
Jim Cone
San Francisco, USA

Who asked that?

--
Regards,
Tom Ogilvy

"Jim Cone" wrote in message
...
Setting the "Take Focus on Click" property to False,
allows the activeX CommandButton to work in XL97.



"Jim Cone" wrote in message
...
Jim,

Your final code doesn't work for me.
For one thing, "ScreenUpdating" should be Application.ScreenUpdating.

Here is my reponse to your previous message, I think it still applies...

In addition to Chip Pearson's recommendation,
the following two changes allowed it to work for me...

1. Change the Button to one from the forms toolbar.
Why?
2. Change the SourceRange to two rows from one row.
Unless you do that you just get the same data repeated.
3. Make sure the i variable is at least 6, or you won't have
enough rows in which to place the filled data...

'---------------------------------------------
Sub FillTest2()
Dim SourceRange As Excel.Range
Dim FillRange As Excel.Range
Dim response
Dim newWO, i, j As Integer

response = InputBox("Enter the number of WO's you want to create")
Application.ScreenUpdating = False

Sheets("Power Units").Activate
i = (response - 2) * 0.4
If i < 6 Then i = 6

With Worksheets("Power Units")
Set SourceRange = .Range("A3:AV4")
Set FillRange = .Range(.Cells(3, 1), .Cells(i, 48))
SourceRange.AutoFill Destination:=FillRange
End With

Application.ScreenUpdating = True

End Sub
'-------------------------------------------

Regards,
Jim Cone
San Francisco, USA