Excel: Automatically import data from another workbook

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

bparker

Thread Starter
Joined
Nov 11, 2004
Messages
159
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
 
Joined
Jun 23, 2004
Messages
167
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:
Code:
[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]
 
Joined
Sep 4, 2003
Messages
4,912
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.
Code:
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
 
Joined
Jun 23, 2004
Messages
167
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 :p
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top