Merging multiple workbook data into one sheet

sk0926

New Member
Joined
Jul 24, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
HI,

I am trying to copy paste data from multiple (over 300 workbooks) workbooks to one sheet so that I can analyze the data.
Below is what I found from the forum (with small modification like the range), which worked great except I need one modification.
The origin data sheets have data in range a31:l42 all the time, but cell B10 has date information. Ideally, I would like to have B10 date copied into column A 12 times so I would have the date next to my data (a31:l42, 12 rows) starting in column B.

Can anybody help me figuring this out?


Sub CopyRange()
Application.ScreenUpdating = False
Dim wkbDest As Workbook
Dim wkbSource As Workbook
Set wkbDest = ThisWorkbook
Dim LastRow As Long
Const strPath As String = "C:\Users\"
ChDir strPath
strExtension = Dir("*.xls*")
Do While strExtension <> ""
Set wkbSource = Workbooks.Open(strPath & strExtension)
With wkbSource
LastRow = .Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
.Sheets("Sheet1").Range("a31:l42").Copy wkbDest.Sheets("Master").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
.Close savechanges:=False
End With
strExtension = Dir
Loop
Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

denzo36

Board Regular
Joined
Sep 22, 2019
Messages
146
Office Version
  1. 365
Platform
  1. Windows
Why don't you use Power Query? If that data is in the same format in all workbooks then Power Query will achieve without much hassle
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,618
Office Version
  1. 2007
Platform
  1. Windows
Hi and welcome to MrExcel

I made several changes to your code, try the following:

VBA Code:
Sub CopyRange()
  Application.ScreenUpdating = False
  Dim wshDest As Worksheet, wkbSource As Workbook
  Dim lr As Long, strExtension As Variant
  
  Set wshDest = ThisWorkbook.Sheets("Master")
  Const strPath As String = "C:\Users\"
  ChDir strPath
  strExtension = Dir("*.xls*")
  
  Do While strExtension <> ""
    Set wkbSource = Workbooks.Open(strPath & strExtension)
    With wkbSource.Sheets("Sheet1")
      lr = wshDest.Range("B" & Rows.Count).End(3).Row + 1
      .Range("a31:l42").Copy wshDest.Range("B" & lr)
      wshDest.Range("A" & lr).Resize(12).Value = .Range("B10").Value
      wkbSource.Close savechanges:=False
    End With
    strExtension = Dir
  Loop
  
  Application.ScreenUpdating = True
End Sub
 

sk0926

New Member
Joined
Jul 24, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
DanteAmor, the code works perfect.

denzo36, I tried the Power Query for the first time and query does what I wanted as well.

Thank you both!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,618
Office Version
  1. 2007
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,127,336
Messages
5,624,093
Members
416,011
Latest member
chengkoonwing

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