Automate Copying Data Across Excel Workbooks with VBA

In this video, I'll show you how to automate the process of copying data ranges multiple workbooks and pasting them into the active sheet of your workbook using VBA. The data will be copies from a specified ranges from each selected workbook and worksheet and appending the data in the active sheet.

[@mostainbillah]

It is important tutorial for excel learner

[@mostainbillah]

Thank you

[@ExcelWithBrainBell]

You're welcome

[@mostainbillah]

Many many Thanks for Making tutorial For My request

[@ExcelWithBrainBell]

You are most welcome

[@mostainbillah]

May Almighty Bless you.

[@mostainbillah]

Dear Sir ,
It is High Quality & scholars type Video Tutorial , I am proud To getting The Tutorial , I am grateful to you , The Video Tutorial Helpful For me ,
Your Code & tutorial is Ok ,
So , A Small Request To You , To Develop Some Option :
My request Is :
1-Set source range (in each workbook) from - Row 17 to 24 in Blank cell between Two data
(End(xlUp).Row + 1) from Row 17 to 24
(Note: Rows 17 to 24 are blank cells and there have Data in rows 25 to 28 , )
2-when blank cell ( 17 to 24 ) will fill up then paste will in last offset row ( Present code destination is will okay )
it is your free time ,Non Emergency , so important
Thanks
Mostain Billah
Dhaka, Bangladesh.

[@ExcelWithBrainBell]

Here’s how you can modify the current VBA code:
Sub CopyRangeFromSelectedWorkbooks()
Dim wb As Workbook
Dim sourceRange As Range
Dim destRange As Range
Dim selectedFiles As Variant
Dim lastRow As Long
Dim activeSheet As Worksheet
' Set active sheet
Set activeSheet = ThisWorkbook.ActiveSheet
' Ask user to select workbooks
selectedFiles = Application.GetOpenFilename( _
FileFilter:="Excel Files (*.xls; *.xlsx), *.xls; *.xlsx", _
MultiSelect:=True, Title:="Select Workbooks")
' Check if the user selected any files
If IsArray(selectedFiles) Then
For i = LBound(selectedFiles) To UBound(selectedFiles)
' Open each selected workbook
Set wb = Workbooks.Open(selectedFiles(i))
' Set source range from Row 17 to Row 24 in each workbook
Set sourceRange = wb.Sheets(1).Range("A17:D24")
' Check if rows 17 to 24 are blank in the active sheet
If Application.WorksheetFunction.CountA(activeSheet.Range("A17:D24")) = 0 Then
' If rows 17 to 24 are blank, paste there
Set destRange = activeSheet.Range("A17:D24")
Else
' If rows 17 to 24 are filled, find the next empty row
lastRow = activeSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
Set destRange = activeSheet.Range("A" & lastRow & ":D" & lastRow)
End If
' Copy source range to destination range in active sheet
sourceRange.Copy destRange
' Close the source workbook without saving changes
wb.Close False
Next i
Else
MsgBox "No files selected."
End If
End Sub

[@mostainbillah]

​@@ExcelWithBrainBell Dear Sir , Many Many thanks for Try to support , your Code not success work , Thank you . May Allah bless you .

[@ExcelWithBrainBell]

I apologize that the revised VBA code did not meet your requirements. Based on your previous comment, here's what I understand you need:
1. You want to copy data from rows 17 to 24 in Sheet1 of each workbook (e.g., workbook1.xlsx, workbook2.xlsx, etc.), and then paste that data into your active worksheet (e.g., Main.xlsx).
2. The data should initially paste into rows 17 to 24 of the active sheet (e.g., Main.xlsx). If rows 17 to 24 are already filled, the next set of data should be pasted into the next available rows, starting after the last filled row.
I’ve tested the code on my system, and it works as described above.
However, if this still isn't matching your expectations, could you please clarify your requirements further? This will help me better understand the specifics of your request and provide an accurate solution.

[@mostainbillah]

@@ExcelWithBrainBell Dear Sir ,Please See The Video Of my working file , Need Two Destination : First 17 to 24 Rows . Second : After 30 rows , present Destination code is okay for second option , I am lucky to your feedback

[@mostainbillah]

Dear Sir ,Could you please help me to provide Practice file . It Will be help for me ,

[@mostainbillah]

I have set a button by the tutorial code, very nice working, so sorry, I have add ( application screen update False) after multiple select file code,

[@ExcelWithBrainBell]

Thank you for your feedback! I'm glad to hear the code is working well for you with the button setup. No problem at all regarding the addition of "Application.ScreenUpdating = False" that's a great improvement for optimizing performance when working with multiple files. It's always helpful to reduce screen flickering during operations like this.

[@mostainbillah]

Thank you sir

[@mostainbillah]

whole day in free time i have exercise your code . need Practical file

[@ExcelWithBrainBell]

brainbell.com/?p=3695#download

[@mostainbillah]

@@ExcelWithBrainBell Dear Sir , It is High Quality & scholars type Video Tutorial , I am proud To getting The Tutorial , I am grateful to you , The Video Tutorial Helpful For me ,
So , A Small Requiest To you , to developing Some Option :
Your Code & tutorial is Ok , but
My requiest Is : 1. Set source range in each workbook from - Row 17 to 24 in Blanck cell .
(End(xlUp).Row + 1) from Row 17 to 24
2. when blank cell will filup then past will in last offset row ( Present code destination )
it is your pleasure & free time ,no Emargency .
thanks
Mostain Billah
Dhaka, Bangladesh.

[@mostainbillah]

@@ExcelWithBrainBell Dear Sir , It is High Quality & scholars type Video Tutorial , I am proud To getting The Tutorial , I am grateful to you , The Video Tutorial Helpful For me ,
So , A Small Requiest To you , to developing Some Option :
Your Code & tutorial is Ok ,
My requiest Is : Set source range in each workbook from - Row 17 to 24 in Blanck cell .
(End(xlUp).Row + 1) from Row 17 to 24
(Note: Rows 17 to 24 of my workbook are blank and there have rows 25 to 28 with data, )
when blank cell will filup then past will in last offset row ( Present code destination )
it is your pleasure & free time ,no Emargency .
thanks
Mostain Billah
Dhaka, Bangladesh.