I have the below code that works perfectly for opening a file, selecting 1 file and it automatically copies from A2:AA2 and pastes under my master sheet of data (below my current data). I am looking to add a feature where I can select multiple sheets, where it will copy from A2:AA2 in all of the selected excel sheets (max 30) and either combine into 1 sheet (where i can then paste later on); Or all be pasted below each other in my master sheet.
Thanks
Sub add_data()
Dim openfile As String
Dim OpenBook As Workbook
Dim targetworkbook As String, targetsheetname As String
Application.ScreenUpdating = False
targetworkbook = Application.GetOpenFilename(FileFilter:="Excel Files, *.xls*", _
Title:="Select Data", MultiSelect:=True)
If targetworkbook = "False" Or openfile = "" Then
'If the value is false or null then exit
Set OpenBook = Application.Workbooks.Open(targetworkbook)
OpenBook.Sheets(1).Select
Range("A2:AA2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ThisWorkbook.Worksheets("xxx").Range("A4").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
OpenBook.Close False
End If
Application.ScreenUpdating = True
End Sub
Here is a piece of code allowing you to choose which worksheets copying data from in a Workbook: