Help making this macro dynamic :)

[nagerpop]

Hey, Im a beginner in excel.

So, I've made a macro that opens up multiple workbooks, copies the data fro
the first sheet in each workbook opened and then pastes this data back into th
first sheet of the master excel book. Each time the data is copied from eac
workbook it opens, the data needs to be pasted underneath the data that wa
pasted previously.

Anywho, I made a macro that works but it is hardcoded - i need it to be dynami
so i can add more data later without it been overwritten by the previou
pastings.

Could someone make my macro dynamic by adding the vars i need and the amende
code?

My attempt:

Sub ImportData()
'
' ImportData Macro
'
' Variables
Dim lngCount As Long

' Turn Off Screen Updater
Application.ScreenUpdating = False

'Opens the file dialog box
With Application.FileDialog(msoFileDialogOpen)
.InitialFileName = sPath
.Title = sTitle
.Filters.Add "Excel Files", "*.xls"
.AllowMultiSelect = True

If .Show = -1 Then
' Open the files to extract the data from
For lngCount = 1 To .SelectedItems.Count
Workbooks.Open .SelectedItems(lngCount)
Next lngCount
End If
End With

' Start below table header - where pasting the data into the cells begins'
Range("A2").Select

' The files have been opened and now extracting the data to the Master Boo
(Data) Worksheet; from the Workbook x (Sheet1) (Hard Coded - make this dynamic)

' Select the first of x workbooks to pull data from'
Windows("Workbook 1.xls").Activate

' Then selects the Data Range and copies the data in the worksheet of'
Range("A1:E26").Select
Range("E26").Activate
Selection.Copy

' Finally, pasting the copied data into the Master file on the Data Sheet'
Windows("Master Book.xlsm").Activate
ActiveSheet.Paste

' Finishing by selecting the empty cell below the pasted data in the Dat
worksheet in the Master Book"
Selection.End(xlDown).Select
Range("A28").Select

< needs a loop here>

' Then the process starts again for x files"
Windows("Workbook 2.xls").Activate
Range("A1:E23").Select
Range("E23").Activate
Application.CutCopyMode = False
Selection.Copy
Windows("Master Book.xlsm").Activate
ActiveSheet.Paste
Selection.End(xlDown).Select
Range("A51").Select


'Close all inactive workbooks - needs hardcoding'
Windows("Workbook 1.xls").Activate
ActiveWindow.Close
Windows("Workbook 2.xls").Activate
ActiveWindow.Close

End Sub

Thanks,
np.

 

[Tim Williams]

Untested....

'********************************************
Sub ImportData()

Dim lngCount As Long

Application.ScreenUpdating = False ' Turn Off Screen Updater

'Opens the file dialog box
With Application.FileDialog(msoFileDialogOpen)
.InitialFileName = sPath
.Title = sTitle
.Filters.Add "Excel Files", "*.xls"
.AllowMultiSelect = True


If .Show = -1 Then
' Open the files to extract the data from
For lngCount = 1 To .SelectedItems.Count
AppendFileData .SelectedItems(lngCount)
Next lngCount
End If
End With

End Sub

Sub AppendFileData(fName As String)

Dim wb As Excel.Workbook, shtDest As Excel.Worksheet
Dim rw As Long

Set wb = Workbooks.Open(fName)
Set shtDest = Workbooks("Master
Book.xlsm").Worksheets("YourSheetName")

'find first empty row (working up from the last row)
rw = shtDest.Cells(1, shtDest.Rows.Count).End(xlUp).Row + 1

'in each opened wb is there a name for the sheet with the data?
'wb.Sheets("SheetName").Range("A1:E26").Copy shtDest.Cells(rw, 1)
'if not, then copy from the active sheet
ActiveSheet.Range("A1:E26").Copy shtDest.Cells(rw, 1)

wb.Close.False 'comment out to leave open...

End Sub
'*************************************

Tim