I am having some problems getting this code to work like it has in the past.
I am sure that there is an easier way to accomplish this task, however, I
must be missing something.

To give you the appropriate background, I have a workbook that contains
numerous pages. I have built a macro which creates a new workbook named NWB.
Then it copies selected worksheets into the new workbook. The first part of
the ode below works fine to eliminate the buttons found on worksheet named
SRN. However, when I get to the line with the . I get a type mismatch
error, and everything stops. I am using the 'With' since I am activating
this code from a worksheet in the original workbook. In simple terms, I just
want to start at a known cell in workbook NWB and worksheet SRN and find out
the bottom row of the table with an entry in column 3. Then I copy it to the
clipboard and past back the cell contents as values. This is being done
since I don't copy the worksheet with the data that these cells reference
into the new workbook.

Please give me some guidance on how to accomplish this task.

Thanks!

' remove command buttons from Sheet
Dim i As Integer, N As Integer
Dim II As Integer
With NWB.Worksheets(SRN)
'get the number of OLEObjects on the sheet
N = .OLEObjects.Count
For i = N To 1 Step -1
'check the type of object. If a command button, delete it
If LCase(TypeName(.OLEObjects(i).Object)) = _
"commandbutton" Then .OLEObjects(i).Delete
Next

' convert the MM and MN cells to values

II = 20 ' start on row 20

Do Until .Cells(II, 3) = "" ' find end of column
II = II + 1
Loop


II = II - 1 ' get back to last row
.Range(.Cells(20, 3), .Cells(II, 4)).Copy ' get the range onto the
clipboard
.Range(.Cells(20, 3), .Cells(II, 4)).PasteSpecial (xlValues)

End With

Ray,

Sounds like you just need to find the last used column
and loop through all of them.
For example:
ActiveCell.SpecialCells(xlLastCell).Select
myLastCol= ActiveCell.Column
colIndex = 1
For colIndex = 1 to myLastCol
'your code here
Next colIndex

If you have any questions, please email me,
Ed

-----Original Message-----
I am having some problems getting this code to work like
it has in the past.
I am sure that there is an easier way to accomplish this
task, however, I
must be missing something.

To give you the appropriate background, I have a
workbook that contains
numerous pages. I have built a macro which creates a new
workbook named NWB.
Then it copies selected worksheets into the new
workbook. The first part of
the ode below works fine to eliminate the buttons found
on worksheet named
SRN. However, when I get to the line with the . I
get a type mismatch
error, and everything stops. I am using the 'With' since
I am activating
this code from a worksheet in the original workbook. In
simple terms, I just
want to start at a known cell in workbook NWB and
worksheet SRN and find out
the bottom row of the table with an entry in column 3.
Then I copy it to the
clipboard and past back the cell contents as values.
This is being done
since I don't copy the worksheet with the data that
these cells reference
into the new workbook.

Please give me some guidance on how to accomplish this
task.

Thanks!

' remove command buttons from Sheet
Dim i As Integer, N As Integer
Dim II As Integer
With NWB.Worksheets(SRN)
'get the number of OLEObjects on the sheet
N = .OLEObjects.Count
For i = N To 1 Step -1
'check the type of object. If a command
button, delete it
If LCase(TypeName(.OLEObjects(i).Object)) = _
"commandbutton" Then .OLEObjects
(i).Delete
Next

' convert the MM and MN cells to values

II = 20 ' start on row 20

Do Until .Cells(II, 3) = "" ' find end of column
II = II + 1
Loop


II = II - 1 ' get back to last row
.Range(.Cells(20, 3), .Cells(II, 4)).Copy ' get
the range onto the
clipboard
.Range(.Cells(20, 3), .Cells(II,
4)).PasteSpecial (xlValues)

End With



.