Copy pages, delete certain values

Hi. Trying to create a multiple page workbook. Start with 3 pages. Pages
2 and 3 have data that does not change and is used by page 1 with
VLOOKUP. I have been successful in adding a command button called "New
Page" to copy page 1 and insert it before page 2 in the workbook, but
when trying to deleted the values in 3 different ranges ( Range1,
Range2 and Range3) that were entered in page 1 before the COPY took
place, I am consistently gettng a error, or the command button seems to
work fine, but the data in the ranges are not deleted. After deletion,
would like to make Cell A11 active.

Also looking to try making a second command button that will save the
file as a name that is pulled from Cell A3 and Cell A7 and print all
pages with the exception of the original pages 2 and 3


---
Message posted from http://www.ExcelForum.com/

Hi
you may post your current code which is used after clicking the command
buttons. always easier to find your errors :-)

--
Regards
Frank Kabel
Frankfurt, Germany


Hi. Trying to create a multiple page workbook. Start with 3 pages.
Pages 2 and 3 have data that does not change and is used by page 1
with VLOOKUP. I have been successful in adding a command button
called "New Page" to copy page 1 and insert it before page 2 in the
workbook, but when trying to deleted the values in 3 different ranges
( Range1, Range2 and Range3) that were entered in page 1 before the
COPY took place, I am consistently gettng a error, or the command
button seems to work fine, but the data in the ranges are not
deleted. After deletion, would like to make Cell A11 active.

Also looking to try making a second command button that will save the
file as a name that is pulled from Cell A3 and Cell A7 and print all
pages with the exception of the original pages 2 and 3


---
Message posted from http://www.ExcelForum.com/

This is the error I get when running the command button.

Runtime error ‘1004’
Select method of range class failed

The part after the second comment about the ranges works fine when run
as a separate macro, but when included in the command button I get the
error

Private Sub NewPage_Click()

' Copy page and move it to next page

Dim Wks As Worksheet
Dim iCount As Integer

iCount = Worksheets.Count
Worksheets(iCount - 2).Copy after:=Worksheets(iCount - 2)
iCount = iCount + 1
Set Wks = Worksheets(iCount)
Worksheets(iCount - 2).Name = (iCount - 2)

' Delete Values for Game, Book and Quantity
Range("A11:E11").Select
Selection.ClearContents
Range("G11:M11").Select
Selection.ClearContents
Range("O11:R11").Select
Selection.ClearContents

Range("A11:E11").Select
Selection.Activate



End Sub


---
Message posted from http://www.ExcelForum.com/

Hi
you may add the worksheet references to your code. Currently you only
use
Range(...)

try something like
with worksheets("your_sheet")
.range(....)
'...
end with

or use an object variable like your 'wks' for this


--
Regards
Frank Kabel
Frankfurt, Germany


This is the error I get when running the command button.

Runtime error ‘1004’
Select method of range class failed

The part after the second comment about the ranges works fine when
run
as a separate macro, but when included in the command button I get
the
error

Private Sub NewPage_Click()

' Copy page and move it to next page

Dim Wks As Worksheet
Dim iCount As Integer

iCount = Worksheets.Count
Worksheets(iCount - 2).Copy after:=Worksheets(iCount - 2)
iCount = iCount + 1
Set Wks = Worksheets(iCount)
Worksheets(iCount - 2).Name = (iCount - 2)

' Delete Values for Game, Book and Quantity
Range("A11:E11").Select
Selection.ClearContents
Range("G11:M11").Select
Selection.ClearContents
Range("O11:R11").Select
Selection.ClearContents

Range("A11:E11").Select
Selection.Activate



End Sub


---
Message posted from http://www.ExcelForum.com/

Worked like a charm!! Thanks!!!


---
Message posted from http://www.ExcelForum.com/