Sum selected Range and copy to clip board

[Dewey]

Hi,

I'm sure its simple but I can't seem to work it out. I need a macro
that sums the selected cells and copies it to the clipboard.

Any help would be greatly appreciated.

Cheers

Dewey

[NickHK]

Dewey,
Here's one way:

Private Sub CommandButton3_Click()
Dim MyData As DataObject
Set MyData = New DataObject
MyData.SetText Application.Sum(Selection)
MyData.PutInClipboard
End Sub

NickHK

"Dewey" <dwight.vandiem@platypusau.com> wrote in message
news:1155284609.950267.143720@h48g2000cwc.googlegroups.com...
> Hi,
>
> I'm sure its simple but I can't seem to work it out. I need a macro
> that sums the selected cells and copies it to the clipboard.
>
> Any help would be greatly appreciated.
>
> Cheers
>
> Dewey
>

[Dewey]

Hey Nick,

'DataObject' isn't a valid Dim so it errors out.

Any otherway??

Dewey


NickHK wrote:

> Dewey,
> Here's one way:
>
> Private Sub CommandButton3_Click()
> Dim MyData As DataObject
> Set MyData = New DataObject
> MyData.SetText Application.Sum(Selection)
> MyData.PutInClipboard
> End Sub
>
> NickHK
>
> "Dewey" <dwight.vandiem@platypusau.com> wrote in message
> news:1155284609.950267.143720@h48g2000cwc.googlegroups.com...
> > Hi,
> >
> > I'm sure its simple but I can't seem to work it out. I need a macro
> > that sums the selected cells and copies it to the clipboard.
> >
> > Any help would be greatly appreciated.
> >
> > Cheers
> >
> > Dewey
> >

[Dewey]

hmmm, got it working. For some reason it wouldn't work when I had it
as a module, so I moved it to a sheet.

Works perfectly, Cheers Nick

Dewey




Dewey wrote:

> Hey Nick,
>
> 'DataObject' isn't a valid Dim so it errors out.
>
> Any otherway??
>
> Dewey
>
>
> NickHK wrote:
>
> > Dewey,
> > Here's one way:
> >
> > Private Sub CommandButton3_Click()
> > Dim MyData As DataObject
> > Set MyData = New DataObject
> > MyData.SetText Application.Sum(Selection)
> > MyData.PutInClipboard
> > End Sub
> >
> > NickHK
> >
> > "Dewey" <dwight.vandiem@platypusau.com> wrote in message
> > news:1155284609.950267.143720@h48g2000cwc.googlegroups.com...
> > > Hi,
> > >
> > > I'm sure its simple but I can't seem to work it out. I need a macro
> > > that sums the selected cells and copies it to the clipboard.
> > >
> > > Any help would be greatly appreciated.
> > >
> > > Cheers
> > >
> > > Dewey
> > >

[NickHK]

DataObject is part of the MSForms library, so you need to add a reference.

NickHK

"Dewey" <dwight.vandiem@platypusau.com> wrote in message
news:1155286832.501026.327350@p79g2000cwp.googlegroups.com...
> Hey Nick,
>
> 'DataObject' isn't a valid Dim so it errors out.
>
> Any otherway??
>
> Dewey
>
>
> NickHK wrote:
>
> > Dewey,
> > Here's one way:
> >
> > Private Sub CommandButton3_Click()
> > Dim MyData As DataObject
> > Set MyData = New DataObject
> > MyData.SetText Application.Sum(Selection)
> > MyData.PutInClipboard
> > End Sub
> >
> > NickHK
> >
> > "Dewey" <dwight.vandiem@platypusau.com> wrote in message
> > news:1155284609.950267.143720@h48g2000cwc.googlegroups.com...
> > > Hi,
> > >
> > > I'm sure its simple but I can't seem to work it out. I need a macro
> > > that sums the selected cells and copies it to the clipboard.
> > >
> > > Any help would be greatly appreciated.
> > >
> > > Cheers
> > >
> > > Dewey
> > >
>

[Dewey]

Cheers,

any idea on the syntax to then paste from the clipboard.

Now I just need another macro that i'll bind to a key stroke so that it
does a 'paste values' command into the active cell.

Cheers

Dewey



NickHK wrote:

> DataObject is part of the MSForms library, so you need to add a reference.
>
> NickHK
>
> "Dewey" <dwight.vandiem@platypusau.com> wrote in message
> news:1155286832.501026.327350@p79g2000cwp.googlegroups.com...
> > Hey Nick,
> >
> > 'DataObject' isn't a valid Dim so it errors out.
> >
> > Any otherway??
> >
> > Dewey
> >
> >
> > NickHK wrote:
> >
> > > Dewey,
> > > Here's one way:
> > >
> > > Private Sub CommandButton3_Click()
> > > Dim MyData As DataObject
> > > Set MyData = New DataObject
> > > MyData.SetText Application.Sum(Selection)
> > > MyData.PutInClipboard
> > > End Sub
> > >
> > > NickHK
> > >
> > > "Dewey" <dwight.vandiem@platypusau.com> wrote in message
> > > news:1155284609.950267.143720@h48g2000cwc.googlegroups.com...
> > > > Hi,
> > > >
> > > > I'm sure its simple but I can't seem to work it out. I need a macro
> > > > that sums the selected cells and copies it to the clipboard.
> > > >
> > > > Any help would be greatly appreciated.
> > > >
> > > > Cheers
> > > >
> > > > Dewey
> > > >
> >

