Hi, all.
How do I copy only 3 worksheets from a book with 9 to a new workbook . I do not know the name/path of the new workbook because the user will save it where he wants.
Please help!, I have tried many things, but it creates a workbook for every worksheet or it happens other weird things.
Thanks.
Jorge.
Copying selected Worksheets to new workbook
epilotusa
Hi Jorge,
Here's some info from our support engineer:
Let’s talk about following Excel VBA issue. Base on my understanding, our buddy wants to copy the Excel Worksheet across the Workbook (Even a new Workbook) and he met some problem on doing this. I suppose the key issue will be 1) Howto enumerate the existing Workbooks and the Worksheets it contains. 2) Howto create a new Workbook (Optional) 3) Copy Worksheets across Workbooks. I will provide sample code regarding these three points:
1. Howto enumerate the existing Workbooks and the Worksheets it contains
=============================================
Public Type XLWorkbookInfo
xlFilePath As String
xlName As String
xlSheets() As String
End Type
Public Function GetOpenWorkbooks() As XLWorkbookInfo()
Dim openWorkbooks() As XLWorkbookInfo
ReDim openWorkbooks(0)
If Application.Workbooks.Count <= 0 Then GetOpenWorkbooks = Empty
Dim xlBook As Workbook
For Each xlBook In Application.Workbooks
ReDim Preserve openWorkbooks(UBound(openWorkbooks) + 1)
Dim xlBookInfo As XLWorkbookInfo
With xlBookInfo
.xlFilePath = xlBook.FullName
.xlName = xlBook.Name
If xlBook.Sheets.Count <= 0 Then
.xlSheets = Empty
Else
ReDim .xlSheets(0)
Dim xlSheet As Worksheet
For Each xlSheet In xlBook.Sheets
ReDim Preserve .xlSheets(UBound(.xlSheets) + 1)
.xlSheets(UBound(.xlSheets)) = xlSheet.Name
Next
End If
openWorkbooks(UBound(openWorkbooks)) = xlBookInfo
End With
'Output
MsgBox openWorkbooks(UBound(openWorkbooks)).xlName & " -> " & openWorkbooks(UBound(openWorkbooks)).xlFilePath
Dim strSheet As String
Dim idx As Integer
strSheet = ""
For idx = 1 To UBound(openWorkbooks(UBound(openWorkbooks)).xlSheets)
strSheet = strSheet & openWorkbooks(UBound(openWorkbooks)).xlSheets(idx) & Chr(13)
Next
MsgBox openWorkbooks(UBound(openWorkbooks)).xlName & " -> " & strSheet
Next
GetOpenWorkbooks = openWorkbooks
End Function
==============================================
2. Howto create a new Workbook (Optional)
==============================================
Sub NewSheet()
Application.Workbooks.Add
End Sub
==============================================
3. Copy Worksheets across Workbooks
==============================================
Sub CopySheet() ‘G is the name of source Worksheet. It will be inserted before ‘Sheet1’ in Workbook ‘Book1’
Sheets("G").Select
Sheets("G").Copy Before:=Workbooks("Book1").Sheets(1)
End Sub
==============================================
-brenda (ISV Buddy Team)