Another Simple Error - More Info

[Jim Berglund]

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

 

[Chip Pearson]

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





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 Thomlinson]

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

 

[Tom Ogilvy]

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

"

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 Cone]

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


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

 

[Chip Pearson]

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



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 Berglund]

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

 

[Jim Cone]

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 Cone]

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

 

[Tom Ogilvy]

Who asked that?

--
Regards,
Tom Ogilvy

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