# Excel: Automatically import data from another workbook



## bparker (Nov 11, 2004)

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


----------



## johnske (Jun 23, 2004)

bparker said:


> 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


Hi bparker - Not sure I'm reading your intent right, but if you can save the "19991118damlbmp_zone" etc sheets into a book on the desktop you name "Book11" and then open a new book called "Book10" and run the code from Book10 - you can use something like this:

```
[SIZE=2]Sub COPYIT()
'this is run from the book named Book10
      Dim N%
      Application.ScreenUpdating = False
      On Error Resume Next          '<< error is - "books already open"
      Workbooks.Open Filename:="C:\WINDOWS\Desktop\Book11.xls"
      For N = 1 To Sheets.Count
            Workbooks("Book11.xls").Activate
            Sheets("Sheet" & N).Select
            Range("A1", Range("A65536").End(xlUp).Rows.EntireRow).Select
            Selection.Copy
            Windows("Book10.xls").Activate
            Sheets("Sheet1").Activate
            Range("A65536").End(xlUp).Offset(1, 0).Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
      Next
      Workbooks("Book11").Close
      Range("A1").Select
End Sub[/SIZE]
```


----------



## Rollin_Again (Sep 4, 2003)

johnske said:


> Range("A1", Range("A65536").End(xlUp).Rows.EntireRow).Select
> Range("A65536").End(xlUp).Offset(1, 0).Select


This assumes that one of the cells in column A will be the last cell containing data on the sheet. If he is importing the data from somewhere else there is a good chance that the last cell containing data will be in a column other than A. Here is a more reliable way to find and copy all the data from the sheet using the real last row and last column.

```
vLastRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row

vLastCol = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column

vColumn = Left(Columns(vLastCol).Address(0, 0), 2 + (vLastCol < 27))

Range("A1", vColumn & vLastRow).Select

Selection.Copy
```
Rollin


----------



## johnske (Jun 23, 2004)

Hi Rollin'

Thanx, yeah, in the absence of further info I made that assumption and rushed it off as a "starter" figuring if that doesn't apply to this particular case we could work from there and modify...

(I was also considering "ActiveCell.CurrentRegion.Select" but we don't know yet if there are any empty rows or columns in the data - if there aren't, that'd be the simplest way to go...)

John


----------

