+ Reply to Thread
Results 1 to 3 of 3

Copy Data From Multiple Workbooks to One Workbook

Hybrid View

  1. #1
    Registered User
    Join Date
    01-11-2011
    Location
    Michigan
    MS-Off Ver
    Excel 2007
    Posts
    38

    Copy Data From Multiple Workbooks to One Workbook

    I am trying to copy data from multiple other workbooks (in this example, just one wother workbook- "Machine Charting Entry.xlsm" ) into one main workbook ("OEE Charting.xlsm"). All of the data in the 'other' workbooks is layed out the same way as in OEE Charting, but has varying lengths of data. The problem that I am having is from an error when I try to paste the code into OEE Charting, there is an error "Object doesn't support this property or method" and I cannot find a way to fix it.

    Sub Import_Recorded_Data()    
        Dim wbk As Workbook
        Dim strOEEMaster As String
        Dim strMachEntry As String
        Dim strWeldEntry As String
        
        strOEEMaster = "G:\OEE\Master\OEE Charting.xlsm"
        strMachEntry = "G:\OEE\Machine\Machine Charting Entry.xlsm"
    
        ThisWorkbook.Sheets("Enter Data").Range("A2:AA60000,D7:E60000,H7:I60000,K7:U60000").ClearContents
        
        Set OEE = Workbooks("OEE Charting.xlsm")
        Set wbk = Workbooks.Open(strMachEntry)
        With wbk.Sheets("Entered Machine Data")
            Sheets("Entered Machine Data").Visible = True
            LastRowMCE = Sheets("Entered Machine Data").Range("A" & Rows.Count).End(xlUp).Row
            Range(Cells(2, 1), Cells(LastRowMCE, 27)).Copy
        End With
    
        Set wbk = ThisWorkbook
        With wbk.Sheets("Enter Data")
            OEE.Range("A2").Paste
        End With
        Workbooks("Machine Charting Entry.xlsm").Sheets("Entered Machine Data").Visible = False
        Workbooks("Machine Charting Entry.xlsm").Close (False)
    End Sub
    I have also tried using .range(cells(2,1),cells(LastRowMCE,27)).paste, but it still returns an error.
    Am I referencing the main workbook incorrectly?
    Thanks in advance

  2. #2
    Valued Forum Contributor StevenM's Avatar
    Join Date
    03-23-2008
    Location
    New Lenox, IL USA
    MS-Off Ver
    2007
    Posts
    910

    Re: Copy Data From Multiple Workbooks to One Workbook

    This is not making any sense:

    With wbk.Sheets("Enter Data")
            OEE.Range("A2").Paste
    End With
    With an "With ... End With" one expects a phrase which begins with a dot. For example, if you are referring to cell A2 in the worksheet "Enter Data" then:

    With wbk.Sheets("Enter Data")
            .Range("A2").Paste
    End With
    Range with a dot before it (and nothing else before the dot) goes with the With statement.

    Also, try PasteSpecial instead of Paste.

    With:

    Set OEE = Workbooks("OEE Charting.xlsm")
    You should then have a statement like:

    Dim OEE As Workbook
    As it is:
    OEE.Range("A2").Paste
    doesn't make any sense, because you need a worksheet (or sheet) statement between a workbook and a Range.

  3. #3
    Registered User
    Join Date
    01-11-2011
    Location
    Michigan
    MS-Off Ver
    Excel 2007
    Posts
    38

    Re: Copy Data From Multiple Workbooks to One Workbook

    I finally got it to work, thanks for the help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1