Array

[kevcar40]

Hi
I am using the formula below to copy a worksheet, which is then pasted out to another workbook.

ThisWorkbook.Worksheets(Array("Tab name")).Copy

is it possible for me to use a cell value instead of having to enter the worksheet name

or

read the Worksheet tab and use this value

thanks

kevin

 

[GS]

kevcar40 wrote on 6/8/2011 :

Hi
I am using the formula below to copy a worksheet, which is then pasted out to
another workbook.

ThisWorkbook.Worksheets(Array("Tab name")).Copy

is it possible for me to use a cell value instead of having to enter the
worksheet name

or

read the Worksheet tab and use this value

thanks

kevin

If it's the active sheet:
ThisWorkbook.ActiveSheet.Copy

If several sheets:
Group them first, then use...

ActiveWindow.SelectedSheets.Copy
OR possibly

ThisWorkbook.Sheets(Array(sWksList)).Copy '//not tested

A reusable function for grouping sheets:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' GroupSheets()
' This procedure requires only the necessary amount of coding be used
' in the Caller. By default, it requires passing only the first arg.
' Use Example: GroupSheets "Sheet1,Sheet3"
' creates a group of only those sheets.
' To group all sheets in a workbook except those sheets:
' GroupSheets "Sheet1,Sheet3", False
' To group all sheets in a workbook pass an empty string:
' GroupSheets "", False
' You can pass the Wkb arg to specify any open workbook.
' (The Wkb doesn't need to be active for this purpose)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub GroupSheets(Sheetnames As String, _
Optional bInGroup As Boolean = True, _
Optional Wkb As Workbook)
' Groups sheets in Wkb based on whether Sheetnames
' are to be included or excluded in the grouping.
' Arg1 is a comma delimited string. (ie: "Sheet1,Sheet3")

Dim Shts() As String, sz As String
Dim i As Integer, Wks As Worksheet, bNameIsIn As Boolean

If Wkb Is Nothing Then Set Wkb = ActiveWorkbook
For Each Wks In Wkb.Worksheets
bNameIsIn = (InStr(Sheetnames, Wks.name) > 0)
If bInGroup Then
If bNameIsIn Then sz = Wks.name
Else
If bNameIsIn Then sz = "" Else sz = Wks.name
End If
If Not sz = "" Then '//build the array
ReDim Preserve Shts(0 To i): Shts(i) = sz: i = i + 1
End If
Next
ActiveWorkbook.Worksheets(Shts).Select
End Sub

 

[GS]

GS formulated on Wednesday :

Next
ActiveWorkbook.Worksheets(Shts).Select
End Sub

Oops! The above should read...
Next
Wkb.Worksheets(Shts).Select
End Sub

 

[Dave Peterson]

Since you're only copying a single sheet, you don't need the Array() portion:

ThisWorkbook.Worksheets("Tab name").Copy

You could use something like this to get the value from a cell on a worksheet:

ThisWorkbook.Worksheets(thisworkbook.worksheets("SomeSheet").range("A1").Value)).copy

or to save a bit of typing:

With thisworkbook
.worksheets(.worksheets("somesheet").range("a1").value).copy
end with