Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Location
    Kansas City, Missouri, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro code in Excel 2000 (Win XP/Excel 2000)

    This is a simple question-- I think-- but at my age, I forgot how I did this earlier.

    I want to write a macro in Excel that copies data from another worskheet in a workbook to a summary worksheet in the same workbook. I want to begin importing the data in cell, let's say, B9, and drop values into the cells off to the right (C9, D9, E9, etc.). Once all the data has been copied, I want to drop down one row and go back to column B (so at the end of the first run, I end up at B10). I then want to be able to modify the data on the source sheet, then go back to my summary sheet and re-run the macro. I want the new data to occupy its appropriate columns on row 10, and when the macro is done, have the cursor end up at B11, ready to repeat again, etc. etc.

    I recorded a macro to do this, and ran it once. It ran just fine. Cursor ended up on B10, etc.

    I then changed my source sheet data and reran the macro. It overwrote the old data and ended up again at B10.

    Is this a relative / absolute references issue? If so, how do I turn on the macro recorder to use relative references?

    Thanks in advance for your help!

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro code in Excel 2000 (Win XP/Excel 2000)

    To answer only the part on relative references, when the record Macro button shows up, clicking the button grid toggles absolute and relative.

    However, for the kind of task you describe, you won't get efficient code from the recorder. If you could post a censored example and explain in more detail, including what modifications you make to the data on the source sheets, someone can probably write some efficient code or paossibly suggest a better way to do what you are doing.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    New Lounger
    Join Date
    Jan 2004
    Location
    Kansas City, Missouri, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro code in Excel 2000 (Win XP/Excel 2000)

    Thanks for the reply, John. Problem is, when I launch the recorder, I don't get the Recorder buttons any more, and that includes the relatvie/absolute references button. Here is a sample of the macro code as it exists now:

    ActiveSheet.Previous.Select
    Range("J1").Select
    Selection.Copy
    ActiveSheet.Next.Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Range("C9").Select
    ActiveSheet.Previous.Select
    Range("F5:G5").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveSheet.Next.Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Range("D9").Select
    ActiveSheet.Previous.Select
    Range("F6:G6").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveSheet.Next.Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Range("E9").Select
    ActiveSheet.Previous.Select
    Range("F7:G7").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveSheet.Next.Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Range("F9").Select
    ActiveSheet.Previous.Select
    Range("F8:G8").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveSheet.Next.Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Range("G9").Select

    And so forth. The problem is the macro is recording the destination cells as absolutes, hence it overrites the original data when I rerun it with new data. I would like a way for the destinatation cells to drop down one row (example: E9 on the first run becomes E10 on the next use, then E11 on the third use, and so on).

    Does that make sense?

    Richard Harshaw
    Kansas City, MO

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro code in Excel 2000 (Win XP/Excel 2000)

    Try this.
    I wasn't sure where J1 was supposed to go, so I "guessed" that you wanted it in B. The way your code was, it went to the active cell of the second sheet.

    I also was confused of why you copied Fand G both since when you copied the next one it overwrote the column G one.

    This can be adjusted if you let us know where this isn't working.

    Steve

    <pre>Sub play()
    Dim wksSource As Worksheet
    Dim wksDest As Worksheet
    Dim lNextRow As Long
    Dim x As Integer
    Set wksSource = Worksheets("Sheet1")
    Set wksDest = Worksheets("Sheet2")

    lNextRow = wksDest.Range("B65536").End(xlUp).Row + 1

    wksDest.Cells(lNextRow, 2) = wksSource.Range("J1")

    For x = 1 To 5
    wksDest.Cells(lNextRow, 2 + x) = wksSource.Cells(4 + x, 6)
    Next
    wksDest.Cells(lNextRow, 2 + x) = wksSource.Cells(4 + x, 7)
    End Sub</pre>


  5. #5
    New Lounger
    Join Date
    Jan 2004
    Location
    Kansas City, Missouri, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro code in Excel 2000 (Win XP/Excel 2000)

    J1 is the starting point of the first worksheet; I copy that data into column B of the second sheet (cell B9 to be exact).

    In some cases, it looks like I am copying two cells from the first worksheet into a single cell of the second, but this is only because on the first worksheet I have merged some cells for a better screen appearance on the first sheet.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro code in Excel 2000 (Win XP/Excel 2000)

    So the question is: does the code I provided do what you want?

    Steve

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro code in Excel 2000 (Win XP/Excel 2000)

    While Steve is doing the hard work on the code, I should mention that to restore the Macro Recording Button., while recordig a macro select View | Toolbars | Stop Recording (which is the name of the TB button). The button probably disappeared because you clicked the 'X" on the button and closed the window.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    New Lounger
    Join Date
    Jan 2004
    Location
    Kansas City, Missouri, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro code in Excel 2000 (Win XP/Excel 2000)

    Steve, I have not had time yet to try it, but will this weekend. If it works, you'll be the SECOND one to know! Thanks for your research.

    Richard Harshaw

  9. #9
    New Lounger
    Join Date
    Jan 2004
    Location
    Kansas City, Missouri, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro code in Excel 2000 (Win XP/Excel 2000)

    How does Homer Simpson say it? Doh!

    Thanks. I'll give that a whirl!

    Richard Harshaw

  10. #10
    New Lounger
    Join Date
    Jan 2004
    Location
    Kansas City, Missouri, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro code in Excel 2000 (Win XP/Excel 2000)

    Great news, folks! John's tip on retrieving the Stop Recording tool bar enabled me to finally get the Relative References turned on and the macro now works like a charm!

    Thanks ever so much for the simple tip, John-- and to Steve for creating some macro code I am going to file away and keep for the future. Your help is immensely appreciated!

    Richard Harshaw

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Macro code in Excel 2000 (Win XP/Excel 2000)

    I recommend you use Steve's code, if it does what you need; his code is much more efficient than recorded code.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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