Method 'Range' of object '_Global' failed

G

Guest

I am just developing this code to cut data from an intermediate workbook and
paste it into the main workbook. Eventually it will be run from Access, but
at the moment it is stand alone.

When I run it the error is
Run-time error '1004': Method 'Range' of object '_Global' failed

The problem line seems to be
Range(Cells(lLastRow, iLastCol)).Activate

The message above that line proves that the Sheetname, last column and last
row are correct - the range to be cut is also correctly highlighted

'Sub mUpdateStats(pvYr As Variant, pvMth As Variant)
Sub mUpdateStats()
Dim pvMth, pvYr As Variant
pvMth = 10
pvYr = 2006
Const csFileDir As String = "C:\Documents and
Settings\Graham\Documents\Access\PRNC\"
Dim sCurrSheet, sDataFile, sDataSheets, sRefsFile, sStatsFile As String
Dim vDataSheets As Variant

Dim iLastCol As Integer
Dim lLastRow As Long
Dim vCnt As Variant

sDataFile = "Data.xls"
'vDataSheets = "CommLearn, Enq, RefFrom, RefTo"
vDataSheets = "Enq, RefFrom, RefTo"
sStatsFile = "CLIContactStats" & pvYr & pvMth & ".xls"
'ActiveWorkbook.SaveAs Filename:=csFileDir & sStatsFile _
' , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
' ReadOnlyRecommended:=False, CreateBackup:=False

Workbooks.Open Filename:=csFileDir & sDataFile

