1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel: Automatically import data from another workbook

Discussion in 'Business Applications' started by bparker, Jan 31, 2005.

Thread Status:
Not open for further replies.
Advertisement
  1. bparker

    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
     
  2. johnske

    johnske

    Joined:
    Jun 23, 2004
    Messages:
    167
    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]
     
  3. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    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
     
  4. johnske

    johnske

    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
     
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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/325399

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice