Automatically import data from another workbook

I need some code or formula to automatically add data from one sheet to my main sheet where I would like to put all of my data together. I am able to download sheets off of the net and they are in numerical sequence by date. "19991118damlbmp_zone", "19991119damlbmp_zone" and so on. I need to create a button or something to gather the info from the first sheet and paste it in my main sheet and then gather the info from the second sheet and paste that into the main sheet in the next available row.

Please help if you can

I am considering Main sheet is the sheet where all the data to be dumped and remaining sheets sheet1,sheet2,sheet3............... are the sheets from which we extract the data

I am defining array to store all the remaining sheets from which to extract data


Sub Macro1()

Dim ar As Variant
Dim adr1, adr2 As Variant
ar = Array("Sheet1", "Sheet2", "Sheet3") 'sheets from which we extract data to Main sheet

For i = 0 To UBound(ar)
'select the sheet to be extracted
'copy the used range
Sheets(ar(i)).Select
adr1 = ActiveSheet.UsedRange.Address
t = Split(adr1, ":")
If UBound(t) > 0 Then
Range("a" & Range(t(0)).Row & ":" & t(1)).Select
Selection.Copy
Else
If adr1 <> "" Then
Range(adr1).Select
Selection.Copy
End If
End If
Selection.Copy

'select the main sheet
'go to the last used row
'paste the extracted data
Sheets("Main").Select
adr2 = ActiveSheet.UsedRange.Address
t1 = Split(adr2, ":")
If UBound(t1) > 0 Then

Range("a" & (Range(t1(1)).Row + 1)).Select
ActiveSheet.Paste
Else
Range("a1").Select
ActiveSheet.Paste
End If
Next


End Sub

so how would I use this?

sorry, the programming is kinda new to me.

Go to tools->macro->visual basic editor.

after visual basic editor is open, go to toolbar , insert->module

and paste the given code

and then what do I do?

After you copy the code to the editor, close the visual basic editor.

now go to tools->macro->macros and select "macro1" and click "run" button .


Make sure that Main sheet is the sheet where data is to imported, and sheet1,sheet2, .... is the sheets from where the data is to extracted.