12-12-13, 10:01 #1Registered User
- Join Date
- Dec 2013
Unanswered: Copy data from multiple sheets in a workbook to one sheet in the master workbook- VBA
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.
12-12-13, 11:21 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
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
12-02-16, 10:53 #3Registered User
- Join Date
- Oct 2016
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.
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 10:58. Reason: *wb.worksheets