[NickHK]

If this is within Excel, you do not need that method. But if you have a
range selected, what do you mean by the Activecell ?
May be you mean this:
Range("D1").Value = Application.Sum(Selection)

NickHK

"Dewey" <dwight.vandiem@platypusau.com> wrote in message
news:1155288256.539568.18220@m79g2000cwm.googlegroups.com...
> Cheers,
>
> any idea on the syntax to then paste from the clipboard.
>
> Now I just need another macro that i'll bind to a key stroke so that it
> does a 'paste values' command into the active cell.
>
> Cheers
>
> Dewey
>
>
>
> NickHK wrote:
>
> > DataObject is part of the MSForms library, so you need to add a
reference.
> >
> > NickHK
> >
> > "Dewey" <dwight.vandiem@platypusau.com> wrote in message
> > news:1155286832.501026.327350@p79g2000cwp.googlegroups.com...
> > > Hey Nick,
> > >
> > > 'DataObject' isn't a valid Dim so it errors out.
> > >
> > > Any otherway??
> > >
> > > Dewey
> > >
> > >
> > > NickHK wrote:
> > >
> > > > Dewey,
> > > > Here's one way:
> > > >
> > > > Private Sub CommandButton3_Click()
> > > > Dim MyData As DataObject
> > > > Set MyData = New DataObject
> > > > MyData.SetText Application.Sum(Selection)
> > > > MyData.PutInClipboard
> > > > End Sub
> > > >
> > > > NickHK
> > > >
> > > > "Dewey" <dwight.vandiem@platypusau.com> wrote in message
> > > > news:1155284609.950267.143720@h48g2000cwc.googlegroups.com...
> > > > > Hi,
> > > > >
> > > > > I'm sure its simple but I can't seem to work it out. I need a
macro
> > > > > that sums the selected cells and copies it to the clipboard.
> > > > >
> > > > > Any help would be greatly appreciated.
> > > > >
> > > > > Cheers
> > > > >
> > > > > Dewey
> > > > >
> > >
>

[Dewey]

Sorry I think I've confused you.

The first code you sent me works perfectly and I have that associated
with a shortcut key, so when I select the range I press crt-e to sum
the data, now I want to go to another workbook and paste that value
into a selected cell. I could right click and go paste special, then
values, but I have to do this a lot of times and was hoping I could get
another macro for this function and assign it to another shortcut key
to save me lots of time.

Thanks

Dwight


NickHK wrote:

> If this is within Excel, you do not need that method. But if you have a
> range selected, what do you mean by the Activecell ?
> May be you mean this:
> Range("D1").Value = Application.Sum(Selection)
>
> NickHK
>
> "Dewey" <dwight.vandiem@platypusau.com> wrote in message
> news:1155288256.539568.18220@m79g2000cwm.googlegroups.com...
> > Cheers,
> >
> > any idea on the syntax to then paste from the clipboard.
> >
> > Now I just need another macro that i'll bind to a key stroke so that it
> > does a 'paste values' command into the active cell.
> >
> > Cheers
> >
> > Dewey
> >
> >
> >
> > NickHK wrote:
> >
> > > DataObject is part of the MSForms library, so you need to add a
> reference.
> > >
> > > NickHK
> > >
> > > "Dewey" <dwight.vandiem@platypusau.com> wrote in message
> > > news:1155286832.501026.327350@p79g2000cwp.googlegroups.com...
> > > > Hey Nick,
> > > >
> > > > 'DataObject' isn't a valid Dim so it errors out.
> > > >
> > > > Any otherway??
> > > >
> > > > Dewey
> > > >
> > > >
> > > > NickHK wrote:
> > > >
> > > > > Dewey,
> > > > > Here's one way:
> > > > >
> > > > > Private Sub CommandButton3_Click()
> > > > > Dim MyData As DataObject
> > > > > Set MyData = New DataObject
> > > > > MyData.SetText Application.Sum(Selection)
> > > > > MyData.PutInClipboard
> > > > > End Sub
> > > > >
> > > > > NickHK
> > > > >
> > > > > "Dewey" <dwight.vandiem@platypusau.com> wrote in message
> > > > > news:1155284609.950267.143720@h48g2000cwc.googlegroups.com...
> > > > > > Hi,
> > > > > >
> > > > > > I'm sure its simple but I can't seem to work it out. I need a
> macro
> > > > > > that sums the selected cells and copies it to the clipboard.
> > > > > >
> > > > > > Any help would be greatly appreciated.
> > > > > >
> > > > > > Cheers
> > > > > >
> > > > > > Dewey
> > > > > >
> > > >
> >