ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Error Message Copy method of worksheet class failed (https://www.excelbanter.com/excel-programming/372920-error-message-copy-method-worksheet-class-failed.html)

Philip4946
Error Message Copy method of worksheet class failed
 
I am writing VBA code in Excel to pull in data from a number of workbooks,
and have come across the "Copy method of worksheet class failed" message.

This is the section of code where I'm having trouble....

'========== Stage Eight ========== Copies TAS Nominal Ledger
Transactions for 4000/999 ====================================

Application.StatusBar = "CRM Import Stage Eight"
Application.ScreenUpdating = False

Workbooks.Open Filename:=Dir & FileIn8
Dim TASSht As Object
TASShtName = "TAS NL 4000 " & YYMM & " " & MMM
Set TASSht = Sheets(TASShtName)
Sheets(TASShtName).Copy Befo=Workbooks(FileOut).Sheets(4)
Workbooks(FileIn8).Close SaveChanges:=False

Columns("F:F").Insert Shift:=xlToRight
Range("F1").FormulaR1C1 = "Descr"

Set c = Range("a2")
NumRows = 0
Range(Range("A2"), ActiveCell.SpecialCells(xlLastCell)).Select
For Each area In Selection.Areas
NumRows = area.Rows.Count
Next area
For x = 1 To NumRows - 4
c.Offset(0, 5).FormulaR1C1 = "=Left(rc[+1],7)"
c.Offset(0, 5).Value = c.Offset(0, 5).Value
Set c = c.Offset(1, 0)
Next x

Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'" & TASShtName & "'!R1C1:R" & NumRows - 3 & "C9").CreatePivotTable
TableDestination:="", _
TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array( _
"Descr", "Data"), ColumnFields:="Code"
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Dr Amount")
.Orientation = xlDataField
.Caption = "Sum of Dr Amount"
.Position = 1
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Cr Amount")
.Orientation = xlDataField
.Caption = "Sum of Cr Amount"
.Function = xlSum
End With
Sheets("Sheet2").Name = "TAS Ledger Analysis"


Application.ScreenUpdating = True
'========== Stage Nine ========== Copies CRM Trial Balance
================================================== =============

Application.StatusBar = "CRM Import Stage Eight"
Application.ScreenUpdating = False

Workbooks.Open Filename:=Dir & FileIn9
Dim CRMSht As Object
CRMShtName = "CRM TB " & YYMM & " " & MMM
MsgBox ("Sheet Name =:" & CRMShtName & ":")
Set CRMSht = Sheets(CRMShtName)

Sheets(CRMShtName).Copy Befo=Workbooks(FileOut).Sheets(8)

Workbooks(FileIn9).Close SaveChanges:=False

Application.ScreenUpdating = True


The 'Stage Eight@ section works fine, but Section Nine falls over with the
error message - but the code is virtually identical except for diffecernt
file/sheet names.

How can I avoid getting this message?

Die_Another_Day
Error Message Copy method of worksheet class failed
 
Philip, run the code, then choose debug when it errors, if the
Immediate Window is not already visible press Ctrl + G.
Type:
?Sheets(CRMShtName).Name
and press Enter, then type:
?Workbooks(FileOut).Sheets(8).Name
and press Enter.
the first statement should return the name of the Source Worksheet, and
the second should return the name of Sheet 8 from the FileOut File.
Let me know if either one of those statements returns an error or
returns a value that you didn't expect.

Charles

Philip4946 wrote:
I am writing VBA code in Excel to pull in data from a number of workbooks,
and have come across the "Copy method of worksheet class failed" message.

This is the section of code where I'm having trouble....

'========== Stage Eight ========== Copies TAS Nominal Ledger
Transactions for 4000/999 ====================================

Application.StatusBar = "CRM Import Stage Eight"
Application.ScreenUpdating = False

Workbooks.Open Filename:=Dir & FileIn8
Dim TASSht As Object
TASShtName = "TAS NL 4000 " & YYMM & " " & MMM
Set TASSht = Sheets(TASShtName)
Sheets(TASShtName).Copy Befo=Workbooks(FileOut).Sheets(4)
Workbooks(FileIn8).Close SaveChanges:=False

Columns("F:F").Insert Shift:=xlToRight
Range("F1").FormulaR1C1 = "Descr"

Set c = Range("a2")
NumRows = 0
Range(Range("A2"), ActiveCell.SpecialCells(xlLastCell)).Select
For Each area In Selection.Areas
NumRows = area.Rows.Count
Next area
For x = 1 To NumRows - 4
c.Offset(0, 5).FormulaR1C1 = "=Left(rc[+1],7)"
c.Offset(0, 5).Value = c.Offset(0, 5).Value
Set c = c.Offset(1, 0)
Next x

Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'" & TASShtName & "'!R1C1:R" & NumRows - 3 & "C9").CreatePivotTable
TableDestination:="", _
TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array( _
"Descr", "Data"), ColumnFields:="Code"
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Dr Amount")
.Orientation = xlDataField
.Caption = "Sum of Dr Amount"
.Position = 1
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Cr Amount")
.Orientation = xlDataField
.Caption = "Sum of Cr Amount"
.Function = xlSum
End With
Sheets("Sheet2").Name = "TAS Ledger Analysis"


Application.ScreenUpdating = True
'========== Stage Nine ========== Copies CRM Trial Balance
================================================== =============

Application.StatusBar = "CRM Import Stage Eight"
Application.ScreenUpdating = False

Workbooks.Open Filename:=Dir & FileIn9
Dim CRMSht As Object
CRMShtName = "CRM TB " & YYMM & " " & MMM
MsgBox ("Sheet Name =:" & CRMShtName & ":")
Set CRMSht = Sheets(CRMShtName)

Sheets(CRMShtName).Copy Befo=Workbooks(FileOut).Sheets(8)

Workbooks(FileIn9).Close SaveChanges:=False

Application.ScreenUpdating = True


The 'Stage Eight@ section works fine, but Section Nine falls over with the
error message - but the code is virtually identical except for diffecernt
file/sheet names.

How can I avoid getting this message?

Tom Ogilvy
Error Message Copy method of worksheet class failed
 
Do you have any hidden sheets in Fileout?

--
Regards,
Tom Ogilvy


"Philip4946" wrote:

I am writing VBA code in Excel to pull in data from a number of workbooks,
and have come across the "Copy method of worksheet class failed" message.

This is the section of code where I'm having trouble....

'========== Stage Eight ========== Copies TAS Nominal Ledger
Transactions for 4000/999 ====================================

Application.StatusBar = "CRM Import Stage Eight"
Application.ScreenUpdating = False

Workbooks.Open Filename:=Dir & FileIn8
Dim TASSht As Object
TASShtName = "TAS NL 4000 " & YYMM & " " & MMM
Set TASSht = Sheets(TASShtName)
Sheets(TASShtName).Copy Befo=Workbooks(FileOut).Sheets(4)
Workbooks(FileIn8).Close SaveChanges:=False

Columns("F:F").Insert Shift:=xlToRight
Range("F1").FormulaR1C1 = "Descr"

Set c = Range("a2")
NumRows = 0
Range(Range("A2"), ActiveCell.SpecialCells(xlLastCell)).Select
For Each area In Selection.Areas
NumRows = area.Rows.Count
Next area
For x = 1 To NumRows - 4
c.Offset(0, 5).FormulaR1C1 = "=Left(rc[+1],7)"
c.Offset(0, 5).Value = c.Offset(0, 5).Value
Set c = c.Offset(1, 0)
Next x

Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'" & TASShtName & "'!R1C1:R" & NumRows - 3 & "C9").CreatePivotTable
TableDestination:="", _
TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array( _
"Descr", "Data"), ColumnFields:="Code"
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Dr Amount")
.Orientation = xlDataField
.Caption = "Sum of Dr Amount"
.Position = 1
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Cr Amount")
.Orientation = xlDataField
.Caption = "Sum of Cr Amount"
.Function = xlSum
End With
Sheets("Sheet2").Name = "TAS Ledger Analysis"


Application.ScreenUpdating = True
'========== Stage Nine ========== Copies CRM Trial Balance
================================================== =============

Application.StatusBar = "CRM Import Stage Eight"
Application.ScreenUpdating = False

Workbooks.Open Filename:=Dir & FileIn9
Dim CRMSht As Object
CRMShtName = "CRM TB " & YYMM & " " & MMM
MsgBox ("Sheet Name =:" & CRMShtName & ":")
Set CRMSht = Sheets(CRMShtName)

Sheets(CRMShtName).Copy Befo=Workbooks(FileOut).Sheets(8)

Workbooks(FileIn9).Close SaveChanges:=False

Application.ScreenUpdating = True


The 'Stage Eight@ section works fine, but Section Nine falls over with the
error message - but the code is virtually identical except for diffecernt
file/sheet names.

How can I avoid getting this message?

Tom Ogilvy
Error Message Copy method of worksheet class failed
 
also, can you copy that sheet manually?

--
Regards,
Tom Ogilvy


"Philip4946" wrote:

I am writing VBA code in Excel to pull in data from a number of workbooks,
and have come across the "Copy method of worksheet class failed" message.

This is the section of code where I'm having trouble....

'========== Stage Eight ========== Copies TAS Nominal Ledger
Transactions for 4000/999 ====================================

Application.StatusBar = "CRM Import Stage Eight"
Application.ScreenUpdating = False

Workbooks.Open Filename:=Dir & FileIn8
Dim TASSht As Object
TASShtName = "TAS NL 4000 " & YYMM & " " & MMM
Set TASSht = Sheets(TASShtName)
Sheets(TASShtName).Copy Befo=Workbooks(FileOut).Sheets(4)
Workbooks(FileIn8).Close SaveChanges:=False

Columns("F:F").Insert Shift:=xlToRight
Range("F1").FormulaR1C1 = "Descr"

Set c = Range("a2")
NumRows = 0
Range(Range("A2"), ActiveCell.SpecialCells(xlLastCell)).Select
For Each area In Selection.Areas
NumRows = area.Rows.Count
Next area
For x = 1 To NumRows - 4
c.Offset(0, 5).FormulaR1C1 = "=Left(rc[+1],7)"
c.Offset(0, 5).Value = c.Offset(0, 5).Value
Set c = c.Offset(1, 0)
Next x

Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'" & TASShtName & "'!R1C1:R" & NumRows - 3 & "C9").CreatePivotTable
TableDestination:="", _
TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array( _
"Descr", "Data"), ColumnFields:="Code"
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Dr Amount")
.Orientation = xlDataField
.Caption = "Sum of Dr Amount"
.Position = 1
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Cr Amount")
.Orientation = xlDataField
.Caption = "Sum of Cr Amount"
.Function = xlSum
End With
Sheets("Sheet2").Name = "TAS Ledger Analysis"


Application.ScreenUpdating = True
'========== Stage Nine ========== Copies CRM Trial Balance
================================================== =============

Application.StatusBar = "CRM Import Stage Eight"
Application.ScreenUpdating = False

Workbooks.Open Filename:=Dir & FileIn9
Dim CRMSht As Object
CRMShtName = "CRM TB " & YYMM & " " & MMM
MsgBox ("Sheet Name =:" & CRMShtName & ":")
Set CRMSht = Sheets(CRMShtName)

Sheets(CRMShtName).Copy Befo=Workbooks(FileOut).Sheets(8)

Workbooks(FileIn9).Close SaveChanges:=False

Application.ScreenUpdating = True


The 'Stage Eight@ section works fine, but Section Nine falls over with the
error message - but the code is virtually identical except for diffecernt
file/sheet names.

How can I avoid getting this message?

Philip4946
Error Message Copy method of worksheet class failed
 
Thanks, Charles, I'll try that when I'm back in the office....

Philip

"Die_Another_Day" wrote:

Philip, run the code, then choose debug when it errors, if the
Immediate Window is not already visible press Ctrl + G.
Type:
?Sheets(CRMShtName).Name
and press Enter, then type:
?Workbooks(FileOut).Sheets(8).Name
and press Enter.
the first statement should return the name of the Source Worksheet, and
the second should return the name of Sheet 8 from the FileOut File.
Let me know if either one of those statements returns an error or
returns a value that you didn't expect.

Charles

Philip4946 wrote:
I am writing VBA code in Excel to pull in data from a number of workbooks,
and have come across the "Copy method of worksheet class failed" message.

This is the section of code where I'm having trouble....

'========== Stage Eight ========== Copies TAS Nominal Ledger
Transactions for 4000/999 ====================================

Application.StatusBar = "CRM Import Stage Eight"
Application.ScreenUpdating = False

Workbooks.Open Filename:=Dir & FileIn8
Dim TASSht As Object
TASShtName = "TAS NL 4000 " & YYMM & " " & MMM
Set TASSht = Sheets(TASShtName)
Sheets(TASShtName).Copy Befo=Workbooks(FileOut).Sheets(4)
Workbooks(FileIn8).Close SaveChanges:=False

Columns("F:F").Insert Shift:=xlToRight
Range("F1").FormulaR1C1 = "Descr"

Set c = Range("a2")
NumRows = 0
Range(Range("A2"), ActiveCell.SpecialCells(xlLastCell)).Select
For Each area In Selection.Areas
NumRows = area.Rows.Count
Next area
For x = 1 To NumRows - 4
c.Offset(0, 5).FormulaR1C1 = "=Left(rc[+1],7)"
c.Offset(0, 5).Value = c.Offset(0, 5).Value
Set c = c.Offset(1, 0)
Next x

Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'" & TASShtName & "'!R1C1:R" & NumRows - 3 & "C9").CreatePivotTable
TableDestination:="", _
TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array( _
"Descr", "Data"), ColumnFields:="Code"
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Dr Amount")
.Orientation = xlDataField
.Caption = "Sum of Dr Amount"
.Position = 1
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Cr Amount")
.Orientation = xlDataField
.Caption = "Sum of Cr Amount"
.Function = xlSum
End With
Sheets("Sheet2").Name = "TAS Ledger Analysis"


Application.ScreenUpdating = True
'========== Stage Nine ========== Copies CRM Trial Balance
================================================== =============

Application.StatusBar = "CRM Import Stage Eight"
Application.ScreenUpdating = False

Workbooks.Open Filename:=Dir & FileIn9
Dim CRMSht As Object
CRMShtName = "CRM TB " & YYMM & " " & MMM
MsgBox ("Sheet Name =:" & CRMShtName & ":")
Set CRMSht = Sheets(CRMShtName)

Sheets(CRMShtName).Copy Befo=Workbooks(FileOut).Sheets(8)

Workbooks(FileIn9).Close SaveChanges:=False

Application.ScreenUpdating = True


The 'Stage Eight@ section works fine, but Section Nine falls over with the
error message - but the code is virtually identical except for diffecernt
file/sheet names.

How can I avoid getting this message?

Philip4946
Error Message Copy method of worksheet class failed
 
Tom,

No, I don't have any hidden sheets...

Philip

"Tom Ogilvy" wrote:

Do you have any hidden sheets in Fileout?

--
Regards,
Tom Ogilvy


"Philip4946" wrote:

I am writing VBA code in Excel to pull in data from a number of workbooks,
and have come across the "Copy method of worksheet class failed" message.

This is the section of code where I'm having trouble....

'========== Stage Eight ========== Copies TAS Nominal Ledger
Transactions for 4000/999 ====================================

Application.StatusBar = "CRM Import Stage Eight"
Application.ScreenUpdating = False

Workbooks.Open Filename:=Dir & FileIn8
Dim TASSht As Object
TASShtName = "TAS NL 4000 " & YYMM & " " & MMM
Set TASSht = Sheets(TASShtName)
Sheets(TASShtName).Copy Befo=Workbooks(FileOut).Sheets(4)
Workbooks(FileIn8).Close SaveChanges:=False

Columns("F:F").Insert Shift:=xlToRight
Range("F1").FormulaR1C1 = "Descr"

Set c = Range("a2")
NumRows = 0
Range(Range("A2"), ActiveCell.SpecialCells(xlLastCell)).Select
For Each area In Selection.Areas
NumRows = area.Rows.Count
Next area
For x = 1 To NumRows - 4
c.Offset(0, 5).FormulaR1C1 = "=Left(rc[+1],7)"
c.Offset(0, 5).Value = c.Offset(0, 5).Value
Set c = c.Offset(1, 0)
Next x

Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'" & TASShtName & "'!R1C1:R" & NumRows - 3 & "C9").CreatePivotTable
TableDestination:="", _
TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array( _
"Descr", "Data"), ColumnFields:="Code"
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Dr Amount")
.Orientation = xlDataField
.Caption = "Sum of Dr Amount"
.Position = 1
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Cr Amount")
.Orientation = xlDataField
.Caption = "Sum of Cr Amount"
.Function = xlSum
End With
Sheets("Sheet2").Name = "TAS Ledger Analysis"


Application.ScreenUpdating = True
'========== Stage Nine ========== Copies CRM Trial Balance
================================================== =============

Application.StatusBar = "CRM Import Stage Eight"
Application.ScreenUpdating = False

Workbooks.Open Filename:=Dir & FileIn9
Dim CRMSht As Object
CRMShtName = "CRM TB " & YYMM & " " & MMM
MsgBox ("Sheet Name =:" & CRMShtName & ":")
Set CRMSht = Sheets(CRMShtName)

Sheets(CRMShtName).Copy Befo=Workbooks(FileOut).Sheets(8)

Workbooks(FileIn9).Close SaveChanges:=False

Application.ScreenUpdating = True


The 'Stage Eight@ section works fine, but Section Nine falls over with the
error message - but the code is virtually identical except for diffecernt
file/sheet names.

How can I avoid getting this message?

Philip4946
Error Message Copy method of worksheet class failed
 
Yes, I can, without any problems!

Incidently, the sheet I'm copying is small, compared to the workbook I'm
copying to.

Philip

"Tom Ogilvy" wrote:

also, can you copy that sheet manually?

--
Regards,
Tom Ogilvy


"Philip4946" wrote:

I am writing VBA code in Excel to pull in data from a number of workbooks,
and have come across the "Copy method of worksheet class failed" message.

This is the section of code where I'm having trouble....

'========== Stage Eight ========== Copies TAS Nominal Ledger
Transactions for 4000/999 ====================================

Application.StatusBar = "CRM Import Stage Eight"
Application.ScreenUpdating = False

Workbooks.Open Filename:=Dir & FileIn8
Dim TASSht As Object
TASShtName = "TAS NL 4000 " & YYMM & " " & MMM
Set TASSht = Sheets(TASShtName)
Sheets(TASShtName).Copy Befo=Workbooks(FileOut).Sheets(4)
Workbooks(FileIn8).Close SaveChanges:=False

Columns("F:F").Insert Shift:=xlToRight
Range("F1").FormulaR1C1 = "Descr"

Set c = Range("a2")
NumRows = 0
Range(Range("A2"), ActiveCell.SpecialCells(xlLastCell)).Select
For Each area In Selection.Areas
NumRows = area.Rows.Count
Next area
For x = 1 To NumRows - 4
c.Offset(0, 5).FormulaR1C1 = "=Left(rc[+1],7)"
c.Offset(0, 5).Value = c.Offset(0, 5).Value
Set c = c.Offset(1, 0)
Next x

Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'" & TASShtName & "'!R1C1:R" & NumRows - 3 & "C9").CreatePivotTable
TableDestination:="", _
TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array( _
"Descr", "Data"), ColumnFields:="Code"
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Dr Amount")
.Orientation = xlDataField
.Caption = "Sum of Dr Amount"
.Position = 1
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Cr Amount")
.Orientation = xlDataField
.Caption = "Sum of Cr Amount"
.Function = xlSum
End With
Sheets("Sheet2").Name = "TAS Ledger Analysis"


Application.ScreenUpdating = True
'========== Stage Nine ========== Copies CRM Trial Balance
================================================== =============

Application.StatusBar = "CRM Import Stage Eight"
Application.ScreenUpdating = False

Workbooks.Open Filename:=Dir & FileIn9
Dim CRMSht As Object
CRMShtName = "CRM TB " & YYMM & " " & MMM
MsgBox ("Sheet Name =:" & CRMShtName & ":")
Set CRMSht = Sheets(CRMShtName)

Sheets(CRMShtName).Copy Befo=Workbooks(FileOut).Sheets(8)

Workbooks(FileIn9).Close SaveChanges:=False

Application.ScreenUpdating = True


The 'Stage Eight@ section works fine, but Section Nine falls over with the
error message - but the code is virtually identical except for diffecernt
file/sheet names.

How can I avoid getting this message?

Tom Ogilvy
Error Message Copy method of worksheet class failed
 
Don't get your hopes up.

for the first test, you would have failed on one of these lines:

MsgBox ("Sheet Name =:" & CRMShtName & ":")
Set CRMSht = Sheets(CRMShtName)


for the second test, you would have gotten a subscipt out of range error.

Have a great day!

--
Regards,
Tom Ogilvy




"Philip4946" wrote:

Thanks, Charles, I'll try that when I'm back in the office....

Philip

"Die_Another_Day" wrote:

Philip, run the code, then choose debug when it errors, if the
Immediate Window is not already visible press Ctrl + G.
Type:
?Sheets(CRMShtName).Name
and press Enter, then type:
?Workbooks(FileOut).Sheets(8).Name
and press Enter.
the first statement should return the name of the Source Worksheet, and
the second should return the name of Sheet 8 from the FileOut File.
Let me know if either one of those statements returns an error or
returns a value that you didn't expect.

Charles

Philip4946 wrote:
I am writing VBA code in Excel to pull in data from a number of workbooks,
and have come across the "Copy method of worksheet class failed" message.

This is the section of code where I'm having trouble....

'========== Stage Eight ========== Copies TAS Nominal Ledger
Transactions for 4000/999 ====================================

Application.StatusBar = "CRM Import Stage Eight"
Application.ScreenUpdating = False

Workbooks.Open Filename:=Dir & FileIn8
Dim TASSht As Object
TASShtName = "TAS NL 4000 " & YYMM & " " & MMM
Set TASSht = Sheets(TASShtName)
Sheets(TASShtName).Copy Befo=Workbooks(FileOut).Sheets(4)
Workbooks(FileIn8).Close SaveChanges:=False

Columns("F:F").Insert Shift:=xlToRight
Range("F1").FormulaR1C1 = "Descr"

Set c = Range("a2")
NumRows = 0
Range(Range("A2"), ActiveCell.SpecialCells(xlLastCell)).Select
For Each area In Selection.Areas
NumRows = area.Rows.Count
Next area
For x = 1 To NumRows - 4
c.Offset(0, 5).FormulaR1C1 = "=Left(rc[+1],7)"
c.Offset(0, 5).Value = c.Offset(0, 5).Value
Set c = c.Offset(1, 0)
Next x

Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'" & TASShtName & "'!R1C1:R" & NumRows - 3 & "C9").CreatePivotTable
TableDestination:="", _
TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array( _
"Descr", "Data"), ColumnFields:="Code"
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Dr Amount")
.Orientation = xlDataField
.Caption = "Sum of Dr Amount"
.Position = 1
.Function = xlSum
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Cr Amount")
.Orientation = xlDataField
.Caption = "Sum of Cr Amount"
.Function = xlSum
End With
Sheets("Sheet2").Name = "TAS Ledger Analysis"


Application.ScreenUpdating = True
'========== Stage Nine ========== Copies CRM Trial Balance
================================================== =============

Application.StatusBar = "CRM Import Stage Eight"
Application.ScreenUpdating = False

Workbooks.Open Filename:=Dir & FileIn9
Dim CRMSht As Object
CRMShtName = "CRM TB " & YYMM & " " & MMM
MsgBox ("Sheet Name =:" & CRMShtName & ":")
Set CRMSht = Sheets(CRMShtName)

Sheets(CRMShtName).Copy Befo=Workbooks(FileOut).Sheets(8)

Workbooks(FileIn9).Close SaveChanges:=False

Application.ScreenUpdating = True


The 'Stage Eight@ section works fine, but Section Nine falls over with the
error message - but the code is virtually identical except for diffecernt
file/sheet names.

How can I avoid getting this message?


All times are GMT +1. The time now is 01:28 AM.

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