Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Paste Data from Several Worksheets into one DB (Excel 2000, SP3)

    What would be the easiest way to take data from approximately 80 different sheets in a workbook and place it all into one database on a separate spreadsheet in the same workbook? All of the data will be in the range A7:R10 on each sheet. Some cells would be blank. (I would want to paste it all, regarless of whether they were blank or not.) Based on the input ranges, I imagine the resulting database area will be approximately A1:R320. The spreadsheets are all named (ie - they're not just called sheet1, sheet2, etc).

    There are two other sheets in the workbook, but if they had to be included for ease of figuring this out, it wouldn't be a big deal. I could pretty easily identify the data from those sheets and delete it from the resulting database.

    Thanks for any advice. Let me know if more background info is needed.

    - Brett

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste Data from Several Worksheets into one DB (Excel 2000, SP3)

    Does this do what you need?

    <pre>Public Sub LinkCells()
    Dim oTgt As Range, oSrcSht As Worksheet, oSavSel As Range
    Application.ScreenUpdating = False
    Set oSavSel = Selection
    Set oTgt = ActiveSheet.Range("A65536").End(xlUp)
    If oTgt.Value <> "" Then
    Set oTgt = oTgt.Offset(1, 0)
    End If
    For Each oSrcSht In Worksheets
    If oSrcSht.Name <> ActiveSheet.Name Then
    oSrcSht.Range("A7:R10").Copy
    oTgt.Select
    ActiveSheet.Paste Link:=True
    Set oTgt = oTgt.Offset(4, 0)
    End If
    Next oSrcSht
    oSavSel.Select
    Application.ScreenUpdating = True
    End Sub
    </pre>

    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste Data from Several Worksheets into one DB (Excel 2000, SP3)

    That worked perfectly.

    Thanks!

    - Brett

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •