PDA

View Full Version : Solved: Copy Worksheet to new Workbook



jo15765
12-09-2011, 06:09 AM
I have this code below that will copy all worksheets to a new workbook. What I am now needing is to only copy the 2nd tab of a worksheet to a new workbook, and it is turning out to be much more difficult of a task than I originally anticipated. Can someone take a look at my code, and point out my error:

Dim Varbooks As Variant
Dim wb As Workbook, wbFinal As Workbook
Dim i As Long
Const Path As String = "File Path Goes Here"

Varbooks = Array("Name of Workbook Goes Here")

Set wbFinal = ActiveWorkbook
Application.EnableEvents = False
For i = LBound(Varbooks, 1) To UBound(Varbooks, 1)
Set wb = Workbooks.Open(Path & Varbooks(i))
wb.Worksheets.Copy wbFinal.Worksheets(1)
wb.Close False
Next
Application.EnableEvents = True

Aflatoon
12-09-2011, 06:17 AM
If you only want to copy one sheet, then it would be:
wb.Worksheets(2).Copy wbFinal.Worksheets(1)

Rob342
12-09-2011, 06:36 AM
Try this

Option Explicit
Sub SaveSheet1()
Dim NewName As String
Dim wb As Workbook
Dim nm As Name
Dim ws As Worksheet
Dim wksCopy As Worksheet
Dim strFullname As String
If Not MsgBox("Copy specific sheets to a new workbook" & vbCr & _
"New Sheets Will Be Pasted As Values Only" _
, vbYesNo, "NewCopy") = vbYes Then Exit Sub

Application.EnableEvents = False
Application.ScreenUpdating = False

'// Copy specific sheets as in the named Array
On Error GoTo ErrCatcher
Sheets(Array("Sheet1")).Copy
On Error GoTo 0

Set wb = ActiveWorkbook

For Each ws In wb.Worksheets
ws.UsedRange.Value = ws.UsedRange.Value
Next ws

'//Display Input box to name new file
NewName = InputBox("Please Specify the name of your new workbook", "New Copy")

'//Save it with the NewName and in the same directory as original
wb.SaveAs ThisWorkbook.Path & "\" & NewName & ".xls"



wb.Close SaveChanges:=False
Application.ScreenUpdating = True
Application.EnableEvents = True

On Error Resume Next
' Kill strFullname
On Error GoTo 0
Exit Sub



ErrCatcher:
MsgBox "Specified sheets do not exist within this workbook"
End Sub

jo15765
12-09-2011, 07:29 AM
Rob Each sheet is in a seperate workbook, It looks to me like your code is loooking for them in the same workbook. (And it looks like I forgot to put that in my original post)


Aflatoon how could I add a line in there that will automatically save the workbook for me? I tried adding in..

' With wb
' .SaveAs Filename:="Location" & "FileName"
' End With


But that threw a crazy VB error?

jo15765
12-09-2011, 07:52 AM
Disregard my previous post, I modified the save code to:

Set wb = ActiveWorkbook
wb.SaveAs Filename:="Location" & "Filename"
wb.Close


And it saved no trouble....As always thank you for the support!