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
