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?