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?