Results 1 to 10 of 10

copy data from multiple worksheets into master workbook

Threaded View

  1. #1
    Registered User
    Join Date
    05-09-2012
    Location
    scotland
    MS-Off Ver
    Excel 2003
    Posts
    5

    copy data from multiple worksheets into master workbook

    Hi there,
    I've seen this sort of answered on an old closed thread but i'm hoping someone can help me some with code.
    I wish to have a 'master.xlsx' which whe I run a macro, allows me to search and open another workbook 'Entry.xlsx'. The Entry workbook has a number of tabs 'data1', 'data2' that I wish to copy and paste from eache sheet and add to the 'master.xlsx'. The master has the same tabs 'data1' etc. The macro should be able to add data (it's basically compliling 'entry' sheets after every month so the master has the full year data).

    hope someone can help. The following code, opens the workbook, but does not copy the data??

    hope someone can help
    Sub wbcopy()
        Dim fn
        Dim wbFrom As Workbook
        Dim ws     As Worksheet
        Dim rCopy  As Range
        Dim sSht   As String
        On Error Resume Next
        fn = Application.GetOpenFilename  'can add parameters. See help for details.
        If fn = False Then
            MsgBox "Nothing Chosen", vbCritical, "Select workbook"
            'now that you have the  name, you can open it
        Else: Workbooks.Open fn
            'set variable to source workbook
            Set wbFrom = ActiveWorkbook
            For Each ws In wbFrom.Worksheets
                With ws
                    sSht = .Name
                    'determine range to copy
                    Set rCopy = .Range(.Cells(4, 1), .Cells(.Rows.Count, 3).End(xlUp))
                End With
                'copy to relevant sheet in master wb
                If Not rCopy Is Nothing Then rCopy.Copy _
                   ThisWorkbook.Worksheets(sSht).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
            Next ws
        End If
        On Error GoTo 0
        Set rCopy = Nothing
        Set wbFrom = Nothing
    End Sub
    Last edited by arlu1201; 05-09-2012 at 04:35 AM. Reason: Please put code tags in future.

Thread Information

Users Browsing this Thread

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

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