Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Error Message Copy method of worksheet class failed
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default 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?
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 644
Default 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?

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default 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?
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default 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?
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default 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?


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default 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?
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4
Default 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?
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default 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?


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Error 1004 Copy method of worksheet class failed Ayo Excel Discussion (Misc queries) 3 March 28th 08 02:05 PM
Run time error (Copy Method of Worksheet class failed) Karthik Bhat - Bangalore Excel Programming 5 January 3rd 06 11:43 AM
Run Time Error 1004 Copy method of Worksheet class failed Paul Hitchcock Excel Programming 2 May 4th 05 08:10 PM
Error Message "Select method of worksheet class failed" philc Excel Programming 5 May 4th 05 08:07 AM
Run-Time Error 1004 Copy method of worksheet class failed Don Lloyd Excel Programming 0 July 27th 04 07:27 PM


All times are GMT +1. The time now is 10:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement