Peculiar Runtime Error 1004

[Jim Berglund]

I'm getting the error at the line with the asterisks. It works when I
replece the variable 'numberofRows' to a number. What am I missing, please?

Private Sub CommandButton2_Click() 'Load Data
Dim numberofRows As Integer, numberofColumns, nextCol As Integer

Application.ScreenUpdating = False
Workbooks("Replacement Strategy5.xls").Activate
Sheets("Raw").Activate
ActiveSheet.Columns("A:BM").Select
Selection.Clear
Selection.Delete Shift:=xlToLeft

Workbooks.Open Filename:= _
"D:\My Documents\ENRV5608\Reports\EMMS
Reports\EMMSReplacement3(EqptData).xls"
Workbooks("EMMSReplacement3(EqptData).xls").Activate
Sheets("Sheet1").Activate
ActiveSheet.Range("A1").Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
numberofColumns = ActiveCell.CurrentRegion.Columns.Count

** ActiveSheet.Range(Cells(1, 1), Cells(numberofRows,
numberofColumns)).Select**
nextCol = numberofColumns + 2
Selection.Copy
Windows("Replacement Strategy5.xls").Activate
Sheets("YTD BD").Activate
Range("A1").Select
ActiveSheet.Paste

Thanks for any help...

Jim Berglund

 

[Jake Marx]

Hi Jim,

What is the value of numberofRows when the error occurs? If you click Debug
and hover over the variable, you should see the value. If the number
exceeds what an Integer can hold (32,767), that would cause an error. When
working with rows in Excel, I typically use a Long to avoid these types of
problems.

If the value is something else, please post back with more info.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[David Adamson]

** ActiveSheet.Range(Cells(1, 1),
Cells(numberofRows,numberofColumns)).Select**

My suggestion would be to use the With command on the line as I have found
that Excel occassional goes mental when using the cells command. Especially
when calling a worksheet from another worksheet

So
With ActiveSheet
Set rng = Range(Cells(1, 1), Cells(numberofRows,numberofColumns)).Select
End with

 

[Jake Marx]

My suggestion would be to use the With command on the line as I have
found that Excel occassional goes mental when using the cells
command. Especially when calling a worksheet from another worksheet

So
With ActiveSheet
Set rng = Range(Cells(1, 1),
Cells(numberofRows,numberofColumns)).Select End with

Oh yeah - I didn't see that. But don't forget the dots:

With ActiveSheet
.Range(.Cells(1, 1), .Cells(numberofRows,numberofColumns)).Select
End With

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[David Adamson]

Thanks for picking that up

 

[Jim Berglund]

Thanks! This did the trick. Where did you get this arcane wizardry?

Jim

My suggestion would be to use the With command on the line as I have
found that Excel occassional goes mental when using the cells
command. Especially when calling a worksheet from another worksheet

So
With ActiveSheet
Set rng = Range(Cells(1, 1),
Cells(numberofRows,numberofColumns)).Select End with

Oh yeah - I didn't see that. But don't forget the dots:

With ActiveSheet
.Range(.Cells(1, 1), .Cells(numberofRows,numberofColumns)).Select
End With

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Jake Marx]

Hi Jim,

Thanks! This did the trick. Where did you get this arcane wizardry?

I don't know about wizardry, but...as David mentioned, this was an issue
with not fully-qualifying your range references. When you simply use Range
or Cells without specifying the Worksheet you intend, Excel assumes
different things depending on your situation.

For example, if you use Range("A1") from a Worksheet class module, it will
always refer to that Worksheet's cell A1. However, if you use Range("A1")
in a standard module (or a class module other than a Worksheet), Excel will
assume you want to work with cell A1 from the active Worksheet.

Since your code was on a Worksheet class module but you're intending to work
with ranges on another Worksheet, you have to be explicit. Using a With
statement is the easiest way to do this, as you don't have to type
Worksheets("Raw") each time.

Hopefully, that makes some sense. If not, let me know and I'll try to
expand on it a bit.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]