run-time error 438

Question

Hi. I'm new to VBA. I'm attempting to write code for a macro in Excel 2010 that copies a worksheet to a new workbook. Below is the code I currently have.

Sub ProcessFiles()

    Dim strPath As String

    Dim strFile As String

    Dim SourceWbk As Workbook

    Dim SourceWsht As Worksheet

    Dim SourceData As Range

    Dim TargetWsht As Worksheet

    Dim lngRow As Long

    strPath = "I:\EXPENDITURE SUMMARY FY1213.xls"

    Application.ScreenUpdating = False

    Set TargetWsht = ActiveSheet

    lngRow = 1

    Set SourceWbk = Workbooks.Open(strPath)

    Set SourceWsht = SourceWbk.Sheet(1)

    Set SourceData = SourceWsht.UsedRange    SourceData.Copy

    TargetWsht.Range("A" & lngRow).PasteSpecial xlPasteValues

    SourceWbk.Close SaveChanges:=False

    Application.CutCopyMode = False

    Application.ScreenUpdating = True

End Sub

When I run it I receive a Run-time error '438' Object doesn't support this property or method message.

The error occurs on the Bolded line in the above code. Any ideas?

Thank you so much in advance.

Answer 1

It would retain the formulae.

If you do not want them then use

    Dim strPath As String

     Dim strFile As String

     Dim SourceWbk As Workbook

     Dim SourceWsht As Worksheet

     Dim TargetWsht As Worksheet

     strPath = "I:\EXPENDITURE SUMMARY FY1213.xls"

     Application.ScreenUpdating = False

     Set TargetWsht = ActiveSheet

     Set SourceWbk = Workbooks.Open(strPath)

     Set SourceWsht = SourceWbk.Sheets(1)

     SourceWsht.UsedRange.Copy

     TargetWsht.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats

     SourceWbk.Close SaveChanges:=False

     Application.ScreenUpdating = True

If you do not want the top lefthand cell of the copied range to be in cell A1, replace that with the required cell reference.

Answer 2

That works great! Thank you again HansV!

Does the "Copy After:" retain the formulas or is there and easy way to PasteValues and Format?

Answer 3

Try this version:

Sub ProcessFiles()

    Dim strPath As String

    Dim strFile As String

    Dim SourceWbk As Workbook

    Dim SourceWsht As Worksheet

    Dim TargetWsht As Worksheet

    strPath = "I:\EXPENDITURE SUMMARY FY1213.xls"

    Application.ScreenUpdating = False

    Set TargetWsht = ActiveSheet

    Set SourceWbk = Workbooks.Open(strPath)

    Set SourceWsht = SourceWbk.Sheets(1)

    SourceWsht.Copy After:=TargetWsht

    SourceWbk.Close SaveChanges:=False

    Application.ScreenUpdating = True

End Sub

Answer 4

Thank you HansV. That works great. But since I'm new I didn't realize the formatting would not be copied, as well as 2 graphs. Is there a way to copy the entire worksheet to a new worksheet in a different workbook? Should this be a dfferent thread at this point?

Thank you again.

Answer 5

I suspect that you get the error on the line

     Set SourceWsht = SourceWbk.Sheet(1)

This should be

        Set SourceWsht = SourceWbk.Sheets(1)