Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    New Lounger
    Join Date
    Apr 2001
    Location
    Dallas, Texas, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy/Paste Macro between Spreadsheets (Excel 97 SR2)

    Please don't laugh that my version is Excel 97. Anyway, I have a Excel macro that copies updates in range A2600 into another spreadsheet at the same "paste" point. The receiving spreadsheet has other information in columns E-M and the information in those row/columns doesn't move or adjust with the changes brought forward from the originating spreadsheet.

    Is there a way to do this in Excel 97? and within my macro?

    Thanks so much folks....

  2. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy/Paste Macro between Spreadsheets (Excel 97 SR2)

    <<Please don't laugh that my version is Excel 97>>

    Only way I would've laughed is if you were using XP... <img src=/S/smile.gif border=0 alt=smile width=15 height=15> . Just kidding...anyway why not make links to the cells and then you won't even have to run the macro. I believe you can select the range you want to copy, go to the new workbook, select the first cell in the range you want to copy to, right click, select paste special, and select "Paste Link".

    This works with 2000, but I cannot remember if the option is available in 97.

  3. #3
    New Lounger
    Join Date
    Apr 2001
    Location
    Dallas, Texas, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy/Paste Macro between Spreadsheets (Excel 97 SR2)

    Thanks for the feedback Mike...I tried the Copy/Paste Link in the beginning, but it basically does the same thing. My macro is really a Copy/Paste Link, just that it runs much faster since I don't have to manually setup the areas in each worksheet. The original worksheet only has 4 columns that get updated (deletes, changes, inserts). The receiving worksheet has the same 4 columns but several more with information in each row that pertains to the original. So, when the cells in columns A-D are copied into the receiving worksheet, the cell values in columns E-M don't move up/down with the row that they pertain to. That's my dilemma.

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy/Paste Macro between Spreadsheets (Excel 97 SR2)

    Are the links relative or absolute? If they are relative, there should be no "$" in front of the cell reference.

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Copy/Paste Macro between Spreadsheets (Excel 97 SR2)

    Hi Nick,
    Cells drawing data from other workbooks will only update correctly if both workbooks are open when the inserting/deleting in the source workbook is going on, or you're using named references for the links. Otherwise, the target workbook has no way of 'knowing' that the cells in the source workbook have changed location.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    New Lounger
    Join Date
    Apr 2001
    Location
    Dallas, Texas, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy/Paste Macro between Spreadsheets (Excel 97 SR2)

    They're relative Mike.......

  7. #7
    New Lounger
    Join Date
    Apr 2001
    Location
    Dallas, Texas, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy/Paste Macro between Spreadsheets (Excel 97 SR2)

    Thanks macro...the macro I recorded to do the Copy/Paste Special opens both workbooks to perform the functions.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy/Paste Macro between Spreadsheets (Excel 97 SR2)

    They would update, sure, but if one inserts a row in the source book, it would only mean one gets a skip in the rows of the linked book:

    =[Book2]Sheet1!A1
    =[Book2]Sheet1!A2
    =[Book2]Sheet1!A3

    would become

    =[Book2]Sheet1!A1
    =[Book2]Sheet1!A3
    =[Book2]Sheet1!A4

    Not very convenient if you need all rows...

    I guess copying the source range by macro is the way to go.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Copy/Paste Macro between Spreadsheets (Excel 97 SR2)

    Hi Nick,
    Is the information in columns E-M formula-based or manually entered? If it's the former, then your macro should easily be able to cope with filling in the relevant rows using an autofill. If it's the latter then it becomes much more complicated!
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    New Lounger
    Join Date
    Apr 2001
    Location
    Dallas, Texas, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy/Paste Macro between Spreadsheets (Excel 97 SR2)

    The data in columns E-M of the receiving spreadsheet is manually entered. So, when I Copy/Paste Special (values & comments) from columns A-D of the originating spreadsheet into A-D of the receiving spreadsheet, the data in the E-M of the receiving spreadsheet doesn't stay related to the same rows. Here's a copy of my macro....

    Sub UpdateServerList()
    '
    ' UpdateServerList Macro
    ' Macro recorded 8/30/2002 by Nick Jackson
    '

    '
    Range("A1").Select
    ChDir "Cocuments and SettingsnjacksonMy DocumentsDallas Server Audit"
    Workbooks.Open FileName:= _
    "Cocuments and SettingsnjacksonMy DocumentsDallas Server AuditDallas Servers-V7work.xls"
    Range("A1").Select
    Windows("Dallas Servers-V6.1.xls").Activate
    Range("A2").Select
    ActiveWindow.LargeScroll Down:=27
    ActiveWindow.SmallScroll Down:=3
    Range("A22000").Select
    Selection.Copy
    Windows("Dallas Servers-V7work.xls").Activate
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range("A2").Select
    Windows("Dallas Servers-V6.1.xls").Activate
    Application.CutCopyMode = False
    Range("A1").Select
    End Sub

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Copy/Paste Macro between Spreadsheets (Excel 97 SR2)

    Which columns(s) of the copied data are the link to the additional data? In other words, is there for example a server name in Column A with some other details in columns B (e.g. IP address etc) and the additional info is server specific? What I'm trying to get at is how to attach the additional info to the correct row of the data you import. If you could post a sample workbook, it might help.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    New Lounger
    Join Date
    Apr 2001
    Location
    Dallas, Texas, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy/Paste Macro between Spreadsheets (Excel 97 SR2)

    The info is confidential, but I've provided an extract of both the originating and receiving spreadsheets with the appropriate info changed for my purposes. The "live" spreadsheets have 600+ rows. The macro I'm using is connected to the Originating Workbook.xls and called UpdateServerList. I've zipped the two files for purposes of this attachment. Thanks again for all your help.
    Attached Files Attached Files

  13. #13
    New Lounger
    Join Date
    Apr 2001
    Location
    Dallas, Texas, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy/Paste Macro between Spreadsheets (Excel 97 SR2)

    I may have figured out how to "fake" Excel into doing what I need (at least for inserts). This doesn't work for deletes so I may have to make a special code/label for a deleted item, use "filters" and just leave the deletes in the Spreadsheet. Here's how I'm handling the inserts for columns A-D and keeping data in E-M associated with the same rows:

    All the copy/paste special stuff in the macro is the same. The last action is to SORT the Server Name column and select Expand the Selection. That way all other info in E-M stays with the A-D update. Wallah....

  14. #14
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Copy/Paste Macro between Spreadsheets (Excel 97 SR2)

    Hi Nick,

    Attached is a revised set of your spreadhseets with formula-based linking to do the updating. No macros involved.

    In the 'Originating' workbook, you can add/delete rows at will and, next time you open the 'Receiving' workbook, you'll be prompted to update the links. When you do, the source data data in columns A-D of the 'Originating' workbook will be automatically updated in the corresponding columns of the 'Receiving' workbook. I've used Conditional Formatting in the 'Receiving' workbook so that Assets with 'x' tags retain their red font. So far so good.

    The remaining issue is keeping the data in columns E-M of the 'Receiving' workbook aligned with the servers referred to in column A. To do this, I moved the data from columns E-M on the 'Server List' sheet to 'Sheet3' and added a 'Server Name' column to 'Sheet3' also, with only the server names for which the additional data exists. I then added a set of INDEX/MATCH formulae to columns E-M of the 'Server List' sheet, with error checking, to pull back the data for the appropriate servers from 'Sheet3' to columns E-M of the 'Server List' sheet. These too will now update whenever columns A-D are updated and/or 'Sheet3' is updated.

    See if this gives you the results you want.

    Cheers

    PS: One caveat - don't add/delete rows in the the 'Originating' workbook while the 'Receiving' workbook is open, or the linked refernces will fail.

    PPS: I've only set the 'Server List' sheet of the 'Receiving' workbook to import the first 20 rows from the 'Originating' workbook and to query only the first 10 rows of 'Sheet3', but the formulae etc are easily edited to change those parameters.
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  15. #15
    New Lounger
    Join Date
    Apr 2001
    Location
    Dallas, Texas, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy/Paste Macro between Spreadsheets (Excel 97 SR2)

    Tks Macropod...I'll try these out tomorrow and let you know how it works.

Page 1 of 2 12 LastLast

Posting Permissions

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