Copy and Move

The Copy and Move methods of the Worksheet object allow you to copy or move one or more worksheets in a single operation. They both have two optional parameters that allow you to specify the destination of the operation. The destination can be either before or after a specified sheet. If you do not use one of these parameters, the worksheet will be copied or moved to a new workbook.

Copy and Move do not return any value or reference, so you have to rely on other techniques if you want to create an object variable referring to the copied or moved worksheets. This is not generally a problem, because the first sheet created by a Copy operation, or the first sheet resulting from moving a group of sheets, will be active immediately after the operation.

Say you have a workbook like that shown in Figure 3-3 and want to add another worksheet for February — and then more worksheets for the following months. The numbers on rows 3 and 4 are the input data, but row 5 contains calculations to give the difference between rows 3 and 4. When you copy the worksheet, you will want to clear the input data from the copies but retain the headings and formulas.

_ n

A 1 B ¡ C

D

E !

f '

i

1

1

2

Apples Mangoes Bananas

Lychees

Li

3

Production 90-7 956

937

961

4

Sales 364 947

915

915

5

1 57l 9

22

43

G

7

► h Jan 20D7 -J J

3k !_

mi . -

HI

The following code creates a new monthly worksheet that is inserted into the workbook after the latest month. It copies the first worksheet, removes any numeric data from it but leaves any headings or formulas in place, and then renames the worksheet to the new month and year:

Sub NewMonth()

'Copy the first worksheet in the active workbook

'to create a new monthly sheet with name of format "mmm yyyy".

'The first worksheet must have a name that is in a recognizable

'date format.

Dim wks As Worksheet

Dim dteFirstDate As Date

Dim iFirstMonth As Integer

Dim iFirstYear As Integer

Dim iCount As Integer

'Initialize counter to number of worksheets iCount = Worksheets.Count

'Copy first worksheet after last worksheet and increase counter Worksheets(1).Copy After:=Worksheets(iCount) iCount = iCount + 1

'Assign last worksheet to wks Set wks = Worksheets(iCount)

'Calculate date from first worksheet name dteFirstDate = DateValue(Worksheets(1).Name)

'Extract month and year components iFirstMonth = Month(dteFirstDate) iFirstYear = Year(dteFirstDate)

'Compute and assign new worksheet name

wks.Name = _

Format(DateSerial(iFirstYear, iFirstMonth

+ iCount - 1, 1),

"mmm yyyy")

'Clear data cells in wks, avoiding error if

there is no data

wks.Cells.SpecialCells(xlCellTypeConstants,

1).ClearContents

End Sub

The result of the copy is shown in Figure 3-4.

ShceK jpy: i:n-

-Hi

A 1 B ! C 1 D

E !

F !fi

1

2

Apples Mangoes Bananas

Lychees

i

3

Production

4

Sales

5

t ol 0 0

a

6

7

1

a

n

► H Jan 1|J0~ Feb 2007 'J IJB

.

Figure 3-4

NewMonth first determines how many worksheets are in the workbook and then copies the current worksheet, appending it to the workbook. It updates the number of worksheets in iCount and creates an object variable wks that refers to the copied sheet. It then uses the DateValue function to convert the name of the January worksheet to a date.

NewMonth extracts the month and year of the date into the two integer variables iFirstMonth and iFirstYear using the Month and Year functions. It then uses the DateSerial function to calculate a new date that follows on from the last one. This calculation is valid even when new years are created, because DateSerial, like the worksheet DATE function, treats month numbers greater than 12 as the appropriate months in the following year.

NewMonth uses the VBA Format function to convert the new date into "mmmyyyy" format as a string. It assigns the text to the Name property of the new worksheet. Finally, NewMonth clears the contents of any cells containing numbers, using the SpecialCells method to find the numbers. SpecialCells is discussed in more detail in the following chapter on the Range object. The On Error Resume Next statement suppresses a run-time error when there is no numeric data to be cleared.

0 0

Post a comment