How to refer to a selected range

[hogstrom]

Hi everyone,

I'm trying to create a macro that copies the cells that I currently have marked.

Example: I select the cells C5:F13 (by marking them with the cursor) and the macro copies this range. If i after this selects G2:R15, the macro will copy this range....

I have searched for a solution but cannot find one, and I know there is one!

I hope this makes any sense and I'm looking forward to a solution!/E

 

[Gruuuu]

Selection.Copy

If you Record your Macro, this is the default code that is generated.

Do this: start recording a macro, physically perform the operations you wish the macro to perform, and review the code generated by the macro.

You will learn a lot this way.

If you see commands or references you are unfamiliar with, look them up in the Reference.

You will also learn a lot this way.

 

[SmrRyan]

If you wanted the cell contents to be copied to the clipboard as soon as you select them (as opposed to having to hit a button etc to execute the Selection.copy command shown above), you might consider putting that single-line code in the selection-change event of the worksheet.

That way, every time you select a cell or some cells, their conrtents will be copied to clipboard and ready for some snappy pasting elsewhere. Sav yourself from the drudgery of Ctrl-c.

Hope that makes sense.

 

[SkipVought]

SmrRyan,

So then, what happens when you SELECT prior to paste?


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SmrRyan]

I'm sure that's not supposed to be a trick question Skip,
but I don't know what you're asking.

The general idea here was to exploit the Events functionality of the workshhet, so as to automate the running of the code upon each selection change in selection.

First you have to know what Events are, and they were quite an eye-opener to me when I learnt about em.

 

[SmrRyan]

Oh I see what you mean. Yeah, if you then went to attemp to paste int the same workshhet, you'd overwrite the clipboard contents as soon as you selected your destination cells.

So you could only get away with pasting to another worksheet or another application.

Minor limitation .. But maybe drudgery prevails after all ...

Plus it totally depends what you're trying to do, whether using Events would be any use to ya.

 

[SkipVought]

I'm asking you to back up your suggestion, by thinking through what the process would be to...

1. Make a selection - The Worksheet_SelectionChange event COPIES the selected cells.

2. THEN WHAT? How would you use the EVENTS or not, to PASTE somewhere on the sheet?


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DaveInIowa]

How about this?

Private copyRange As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If (Target.Cells.Count = 1) Then
        Set copyRange = Nothing
    ElseIf (copyRange Is Nothing) Then
        Set copyRange = Target
    ElseIf (copyRange.Rows.Count = Target.Rows.Count And copyRange.Columns.Count = Target.Columns.Count) Then
        copyRange.Copy Target
        Set copyRange = Nothing
    Else
        Set copyRange = Nothing
        Application.StatusBar = "Range sizes were not equal -- Copy aborted"
        Exit Sub
    End If
    
    If (copyRange Is Nothing) Then
        Application.StatusBar = False
    Else
        Application.StatusBar = "Select range to copy " & copyRange.Address & " to"
    End If

End Sub

 

[hogstrom]

Thanx for your help guys,

Selection.copy work just fine, I tried that before but´it must have been something else, because that time it did not work.

Anythin with worksheet_selectionchange is not applicable because I´m putting the code in a Add-In (It could prob. work but it´s just to messy to try to get that going).

Big thanks to everyone!!!!!