For vCnt = 1 To fnNumEntries(vDataSheets, ",")
sCurrSheet = fnEntry(vCnt, vDataSheets, ",")
Windows(sDataFile).Activate
Sheets(sCurrSheet).Select
iLastCol = Cells.Find(What:="*", After:=[A1],
Searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
lLastRow = Cells.Find(What:="*", After:=[A1], Searchorder:=xlByRows,
searchdirection:=xlPrevious).Row
Range(Cells(1, 1), Cells(lLastRow, iLastCol)).Select
MsgBox sCurrSheet & " | " & lLastRow & " | " & iLastCol
Range(Cells(lLastRow, iLastCol)).Activate
Selection.Cut
Windows(sStatsFile).Activate
Sheets(sCurrSheet).Select
Range(Cells(1, 1)).Select
ActiveSheet.Paste
Next vCnt
End Sub
 
B

Bob Phillips

Haven't tested it but that line has redundancy. Try

Cells(lLastRow, iLastCol).Activate

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
G

Guest

Thanks Bob

That was it - so logical when it is pointed out.

Now there is another problem - first iteration is fine, but on the second
iteration the line
Sheets(sCurrSheet).Select
results in Run-time error '9': Subscript out of range

I put a message in before that line and the sheetname looks fine.

Any suggestions please.

Thanks again
--
Graham


Bob Phillips said:
Haven't tested it but that line has redundancy. Try

Cells(lLastRow, iLastCol).Activate

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Graham said:
I am just developing this code to cut data from an intermediate workbook and
paste it into the main workbook. Eventually it will be run from Access, but
at the moment it is stand alone.

When I run it the error is
Run-time error '1004': Method 'Range' of object '_Global' failed

The problem line seems to be
Range(Cells(lLastRow, iLastCol)).Activate

The message above that line proves that the Sheetname, last column and last
row are correct - the range to be cut is also correctly highlighted

'Sub mUpdateStats(pvYr As Variant, pvMth As Variant)
Sub mUpdateStats()
Dim pvMth, pvYr As Variant
pvMth = 10
pvYr = 2006
Const csFileDir As String = "C:\Documents and
Settings\Graham\Documents\Access\PRNC\"
Dim sCurrSheet, sDataFile, sDataSheets, sRefsFile, sStatsFile As String
Dim vDataSheets As Variant

Dim iLastCol As Integer
Dim lLastRow As Long
Dim vCnt As Variant

sDataFile = "Data.xls"
'vDataSheets = "CommLearn, Enq, RefFrom, RefTo"
vDataSheets = "Enq, RefFrom, RefTo"
sStatsFile = "CLIContactStats" & pvYr & pvMth & ".xls"
'ActiveWorkbook.SaveAs Filename:=csFileDir & sStatsFile _
' , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
' ReadOnlyRecommended:=False, CreateBackup:=False

Workbooks.Open Filename:=csFileDir & sDataFile

For vCnt = 1 To fnNumEntries(vDataSheets, ",")
sCurrSheet = fnEntry(vCnt, vDataSheets, ",")
Windows(sDataFile).Activate
Sheets(sCurrSheet).Select
iLastCol = Cells.Find(What:="*", After:=[A1],
Searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
lLastRow = Cells.Find(What:="*", After:=[A1], Searchorder:=xlByRows,
searchdirection:=xlPrevious).Row
Range(Cells(1, 1), Cells(lLastRow, iLastCol)).Select
MsgBox sCurrSheet & " | " & lLastRow & " | " & iLastCol
Range(Cells(lLastRow, iLastCol)).Activate
Selection.Cut
Windows(sStatsFile).Activate
Sheets(sCurrSheet).Select
Range(Cells(1, 1)).Select
ActiveSheet.Paste
Next vCnt
End Sub
Click to expand...
Click to expand...
 
B

Bob Phillips

Are you sure that sCurrSheet doesn't have leading or trailing spaces?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Graham said:
Thanks Bob

That was it - so logical when it is pointed out.

Now there is another problem - first iteration is fine, but on the second
iteration the line
Sheets(sCurrSheet).Select
results in Run-time error '9': Subscript out of range

I put a message in before that line and the sheetname looks fine.

Any suggestions please.

Thanks again
--
Graham


Bob Phillips said:
Haven't tested it but that line has redundancy. Try

Cells(lLastRow, iLastCol).Activate

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Graham said:
I am just developing this code to cut data from an intermediate
Click to expand...
workbook
and
paste it into the main workbook. Eventually it will be run from
Click to expand...
Access,
but
at the moment it is stand alone.

When I run it the error is
Run-time error '1004': Method 'Range' of object '_Global' failed

The problem line seems to be
Range(Cells(lLastRow, iLastCol)).Activate

The message above that line proves that the Sheetname, last column and last
row are correct - the range to be cut is also correctly highlighted

'Sub mUpdateStats(pvYr As Variant, pvMth As Variant)
Sub mUpdateStats()
Dim pvMth, pvYr As Variant
pvMth = 10
pvYr = 2006
Const csFileDir As String = "C:\Documents and
Settings\Graham\Documents\Access\PRNC\"
Dim sCurrSheet, sDataFile, sDataSheets, sRefsFile, sStatsFile As String
Dim vDataSheets As Variant

Dim iLastCol As Integer
Dim lLastRow As Long
Dim vCnt As Variant

sDataFile = "Data.xls"
'vDataSheets = "CommLearn, Enq, RefFrom, RefTo"
vDataSheets = "Enq, RefFrom, RefTo"
sStatsFile = "CLIContactStats" & pvYr & pvMth & ".xls"
'ActiveWorkbook.SaveAs Filename:=csFileDir & sStatsFile _
' , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
' ReadOnlyRecommended:=False, CreateBackup:=False

Workbooks.Open Filename:=csFileDir & sDataFile

For vCnt = 1 To fnNumEntries(vDataSheets, ",")
sCurrSheet = fnEntry(vCnt, vDataSheets, ",")
Windows(sDataFile).Activate
Sheets(sCurrSheet).Select
iLastCol = Cells.Find(What:="*", After:=[A1],
Searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
lLastRow = Cells.Find(What:="*", After:=[A1], Searchorder:=xlByRows,
searchdirection:=xlPrevious).Row
Range(Cells(1, 1), Cells(lLastRow, iLastCol)).Select
MsgBox sCurrSheet & " | " & lLastRow & " | " & iLastCol
Range(Cells(lLastRow, iLastCol)).Activate
Selection.Cut
Windows(sStatsFile).Activate
Sheets(sCurrSheet).Select
Range(Cells(1, 1)).Select
ActiveSheet.Paste
Next vCnt
End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks once again Bob

That was exactly it.

To put spaces in a delimited list is more than enough proof that I have been
away from professional coding for some time.

How would us hackers survive without a forum like this.
--
Graham


Bob Phillips said:
Are you sure that sCurrSheet doesn't have leading or trailing spaces?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Graham said:
Thanks Bob

That was it - so logical when it is pointed out.

Now there is another problem - first iteration is fine, but on the second
iteration the line
Sheets(sCurrSheet).Select
results in Run-time error '9': Subscript out of range

I put a message in before that line and the sheetname looks fine.

Any suggestions please.

Thanks again
--
Graham


Bob Phillips said:
Haven't tested it but that line has redundancy. Try

Cells(lLastRow, iLastCol).Activate

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I am just developing this code to cut data from an intermediate workbook
and
paste it into the main workbook. Eventually it will be run from Access,
but
at the moment it is stand alone.

When I run it the error is
Run-time error '1004': Method 'Range' of object '_Global' failed

The problem line seems to be
Range(Cells(lLastRow, iLastCol)).Activate

The message above that line proves that the Sheetname, last column and
last
row are correct - the range to be cut is also correctly highlighted

'Sub mUpdateStats(pvYr As Variant, pvMth As Variant)
Sub mUpdateStats()
Dim pvMth, pvYr As Variant
pvMth = 10
pvYr = 2006
Const csFileDir As String = "C:\Documents and
Settings\Graham\Documents\Access\PRNC\"
Dim sCurrSheet, sDataFile, sDataSheets, sRefsFile, sStatsFile As
String
Dim vDataSheets As Variant

Dim iLastCol As Integer
Dim lLastRow As Long
Dim vCnt As Variant

sDataFile = "Data.xls"
'vDataSheets = "CommLearn, Enq, RefFrom, RefTo"
vDataSheets = "Enq, RefFrom, RefTo"
sStatsFile = "CLIContactStats" & pvYr & pvMth & ".xls"
'ActiveWorkbook.SaveAs Filename:=csFileDir & sStatsFile _
' , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
' ReadOnlyRecommended:=False, CreateBackup:=False

Workbooks.Open Filename:=csFileDir & sDataFile

For vCnt = 1 To fnNumEntries(vDataSheets, ",")
sCurrSheet = fnEntry(vCnt, vDataSheets, ",")
Windows(sDataFile).Activate
Sheets(sCurrSheet).Select
iLastCol = Cells.Find(What:="*", After:=[A1],
Searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
lLastRow = Cells.Find(What:="*", After:=[A1],
Searchorder:=xlByRows,
searchdirection:=xlPrevious).Row
Range(Cells(1, 1), Cells(lLastRow, iLastCol)).Select
MsgBox sCurrSheet & " | " & lLastRow & " | " & iLastCol
Range(Cells(lLastRow, iLastCol)).Activate
Selection.Cut
Windows(sStatsFile).Activate
Sheets(sCurrSheet).Select
Range(Cells(1, 1)).Select
ActiveSheet.Paste
Next vCnt
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

method 'cells' of object '_global' failed 0
Modify Claus code "Sub Array_Var_Column_Sort()" 27
Calling Add-in functions from VBA 5
ResetUsedRange after a Print-preview causing wierd errors. 2
Method 'Range" of object'_Global' failed 2
conso macro 0
Assign Range to Array then use JOIN function 5
Error 1004 Method 'Range' of object '_Global' failed 4

Top