Error Message Copy method of worksheet class failed

P

Philip4946

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 Before:=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:=xlDatabase, 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 Before:=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?
 
D

Die_Another_Day

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
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 Before:=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:=xlDatabase, 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 Before:=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?
Click to expand...
 
T

Tom Ogilvy

Do you have any hidden sheets in Fileout?

--
Regards,
Tom Ogilvy


Philip4946 said:
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 Before:=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:=xlDatabase, 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 Before:=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?
Click to expand...
 
T

Tom Ogilvy

also, can you copy that sheet manually?

--
Regards,
Tom Ogilvy


Philip4946 said:
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 Before:=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:=xlDatabase, 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 Before:=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?
Click to expand...
 
P

Philip4946

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

Philip

Die_Another_Day said:
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
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 Before:=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:=xlDatabase, 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 Before:=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?
Click to expand...
Click to expand...
 
P

Philip4946

Tom,

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

Philip

Tom Ogilvy said:
Do you have any hidden sheets in Fileout?

--
Regards,
Tom Ogilvy


Philip4946 said:
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 Before:=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:=xlDatabase, 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 Before:=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?
Click to expand...
Click to expand...
 
P

Philip4946

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 said:
also, can you copy that sheet manually?

--
Regards,
Tom Ogilvy


Philip4946 said:
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 Before:=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:=xlDatabase, 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 Before:=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?
Click to expand...
Click to expand...
 
T

Tom Ogilvy

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 said:
Thanks, Charles, I'll try that when I'm back in the office....

Philip

Die_Another_Day said:
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
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 Before:=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:=xlDatabase, 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 Before:=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?
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

Runtime error 1004: Select method of worksheet class failed 3
Run Time error 1004 - Pastespecial method of range class failed. 3
Run-Time error '1004: Unable to get the PivotTables property of the worksheet class 4
Run-Time error 1004 in Autofill method of rangle class failed 2
Copy method of Worksheet class failed 4
Runtime Error '1004' - AddFields method of PivotTable class failed 1
Run-time error '1004': Select method of Range class failed 3
PasteSpecial Method of Range Class Failed Error 5

Top