CODEWITHSUNDEEP
vbaexcel
DChantal
DChantal

Reputation: 47

Transfer Worksheet to New Workbook

Incredibly novice with excel VBA. I've found this question in many different places but can't seem to manipulate the code I've tried to work. Essentially, I have a workbook that creates a new workbook, copies one sheet entirely, saves the workbook, and then needs to transfer a second sheet from the original workbook. I've gotten it done with this:

Wb1.Sheets("Scorecard").Range("A1:M37").Value(11) = Wb.Sheets("Scorecard").Range("A1:M37").Value(11)

where Wb1 is the "transfer to" book and Wb is the "transfer from" workbook. The problem is this doesn't the headers/footers, picture (logo), or hide columns that are meant to be hidden.

I've tried this but it just seems to open another book and freeze my system and open up a third book:

Wb.Sheets("Scorecard").Copy
Wb1.Sheets("Scorecard").PasteSpecial xlPasteValues

I feel like I'm missing something very obvious but I'm tired of banging my head against the wall on this. Full code up this point is below:

Sub NewWb()
Dim Aname As String
Dim HospName As String
Dim DateDone As String
Dim xPath As String
Dim Wb As Workbook
Dim Wb1 As Workbook
Set Wb = ThisWorkbook
xPath = Application.ActiveWorkbook.Path
Aname = "CS Diversion Prevention Assessment"
HospName = Sheets("Hospital ID").Range("I8")
DateDone = Replace((Sheets("Hospital ID").Range("I13").Text), "/", "-")

Application.ScreenUpdating = False
ActiveWorkbook.Sheets("Compliance Checklist").Copy
With ActiveSheet.UsedRange
  .Value = .Value
End With
ActiveSheet.Shapes("Button 1").Delete
Sheets.Add.Name = "Scorecard"
ActiveWorkbook.SaveAs Filename:=xPath & "\" & HospName & "." & Aname & "." &     DateDone & ".xlsx"
Set Wb1 = Workbooks.Open(xPath & "\" & HospName & "." & Aname & "." & DateDone & ".xlsx")

Wb.Activate

Thanks in advance

Upvotes: 2

Views: 252

Answers (2)

cxw
cxw

Reputation: 17041

This worked for me on Excel 2013, using the Worksheet.Copy method. I also tried a move, using the Worksheet.Move method, but it took ~10 seconds to move the sheet (!).

Public Sub copy_it_buster()
    Dim to_copy As Worksheet, copy_after As Worksheet
    Set to_copy = Workbooks("Book2").Sheets(1)  ' Or whatever worksheet you're moving from 
    Set copy_after = Workbooks("Book3").Sheets(1)  ' Or whatever worksheet you want to copy after.
        ' Note that this is in a different workbook!
    to_copy.Copy After:=copy_after
    'to_copy.Move After:=copy_after ' if you don't want to keep it where
                                    ' it was originally
End Sub

Edit 2: After copying, to convert all the formulas to flat values, try this courtesy of Ron de Bruin's MVP site:

dim sh as Worksheet
set sh = ' whatever your new worksheet is
With sh.UsedRange
    .Value = .Value
End With

Edit For the Move case I originally tested, when I started with:

Book2                   Book3
Sheet1                  Sheet1

I wound up with:

no Book2!               Book3
                        Sheet1    Sheet1 (2)

Edit for Copy, I get:

Book2                   Book3
Sheet1                  Sheet1 Sheet1(2)

Note that this requires the target workbook already exist. Also, if you want to copy/move to before an existing worksheet instead of after, just change After to Before in the Copy call.

And a PS: To reduce frustration on this and future projects, use names like FromWorkbook and ToWorkbook rather than Wb and Wb1. It may not seem like much, but in my experience it significantly reduces mental workload while debugging!

Upvotes: 2

Rob B
Rob B

Reputation: 1

Have you considered saving/copying the source workbook (Wb) as a new workbook (Wb1), then editing the new one?

So instead of creating a new blank wb and adding to it... Create a copy of the original, then remove from it what you need.

Upvotes: 0

Related Questions