I need to cut data from one sheet and paste it to another. The column
numbers always remain the same, but the number of rows will vary. The
following works, but the problem is that there is a command button in the
range (on sheet "test") that I do not want to be included in the cut/paste.

Is there a way to exclude it? For the data, I need to both the values and
the formatting.

TIA,

Patti

Sub cutNpaste()
' works, but includes command button imbedded on sheet "test"
With Sheets("test")
Range("a:o").EntireColumn.Cut _
Destination:=Sheets("myDest").Range("a1")
End With

End Sub

After pasting, you could delete every button on the myDest sheet by using the
Buttons collection:

For Each b In ThisWorkbook.Worksheets("myDest").Buttons
b.Delete
Next b

Regards
Ben


"Patti" wrote:

I need to cut data from one sheet and paste it to another. The column
numbers always remain the same, but the number of rows will vary. The
following works, but the problem is that there is a command button in the
range (on sheet "test") that I do not want to be included in the cut/paste.

Is there a way to exclude it? For the data, I need to both the values and
the formatting.

TIA,

Patti

Sub cutNpaste()
' works, but includes command button imbedded on sheet "test"
With Sheets("test")
Range("a:o").EntireColumn.Cut _
Destination:=Sheets("myDest").Range("a1")
End With

End Sub

Thanks Ben. But is there anyway to just exclude it from the cut? I need it
to remain on my main page "test" for future use.

Regards,

Patti

"Ben Griffiths" wrote:

After pasting, you could delete every button on the myDest sheet by using the
Buttons collection:

For Each b In ThisWorkbook.Worksheets("myDest").Buttons
b.Delete
Next b

Regards
Ben


"Patti" wrote:

I need to cut data from one sheet and paste it to another. The column
numbers always remain the same, but the number of rows will vary. The
following works, but the problem is that there is a command button in the
range (on sheet "test") that I do not want to be included in the cut/paste.

Is there a way to exclude it? For the data, I need to both the values and
the formatting.

TIA,

Patti

Sub cutNpaste()
' works, but includes command button imbedded on sheet "test"
With Sheets("test")
Range("a:o").EntireColumn.Cut _
Destination:=Sheets("myDest").Range("a1")
End With

End Sub

Just to supplement.
If it is in the Buttons collection (and it probably is, because a true
commandbutton isn't copied with the code shown by the OP), then

Thisworkbook.Worksheets("MyDest").Buttons.Delete

should suffice. (save a little looping)

--
Regards,
Tom Ogilvy


"Ben Griffiths" wrote in message
...
After pasting, you could delete every button on the myDest sheet by using
the
Buttons collection:

For Each b In ThisWorkbook.Worksheets("myDest").Buttons
b.Delete
Next b

Regards
Ben


"Patti" wrote:

I need to cut data from one sheet and paste it to another. The column
numbers always remain the same, but the number of rows will vary. The
following works, but the problem is that there is a command button in
the
range (on sheet "test") that I do not want to be included in the
cut/paste.

Is there a way to exclude it? For the data, I need to both the values
and
the formatting.

TIA,

Patti

Sub cutNpaste()
' works, but includes command button imbedded on sheet "test"
With Sheets("test")
Range("a:o").EntireColumn.Cut _
Destination:=Sheets("myDest").Range("a1")
End With

End Sub

Sub cutNpaste()
' works, but includes command button imbedded on sheet "test"
Dim rng As Range
Set rng1 = Sheets("test").Range("a:o").EntireColumn
rng1.Copy
With Worksheets("MyDest").Range("A1")
.PasteSpecial xlFormats
.PasteSpecial xlFormulas
End With
rng1.ClearContents
End Sub

Works for me.

--
Regards,
Tom Ogilvy


"Patti" wrote in message
...
Thanks Ben. But is there anyway to just exclude it from the cut? I need
it
to remain on my main page "test" for future use.

Regards,

Patti

"Ben Griffiths" wrote:

After pasting, you could delete every button on the myDest sheet by
using the
Buttons collection:

For Each b In ThisWorkbook.Worksheets("myDest").Buttons
b.Delete
Next b

Regards
Ben


"Patti" wrote:

I need to cut data from one sheet and paste it to another. The column
numbers always remain the same, but the number of rows will vary. The
following works, but the problem is that there is a command button in
the
range (on sheet "test") that I do not want to be included in the
cut/paste.

Is there a way to exclude it? For the data, I need to both the
values and
the formatting.

TIA,

Patti

Sub cutNpaste()
' works, but includes command button imbedded on sheet "test"
With Sheets("test")
Range("a:o").EntireColumn.Cut _
Destination:=Sheets("myDest").Range("a1")
End With

End Sub

As always, I appreciate your help Tom!

"Tom Ogilvy" wrote:

Sub cutNpaste()
' works, but includes command button imbedded on sheet "test"
Dim rng As Range
Set rng1 = Sheets("test").Range("a:o").EntireColumn
rng1.Copy
With Worksheets("MyDest").Range("A1")
.PasteSpecial xlFormats
.PasteSpecial xlFormulas
End With
rng1.ClearContents
End Sub

Works for me.

--
Regards,
Tom Ogilvy


"Patti" wrote in message
...
Thanks Ben. But is there anyway to just exclude it from the cut? I need
it
to remain on my main page "test" for future use.

Regards,

Patti

"Ben Griffiths" wrote:

After pasting, you could delete every button on the myDest sheet by
using the
Buttons collection:

For Each b In ThisWorkbook.Worksheets("myDest").Buttons
b.Delete
Next b

Regards
Ben


"Patti" wrote:

I need to cut data from one sheet and paste it to another. The column
numbers always remain the same, but the number of rows will vary. The
following works, but the problem is that there is a command button in
the
range (on sheet "test") that I do not want to be included in the
cut/paste.

Is there a way to exclude it? For the data, I need to both the
values and
the formatting.

TIA,

Patti

Sub cutNpaste()
' works, but includes command button imbedded on sheet "test"
With Sheets("test")
Range("a:o").EntireColumn.Cut _
Destination:=Sheets("myDest").Range("a1")
End With

End Sub