I have a logical group of cells in Sheet1 that can change in size over time
(new cells are added, old cells are removed)

I would like to have a way to copy this group of cells from Sheet1 into
Sheet2 with the push of a button. (Keep in mind that the number of cells
fluctuate)

I am curious if anyone else has ever run into this and if there is a VBA
example of how they handled it.

Thanks in advance for your assistance.

Brad

Create your command button from the Control Toolbox, then in design mode
right click the button and select View Code from the drop down list. Copy
the code below into the code window that opens.

Private Sub CommandButton1_Click()
Sheets(1).ActiveCell.CurrentRange.Copy _
Sheets(2).Range("A1")
Application.CutCopyMode = False
End Sub

Exit design mode, select a cell in the range, then click the button.


"Brad" <(E-Mail Removed)> wrote in message
news:4F24D1AD-11C4-4773-B725-(E-Mail Removed)...
>I have a logical group of cells in Sheet1 that can change in size over time
> (new cells are added, old cells are removed)
>
> I would like to have a way to copy this group of cells from Sheet1 into
> Sheet2 with the push of a button. (Keep in mind that the number of cells
> fluctuate)
>
> I am curious if anyone else has ever run into this and if there is a VBA
> example of how they handled it.
>
> Thanks in advance for your assistance.
>
> Brad
>

Had my mind on range, needed region. Use this modified version.

Private Sub CommandButton1_Click()
Sheets(1).ActiveCell.CurrentRegion.Copy _
Sheets(2).Range("A1")
Application.CutCopyMode = False
End Sub




"Brad" <(E-Mail Removed)> wrote in message
news:4F24D1AD-11C4-4773-B725-(E-Mail Removed)...
>I have a logical group of cells in Sheet1 that can change in size over time
> (new cells are added, old cells are removed)
>
> I would like to have a way to copy this group of cells from Sheet1 into
> Sheet2 with the push of a button. (Keep in mind that the number of cells
> fluctuate)
>
> I am curious if anyone else has ever run into this and if there is a VBA
> example of how they handled it.
>
> Thanks in advance for your assistance.
>
> Brad
>

JLGWhiz,

Thanks for the help. I can't wait to get into work to try this. I am
fairly new to Excel and really new at VBA. I have a lot to learn.

This is probably a really dumb question. How do you establish a "Region" in
Sheet1?

Thanks again,

Brad


"JLGWhiz" wrote:

> Had my mind on range, needed region. Use this modified version.
>
> Private Sub CommandButton1_Click()
> Sheets(1).ActiveCell.CurrentRegion.Copy _
> Sheets(2).Range("A1")
> Application.CutCopyMode = False
> End Sub
>
>
>
>
> "Brad" <(E-Mail Removed)> wrote in message
> news:4F24D1AD-11C4-4773-B725-(E-Mail Removed)...
> >I have a logical group of cells in Sheet1 that can change in size over time
> > (new cells are added, old cells are removed)
> >
> > I would like to have a way to copy this group of cells from Sheet1 into
> > Sheet2 with the push of a button. (Keep in mind that the number of cells
> > fluctuate)
> >
> > I am curious if anyone else has ever run into this and if there is a VBA
> > example of how they handled it.
> >
> > Thanks in advance for your assistance.
> >
> > Brad
> >
>
>
>

The CurrentRegion is established when two or more adjacent cells contain
data. Making any one of the cells the active cell will allow you to use
the CurrentRegion constant to identify the entire range in the code.

ActiveCell.CurrentRegion.Address

This will return the absolute address of the range in A1 format.

In going back and testing the code, I realized that the Sheets(1) reference
cannot be used with this particular syntax. So delete that from the
Sheets(1). and it will be:

Private Sub CommandButton1_Click()
ActiveCell.CurrentRegion.Copy _
Sheets(2).Range("A1")
Application.CutCopyMode = False
End Sub

And you have to make sure that the sheet from which the data is being copied
is the active sheet.



"Brad" <(E-Mail Removed)> wrote in message
news:09187ACC-14F6-463B-A513-(E-Mail Removed)...
> JLGWhiz,
>
> Thanks for the help. I can't wait to get into work to try this. I am
> fairly new to Excel and really new at VBA. I have a lot to learn.
>
> This is probably a really dumb question. How do you establish a "Region"
> in
> Sheet1?
>
> Thanks again,
>
> Brad
>
>
> "JLGWhiz" wrote:
>
>> Had my mind on range, needed region. Use this modified version.
>>
>> Private Sub CommandButton1_Click()
>> Sheets(1).ActiveCell.CurrentRegion.Copy _
>> Sheets(2).Range("A1")
>> Application.CutCopyMode = False
>> End Sub
>>
>>
>>
>>
>> "Brad" <(E-Mail Removed)> wrote in message
>> news:4F24D1AD-11C4-4773-B725-(E-Mail Removed)...
>> >I have a logical group of cells in Sheet1 that can change in size over
>> >time
>> > (new cells are added, old cells are removed)
>> >
>> > I would like to have a way to copy this group of cells from Sheet1 into
>> > Sheet2 with the push of a button. (Keep in mind that the number of
>> > cells
>> > fluctuate)
>> >
>> > I am curious if anyone else has ever run into this and if there is a
>> > VBA
>> > example of how they handled it.
>> >
>> > Thanks in advance for your assistance.
>> >
>> > Brad
>> >
>>
>>
>>