Prev: statistical database attack verses defense for online game
Next: vba excel 2007 data mining
From: BeSmart on 13 Mar 2010 21:58
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.
--
BeSmart


"Mike H" wrote:

> 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.
>
>
> "BeSmart" wrote:
>
> > 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
> >
> > --
> > Thank for your help
> > BeSmart
First  |  Prev  | 
Pages: 1 2
Prev: statistical database attack verses defense for online game
Next: vba excel 2007 data mining