"copy destination:" using activecell & relative positions

  • I'm trying to re-write this part of a code


    Code
    Sheets("input").select
            ActiveCell.Offset(0, 4).Range("A1:A10").Select
            Application.CutCopyMode = False
            Selection.Copy
    Sheets("output").select
            ActiveCell.Offset(0, 1).Range("A1").Select
            ActiveSheet.Paste


    using the way more efficient "copy destination"


    ... Dave once showed me how to incorporate relative references, but I can't figure out how to incorporate the activecell part into the syntax. keep getting errors...


    Is there a way to do this?


    or at least... if there isn't a way, please let me!


    as always, i'm thankful for any leads.

  • Mispost, sorry!


    Hi orbsplateau,


    You may need to select the "output" sheet as activecell will only work in the active cell of the active window.


    Regards,
    Batman

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Hi orbsplateau,


    Sorry for the delay, my last post was just before going home.


    What concerns me is, even if it were possible to select the active cell of another sheet for copying to, how reliable this would be in terms of being the correct destination. Is it not possible to determine the position of the start cell to copy to by some other means (e.g. a specific cell address such as A1, or the next empty cell under a specific heading, etc.)? This would allow you to calculate the cell to copy to before the Copy command and you would then be able to use the Destination in the same statement.


    Regards,
    Batman.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • If you want to copy to "output" sheet referring "input" sheet´s active cell try this one

    Code
    Sub test()
    Dim Address As String
    Worksheets("Input").Activate
    Address = ActiveCell.Address
    ActiveCell.Offset(0, 4).Range("A1:A10").Copy _
    Destination:=Worksheets("Output").Range(Address).Offset(0, 1).Range("A1")
    End Sub

    If you walked away smiling-then for you the price was right

  • Quote from Batman


    What concerns me is, even if it were possible to select the active cell of another sheet for copying to, how reliable this would be in terms of being the correct destination. Is it not possible to determine the position of the start cell to copy to by some other means (e.g. a specific cell address such as A1, or the next empty cell under a specific heading, etc.)? This would allow you to calculate the cell to copy to before the Copy command and you would then be able to use the Destination in the same statement.


    That's a good point... in this macro however, the activecell's position is determined by the FIND command. It searches for a string in all the cells, and once it finds it, it copies data from neighbouring cells to another sheet.


    I have no way of knowing in advance where the dice are gonna land!


    thanks for the advice though

  • Quote from kundepuu

    If you want to copy to "output" sheet referring "input" sheet´s active cell try this one


    yeay! :) that's clever....


    the only question is, will this be significantly faster than copying & pasting ... hmmm... I'll let you know.

  • Hi orbsplateau,


    You say you want to copy the data "to another sheet"; the problem is whereabouts in that other sheet?


    I believe you will only be able to use Activecell in the destination part of the "copy destination" command if the "other sheet" is the active sheet. Once we have determined how to identify the location of the cell you want to paste the data into, we will be able to work out how to structure the command.


    I suspect that, unless you are copying to within the same worksheet, you will not be able to use Activecell in both halves of a single command.


    Regards,
    Batman.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • aaaahhhh, Ok i get it.


    I guess this is why kundepuu's work around in necessary...


    the "activecells" in the other sheets are no longer "Active" when I switch to another sheet. didn't know....
    it just seemed that excel somehow remembers where each sheet's activecell is.


    I guess, when I'm using several sheets i can record the activecell's address in each sheet in a variable (they'd have to be constantly updated) and that way i can directly refer to them.
    and as you said earlier, I should probably avoid refering to activecell whenever possible.


    thanks a lot for that insight

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!