Copy Paste Data from Multiple Files to Main Workbook

youngatheart

New Member
Joined
Apr 30, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi,

I need help in consolidating from multiple workbooks into one,
- Source Data - Starts from Row A5 to AC5 until 2nd to the last column *last column of the data is not needed*
- Master data - header is at Row 2, Paste data starting on Column B, now I need to identify the copied data in column A "Batch" on which batch it came from stating 1 if from first source data and so forth

I usually do this manually but data is thousands of rows to copy from 5-10 workbooks to one.

Hope you can help me on this to make this more efficient.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Is AC the last column or the 2nd to the last column?
I need to identify the copied data in column A "Batch" on which batch it came from stating 1 if from first source data and so forth
By this do you mean: enter a "1" for the first source file data in column A, "2" for the second source data file, etc.?

What is the full path to the folder containing the source data? What is the extension (xlsx, xlsm) of the source files? Are the source files the only files in that folder?
 
Upvote 0
Is AC the last column or the 2nd to the last column?

By this do you mean: enter a "1" for the first source file data in column A, "2" for the second source data file, etc.?

What is the full path to the folder containing the source data? What is the extension (xlsx, xlsm) of the source files? Are the source files the only files in that folder?
Column AC is the last column
Yes you are correct regarding the identifier, the identifier is not part of the data being copied but manual input
Source files are in one folder
Path would be: C:\Downloads\by_wbs_extract
Extension of source data is .xlsx
Sheet name of the source data is default to = 0ANALYSIS_PATTERN
Master Data Sheet name = RAW
 
Upvote 0
Try:
VBA Code:
Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, desWS As Worksheet, srcWB As Workbook, x As Long: x = 1
    Set desWS = ThisWorkbook.Sheets("RAW")
    Const strPath As String = "C:\Downloads\by_wbs_extract\"
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set srcWB = Workbooks.Open(strPath & strExtension)
        With srcWB.Sheets("0ANALYSIS_PATTERN")
            LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Range("A5:AB" & LastRow).Copy desWS.Cells(desWS.Rows.Count, "B").End(xlUp).Offset(1, 0)
            desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(LastRow - 4) = x
            x = x + 1
        End With
        srcWB.Close False
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Check to make sure that this folder path is correct:
"C:\Downloads\by_wbs_extract\"
 
Upvote 0
Check to make sure that this folder path is correct:
"C:\Downloads\by_wbs_extract\"
Hi,

Thank you for this. This does run but nothing happened/ nothing was copied.. What seems to be the problem?
 
Upvote 0
Hi,

Thank you for this. This does run but nothing happened/ nothing was copied.. What seems to be the problem?
I tried running the codes per line though these lines seems to be skipped?

"Const strPath As String = "C:\Users\pviloria\Downloads\by_wbs_extract""

and

"Set srcWB = Workbooks.Open(strPath & strExtension)
With srcWB.Sheets("0ANALYSIS_PATTERN")
LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
.Range("A5:AB" & LastRow).Copy desWS.Cells(desWS.Rows.Count, "B").End(xlUp).Offset(1, 0)
desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(LastRow - 4) = x
x = x + 1
End With
srcWB.Close False
strExtension = Dir
Loop"
 
Upvote 0
It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your "RAW" sheet and one of your "0ANALYSIS_PATTERN" sheets. Alternately, you could upload a copy of your Master file and a copy of at least one source file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file (de-sensitized if necessary) that you can post here.
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top