Results 1 to 3 of 3
\r\n \r\n\r\n \r\n \r\n
\r\n
\r\n
\r\n Hi,
\r\n
\r\nThis is my problem and i hope I can get help with this.
\r\nI 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.
\r\n
\r\nLet me use an example of 3 of my workbooks.
\r\nWorkbook 1 is named "Wema"
\r\nWorkbook 2 is named "Keystone"
\r\nWorkbook 3 is named "ADH"
\r\nnot 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.
\r\n
\r\nThe 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"
\r\n
\r\nWhat I want to do is use VBA to build a macro that will copy data from the workbooks and paste in the master file.
\r\nTo be exact, copy data from the "Wema" file and poast it in the "Wema" column of the Master sheet horizontally down.
\r\n
\r\nI 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.
\r\n
\r\nThanks
\r\n
\r\n
\r\n\r\n \r\n\r\n\r\n
\r\n \r\n \r\n \r\n \r\n \r\n
\r\n
\r\n
\r\n
\r\n \r\n \r\n \r\n \r\n \r\n Reply With Quote Reply With Quote \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n \r\n \r\n\r\n \r\n\r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n
\r\n
\r\n
\r\n\r\n'; pd[6608918] = '\r\n
  • \r\n
    \r\n
    \r\n \r\n \r\n \r\n \r\n #2\r\n \r\n \r\n \r\n \r\n \r\n
    \r\n\r\n
    \r\n
    \r\n \r\n
    \r\n \r\n
    \r\n \r\n \r\n
    \r\n healdem is offline\r\n\r\n \r\n \r\n Jaded Developer\r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n
    \r\n
    \r\n \r\n
    \r\n
    \r\n
    Join Date
    Nov 2004
    \r\n
    Location
    out on a limb
    \r\n \r\n
    Posts
    13,692
    \r\n \r\n
    \r\n \r\n
    Provided Answers: 59
    \r\n
    \r\n \r\n
    \r\n
    \r\n\r\n
    \r\n \r\n
    \r\n \r\n \r\n\r\n \r\n \r\n
    \r\n
    \r\n
    \r\n so is this a VB or VBA problem
    \n
    \nif it is VBA is it an Excel problem or one of the other applications in the Office family?
    \n
    \npersonally 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.
    \r\n
    \r\n
    \r\n\r\n \r\n\r\n\r\n
    \r\n \r\n \r\n \r\n \r\n
    I\'d rather be riding on the Tiger 800 or the Norton
    \r\n \r\n \r\n
    \r\n
    \r\n
    \r\n
    \r\n \r\n \r\n \r\n \r\n \r\n Reply With Quote Reply With Quote \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n \r\n \r\n\r\n \r\n\r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n
    \r\n
    \r\n
    \r\n
  • \r\n'; pd[6645989] = '\r\n
  • \r\n
    \r\n
    \r\n \r\n \r\n \r\n \r\n #3\r\n \r\n \r\n \r\n \r\n \r\n
    \r\n\r\n
    \r\n
    \r\n \r\n
    \r\n \r\n
    \r\n \r\n \r\n
    \r\n HotBreakfast is offline\r\n\r\n \r\n \r\n Registered User\r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n
    \r\n
    \r\n \r\n
    \r\n
    \r\n
    Join Date
    Oct 2016
    \r\n
    Location
    Minneapolis
    \r\n \r\n
    Posts
    4
    \r\n \r\n
    \r\n \r\n \r\n
    \r\n \r\n
    \r\n
    \r\n\r\n
    \r\n \r\n
    \r\n \r\n

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

    \r\n \r\n \r\n
    \r\n \r\n\r\n \r\n \r\n
    \r\n
    \r\n
    \r\n Hey man, I believe this code should do the trick.
    \n
    \nHeads 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.
    \n
    \n
    \r\n
    Code:
    \r\n
    Option Explicit\nSub Test()\n\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\n\'Workbook 1 is named "Wema"\n\'Workbook 2 is named "Keystone"\n\'Workbook 3 is named "ADH"\n\'\'\'\'\'\'added \'master\' workbook for this example\n\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\'\n\n\nDim FileName As String\nDim FileSpec As String\nDim i As Integer, FoundFiles As Integer\nDim FileList() As String\n\nFileSpec = "C:\\Users\\Gloria\\Documents\\Trade Data Capture\\" & "*.xls"\nFileName = Dir(FileSpec)\n\n\n\'Locate File\nIf FileName <> "" Then\n    FoundFiles = 1\n    ReDim Preserve FileList(1 To FoundFiles)\n        FileList(FoundFiles) = FileName\nElse\n    MsgBox "No Files found Matching " & FileSpec\n    Exit Sub\nEnd If\n\n\'Get other FileNames (in case you need them for future)\nDo\n    FileName = Dir\n    If FileName = "" Then Exit Sub\n    FoundFiles = FoundFiles + 1\n    ReDim Preserve FileList(1 To FoundFiles)\n    FileList(FoundFiles) = FileName & "*"\nLoop\n\n\'process targeted files in folder\nFor i = 1 To FoundFiles\n    If FileList(i) = "Wema" Or _\n    FileList(i) = "Keystone" Or _\n    FileList(i) = "ADH" Then\n        Call ProcessFiles(FileList(i))\n    End If\nNext i\nEnd Sub\n\n\nSub ProcessFiles(FileName As String)\nDim ws As Worksheet\nDim j As Integer, LC As Integer\n\n\nDim Master As Workbook: Set Master = "C:\\Users\\Gloria\\Documents\\Trade Data Capture\\Master.xls"\nDim WB As Workbook: Set WB = Workbooks.Open("C:\\Users\\Gloria\\Documents\\Trade Data Capture\\" & FileName & ".xls")\n\n\nFor Each ws In WB.worksheets\n    ws.Range("D1:D" & Rows.Count).Copy\n    \'if you place this code in the master workbook, it\'ll already be open, so bypass an error\n    On Error Resume Next\n    Workbooks.Open (Master)\n    Master.Activate \'keep focus on master workbook\n    LC = Cells(1, Columns.Count).End(xlToLeft).Column\n    For j = 1 To LC\n        \'match the correct column to paste the data\n        If Cells(1, j).Value = FileName Then\n            Cells(900, j).End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll\n        End If\n    Next j\n    \'focus moves back to WB\n    WB.Activate\nNext ws\n\'close wb without saving changes\nWB.Close False\nEnd Sub
    \r\n
    \r\n
    \r\n
    \r\n\r\n \r\n\r\n\r\n
    \r\n \r\n \r\n
    \r\n \r\n Last edited by HotBreakfast; 12-02-16 at 10:58.\r\n \r\n \r\n Reason: *wb.worksheets\r\n \r\n
    \r\n \r\n \r\n \r\n \r\n \r\n \r\n
    \r\n
    \r\n
    \r\n
    \r\n \r\n \r\n \r\n \r\n \r\n Reply With Quote Reply With Quote \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n \r\n\r\n \r\n \r\n\r\n \r\n\r\n \r\n \r\n\r\n \r\n \r\n \r\n \r\n \r\n \r\n
    \r\n
    \r\n
    \r\n
  • \r\n'; // next/previous post info pn[6608914] = "6645989,6608918"; pn[0] = ",6608914"; pn[6608918] = "6608914,6645989"; pn[6645989] = "6608918,6608914"; // cached usernames pu[0] = guestphrase; pu[336858] = "SuperKunle"; pu[92418] = "healdem"; pu[364482] = "HotBreakfast"; // -->

    Threaded View

    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

    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
    •