Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2013
    Posts
    1

    Unanswered: Copy data from multiple sheets in a workbook to one sheet in the master workbook- VBA

    Hi,

    This is my problem and i hope I can get help with this.
    I have about 10-15 files in a Folder, here is the link to the folder "C:\Users\Gloria\Documents\Trade Data Capture". This folder also includes the Master workbook. Each workbook has 3 tabs "Inter-member Trades", "Client Trades" and "Trades with CBN". All the data I need to copy is in Column D of all the workbooks. The master file has one worksheet "Trade Data Master". Each Column from Col D to Col M represents the workbooks I want to copy data from individually.

    Let me use an example of 3 of my workbooks.
    Workbook 1 is named "Wema"
    Workbook 2 is named "Keystone"
    Workbook 3 is named "ADH"
    not forgetting that each of the workbooks have the same number of tabs which is 3 with names Inter-member Trades", "Client Trades" and "Trades with CBN" all identical in the 3 tabs.

    The master sheet in turn has a horizontal listing of "Inter-member Trades", "Client Trades" and "Trades with CBN" in one column while stretched across is "Wema", "Keystone" and "ADH"

    What I want to do is use VBA to build a macro that will copy data from the workbooks and paste in the master file.
    To be exact, copy data from the "Wema" file and poast it in the "Wema" column of the Master sheet horizontally down.

    I really hope that I have explained what I want properly, If I have not, kindly clarify. Need urgent help as I have been battling with this for over a week.

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so is this a VB or VBA problem

    if it is VBA is it an Excel problem or one of the other applications in the Office family?

    personally I'd strongly recommend that you ditch the idea of using spreadsheets to store primary data. they are a great design paradigm to tinker about, try what if scenario's they are iffy to be the reference point for data. especially financial data.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2016
    Location
    Minneapolis
    Posts
    4

    Thumbs up Copy Data from Multiple Sheets in Workbooks in a Folder and Paste range into Master

    Hey man, I believe this code should do the trick.

    Heads up, I wrote it but haven't tested. Any bugs should be fairly simple to quickly edit if need be. Hope this helps you out.

    Code:
    Option Explicit
    Sub Test()
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Workbook 1 is named "Wema"
    'Workbook 2 is named "Keystone"
    'Workbook 3 is named "ADH"
    ''''''added 'master' workbook for this example
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    
    Dim FileName As String
    Dim FileSpec As String
    Dim i As Integer, FoundFiles As Integer
    Dim FileList() As String
    
    FileSpec = "C:\Users\Gloria\Documents\Trade Data Capture\" & "*.xls"
    FileName = Dir(FileSpec)
    
    
    'Locate File
    If FileName <> "" Then
        FoundFiles = 1
        ReDim Preserve FileList(1 To FoundFiles)
            FileList(FoundFiles) = FileName
    Else
        MsgBox "No Files found Matching " & FileSpec
        Exit Sub
    End If
    
    'Get other FileNames (in case you need them for future)
    Do
        FileName = Dir
        If FileName = "" Then Exit Sub
        FoundFiles = FoundFiles + 1
        ReDim Preserve FileList(1 To FoundFiles)
        FileList(FoundFiles) = FileName & "*"
    Loop
    
    'process targeted files in folder
    For i = 1 To FoundFiles
        If FileList(i) = "Wema" Or _
        FileList(i) = "Keystone" Or _
        FileList(i) = "ADH" Then
            Call ProcessFiles(FileList(i))
        End If
    Next i
    End Sub
    
    
    Sub ProcessFiles(FileName As String)
    Dim ws As Worksheet
    Dim j As Integer, LC As Integer
    
    
    Dim Master As Workbook: Set Master = "C:\Users\Gloria\Documents\Trade Data Capture\Master.xls"
    Dim WB As Workbook: Set WB = Workbooks.Open("C:\Users\Gloria\Documents\Trade Data Capture\" & FileName & ".xls")
    
    
    For Each ws In WB.worksheets
        ws.Range("D1:D" & Rows.Count).Copy
        'if you place this code in the master workbook, it'll already be open, so bypass an error
        On Error Resume Next
        Workbooks.Open (Master)
        Master.Activate 'keep focus on master workbook
        LC = Cells(1, Columns.Count).End(xlToLeft).Column
        For j = 1 To LC
            'match the correct column to paste the data
            If Cells(1, j).Value = FileName Then
                Cells(900, j).End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
            End If
        Next j
        'focus moves back to WB
        WB.Activate
    Next ws
    'close wb without saving changes
    WB.Close False
    End Sub
    Last edited by HotBreakfast; 12-02-16 at 11:58. Reason: *wb.worksheets

Tags for this Thread

Posting Permissions

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