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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,624
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?
 

youngatheart

New Member
Joined
Apr 30, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,624
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
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,624
Check to make sure that this folder path is correct:
"C:\Downloads\by_wbs_extract\"
 

youngatheart

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

ADVERTISEMENT

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?
 

youngatheart

New Member
Joined
Apr 30, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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"
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,624
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.
 

Forum statistics

Threads
1,141,301
Messages
5,705,583
Members
421,400
Latest member
chakam

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
Top