Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code to select cell 1 column left to active cell (XL 2002 SP3)

    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">Sorry to all Lounge members,
    I had to look a bit more closely to the Help file.
    The solution was there :
    ActiveCell.Offset(row,column).Activate</span hi> <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>


    Hi all,

    I have a workbook with several worksheets. Every week another sheet is added. As I need to copy info from the last weeks sheet into the newly added sheet, I am trying to get this "standard copying" into VBA Code.

    Now I'm stuck with the following : I need to copy a cell from the previous sheet to the newly added sheet. The cell to be copied is always in the same column, but the row may differ every week. I have found a way to select the cell 1 column to the right of the cell to be copied. But I do not know the code to tell Excel to move the cursor 1 column to the left and select this cell.

    Anyone who can help me on this (most probably easy) one ?

    MTIA

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

    Re: Code to select cell 1 column left to active cell (XL 2002 SP3)

    I see that you have found the solution to what you want to do. However, I would like to add one additional comment. It is almost never necessary to select cells in VBA to accomplish what you want to do, and it is always slower and it does cause problems like the screen flashing while the macro is running. If you will show us the code that does what you want, we can probably show you how to do it without the selecting.
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code to select cell 1 column left to active cell (XL 2002 SP3)

    Thank you for your interest in my problem, Legare <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> !

    The code I use is :

    <font color=blue> ActiveSheet.Previous.Select
    Range("I1:I1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(3, -1).Activate
    ActiveCell.CurrentRegion.Select
    Selection.Copy
    ActiveSheet.Next.Select
    Range("I1:I1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(3, -2).Activate
    ActiveCell.PasteSpecial
    Range("H2:H2").Select
    </font color=blue>

    What I'm doing is :
    - I start on a newly added worksheet (one added every week) : "Sheet added this week" in my sample workbook
    - Go to the previous worksheet : "Sheet from last week" in my sample workbook
    - Copy a specific range of cells containing text and a formula (column is fixed, row number is variable so needs to be found)
    - Go to the next worksheet
    - Paste the cells (column is fixed, row number is variable so needs to be found)
    - Activate cell H2 : to get ready for the next action

    I have added a stripped down version of my excel file (no code included) with some comments on the worksheets.
    The code works as it is, but (as a novice) I am always open to learn how to improve my coding.
    So I really appreciate your suggestion to have a look at the code and see how it can be improved. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Regards

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Code to select cell 1 column left to active cell (XL 2002 SP3)

    Instead of all that selecting, you can reduce the core of the code to one long instruction (split into two lines here)
    <code>
    ActiveSheet.Previous.Range("I1").End(xlDown).Offse t(3, -1).CurrentRegion.Copy _
    Destination:=Range("I1").End(xlDown).Offset(3, -2)
    </code>
    If you want to clear the clipboard, add this line:
    <code>
    Application.CutCopyMode = False
    </code>
    You can select cell H2 if you like, but it is not really necessary, for the above code does not change the selection. The long instruction constructs the source and target ranges for the copy and paste operation without selecting them.

    Note: I used <code>Range("I1")</code> instead of <code>Range("I1:I1")</code>, and included the paste in the copy instruction instead of using PasteSpecial.

  5. #5
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code to select cell 1 column left to active cell (XL 2002 SP3)

    Wow, Hans <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    All I want in just 1 line of code ! <img src=/S/hailpraise.gif border=0 alt=hailpraise width=27 height=22>
    I just did not know about this technique to concatenate all the separate lines of code.
    Do you know where can I find more information on the subject (concatenate) ? <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

    Regards

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Code to select cell 1 column left to active cell (XL 2002 SP3)

    It's not so much concatenation as successive elimination. In most situations, if you have code like this:

    ObjectA.Select
    Selection.MethodB

    you can replace it with

    ObjectA.MethodB

    Similarly

    ObjectA.Select
    Selection.PropertyC = ValueD

    can be reduced to

    ObjectA.PropertyC = ValueD

    This can often be repeated, as in your code.

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code to select cell 1 column left to active cell (XL 2002 SP3)

    Hans put it quite nicely, but to put this technique into other words...its a matter of looking for :
    Select...Selection Pairs
    OR
    Select....Activecell Pairs.
    If you take a look at your previous code, you will notice that Hans simply removed most of these pairings to shorten the code. It makes the code run faster and its less disruptive on the screen. Legare mentioned this in his original reply!
    Regards,
    Rudi

  8. #8
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code to select cell 1 column left to active cell (XL 2002 SP3)

    Hans,

    Thank you for the explanation on successive elimination. I will keep this in mind when coding <img src=/S/compute.gif border=0 alt=compute width=40 height=20> .


    To Rudi,

    Your reply is a useful bonus to the answer I got from Hans <img src=/S/yep.gif border=0 alt=yep width=15 height=15> . Thanks !

    Regards

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code to select cell 1 column left to active ce

    A crude, but sometimes effective, solution to eliminate the "Pairs" - when editing down a recoded macro is to use Word as your text editor. Copy the entire procedure fromn the VB Editor into Word, use Find & Replace - as appropriate - to eliminate the Pairs and then copy your code back into the VB Editor. HTH
    Gre

  10. #10
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code to select cell 1 column left to active ce

    Thanks for the tip, Unkamunka.

    Regards

Posting Permissions

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