Code to copy worksheet

[BeSmart]

Hi
I'm sorry in advance - I'm a novice and I am stuck with re-writing the
following code without using a hard code worksheet name, or "current
worksheet" or "active worksheet" or "selection"... (I must avoid using these
words as they are apparently causing problems macros assigned to buttons)

The code needs to:
- make a copy of the current worksheet - whose name appears in cell BP8
- put the copy into a separate workbook
- select the named range "PlanCPTrange" on the copied worksheet (only) and
paste special as values
- select all buttons on the copied worksheet (only) and delete them
- select cell A1 (without using the word "select"
I tried to do this myself, but failed:

Sub CopyPlan()
Dim mySheet As Worksheet (?????)
Dim DestSheet As Worksheet (????)

Set mySheet = Range("BP8").Value 'mySheet worksheet name is quoted in BP8
on the current worksheet
Set DestSheet = ???? 'the name of the copied mySheet i.e. if BP8 = "Test"
then it will be "Test (2)"

With mySheet
Copy After:=Workbooks(????).Sheets(1) 'how to copy mySheet worksheet
into a new workbook
End With

With DestSheet.Range("PlanCPTrange")
Cells.Copy
Cells.PasteSpecial Paste:=xlValues
End With

With Destsheet.Buttons
.Delete
End With

End Sub

 

[Robert Crandal]

Have you considered throwing your current code out the window
and using a different approach?? If you don't want your buttons
to appear in the new workbook, why even both copying them
over in the first place??

Also, do you have a template workbook that matches your source
workbook? If you have an empty template workbook, couldn't
you write VBA code that opens the template workbook and then
your VBA code can transfer ONLY the data from the source
workbook into the destination workbook. This is just an alternative
idea.

Also, doesn't the name of your sheet already appear on the sheet
tab?? Does it matter that the sheet name is actually in a cell?? (BP8)

As for setting the current cell to "A1", I thought "Range("A1").Select"
would be sufficient....I know someone said it is good practice to
avoid this, but I can't think any other methods which select a cell.
Maybe someone else can answer this. 8)

Robert

 

[Mike H]

Hi,

I strongly disagree with whoever is telling you that unqualified ranges are
a good idea. Range("A1") for example without specifying the worksheet in some
way; particularly when working with multiple workbooks, is just about as bad
a practice as it gets. Even worse is that you have been expected to copy a
worksheet without even being given the liberty to refer to the activesheet.
What code would your advisor like you to use? perhaps

anyworkbook(anysheet you like).copy anywhere you feel like.paste

Have a look at the code below. One point is if were copying the active sheet
we don't need the name in a cell, we already now it from activesheet.name

This copies the activesheet to a workbook named MyBookName.xlsm and renames
the sheet and changes formula to values in the named range

Sub CopyPlan()
Dim DestSheetName As String


DestSheetName = ActiveSheet.Name & "(2)"
' MyBookName.xlsm change to suit
ActiveSheet.Copy After:=Workbooks("MyBookName.xlsm").Sheets(1)
Workbooks("MyBookName.xlsm").Sheets(2).Name = DestSheetName


Workbooks("MyBookName.xlsm").Sheets(2).Range("PlanCPTrange").Value = _
Workbooks("MyBookName.xlsm").Sheets(2).Range("PlanCPTrange").Value
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mike H]

Robert,

As for setting the current cell to "A1", I thought "Range("A1").Select"
would be sufficient....I know someone said it is good practice to
avoid this, but I can't think any other methods which select a cell.
Maybe someone else can answer this. 8)

Application.Goto Sheets("Sheet1").Range("a1")

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[BeSmart]

Thank you all for your help.
I also found it strange to be advised to not use ActiveWorkbook, Select etc...
Perhaps I haven't understood and explained the situation properly - here is
the previous post and reply:
______________
Problem previously posted:
I have Buttons on worksheets that are assigned to macros in module 1 within
the current open workbook.
The macro copies the active worksheet, formats it (copy/paste values to
remove formulas, delete buttons not required), and moves it out into a
separate workbook.
The 1st time I click on the button it works great.
The 2nd time I click - the buttons have been re-assigned to Book1!"mba"
which doesn't have any macros
_____________________
Joel responded with: (although he was confused whether I was opening a
workbook - it's already open)
Excel doesn't delete the VBA project when a workbook is closed so the
project is still active when you run the macro a 2nd time. You always want to
run the macro from the first workbook that was opened. You should always
refere to the main workbook using THISWORKBOOK.
Try to Avoid using Activeworkbook. When you open a workbook use

Set bk = workbooks.open(filename:="c:\temp\book1.xls")

Then use bk to reference the new workbook. for some reason if you open a
text file as a workbook the statement above doesn't work. So I immediately
after opening the workbook use a set statement to make a variable equal to
the active workbook

Set bk = activeworkbook

A common problem with VBA is that the focus shifts from one object to
another without you knowing it is happening. for example when you open a
workbook the focus changes to the workbook that you opened to one of the
sheet of the workbook (which ever sheet was the active sheet when the
workbook was closed).

Without seeing your code I can't tell how to fix your problem. but is is
good practive to avoid usiong the following methods:
1) ActiveSheet
2) ActiveCell
3) select
4) Selection
With VBA it is inconsistenet and some commands only work with the above
methods so that is why I say Avoid.
_________________
Here is the original code:
Sub copyplan()

ActiveSheet.Copy After:=Sheets(Sheets.Count)
Application.Goto Reference:="PlanCPTrange"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Goto Reference:="GRPpot"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
ActiveSheet.Move
ActiveSheet.Buttons.Select
Selection.Delete
Columns("C").Select
Cells.Find(What:=".", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Activate
ActiveCell.Replace What:=".", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub


Your help with fixing my problem would be greatly appreciated.

 

[BeSmart]

Hi Mike
I tried using your code below, but got an error of:
Run-time error 9 "Subscript out of range"
and it debugged at:
ActiveSheet.Copy After:=Workbooks("MyBookName.xls").Sheets(1)

Does this mean that a workbook called "MyBookName" has to be created by the
user each time before they running the macro?
I can't rely on users to do this - therefore I was copying and moving the
active worksheet out and then letting them save it at the end.
PS - I'm using Excel 2003 so I changed it from .xlsm to .xls.