ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Method 'Range' of object '_Global' failed (https://www.excelbanter.com/excel-programming/376774-method-range-object-_global-failed.html)

Graham
Method 'Range' of object '_Global' failed
 
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
--
Graham

Bob Phillips
Method 'Range' of object '_Global' failed
 
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" wrote in message
...
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
--
Graham

Graham
Method 'Range' of object '_Global' failed
 
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" wrote:

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" wrote in message
...
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
--
Graham

Bob Phillips
Method 'Range' of object '_Global' failed
 
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" wrote in message
...
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" wrote:

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" wrote in message
...
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
--
Graham

Graham
Method 'Range' of object '_Global' failed
 
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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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
--
Graham


All times are GMT +1. The time now is 02:13 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com