New workbook created unintentionally

Question

I'm trying to copy a worksheet ("ReceivingRecords") from the workbook ("InventoryControlSystemV1.1") and paste it in a new workbook ("RecordBook"). I have created a temporary workbook named "Temp.xls" which allows me to use the SaveCopyAs method to create my new workbook "RecordBook".

When I run the procedure, "RecordBook" is created as intended but with only one entry (The text 'InventoryControlSystemV1.1.xls') in cell A1.

The worksheet that I want to copy is then pasted into a new, unnamed, workbook. I can't figure out where or why this new workbook is being created.

Here is the code for this procedure:

Sub WriteReceivingToRecords()

    Dim UsedRng As Range
    Dim LastCol As Long
    Dim BeginDate, EndDate
    Dim NameString
    Dim FormatBeginDate, FormatEndDate
    Dim BackupQuest As Integer
    Dim BackupMsg As String

    'Confirmation dialog box to avoid mistakes
    BackupMsg = "This will create a new workbook for the period" & vbNewLine
    BackupMsg = BackupMsg & " since the last backup was made, and will clear" & vbNewLine
    BackupMsg = BackupMsg & " the receiving records in this workbook." & vbNewLine & vbNewLine
    BackupMsg = BackupMsg & "Are you sure you want to back up the receiving records?"
    BackupQuest = MsgBox(BackupMsg, vbYesNo, "Back-up Records")

    If BackupQuest = vbNo Then
        Exit Sub
    Else

    '   Find start and end dates of receiving - To use for worksheet title
        Workbooks("InventoryControlSystemV1.1.xls").Activate
        Worksheets("ReceivingRecords").Activate
        Set UsedRng = ActiveSheet.UsedRange
        LastCol = UsedRng(UsedRng.Cells.Count).Column
        Do While Cells(2, LastCol) = ""
                LastCol = LastCol - 1
        Loop
        EndDate = Cells(2, LastCol).Text
        BeginDate = Cells(2, 2).Text

        FormatBeginDate = Format(BeginDate, "d mmmm yy")
        FormatEndDate = Format(EndDate, "d mmmm yy")
        NameString = "M-Props Receiving Records " & FormatBeginDate & " To " _
            & FormatEndDate & ".xls"



        Workbooks("InventoryControlSystemV1.1.xls").Sheets("ReceivingRecords").Copy

        Workbooks.Open Filename:="Temp.xls"
        Workbooks("Temp.xls").Activate
        Workbooks("Temp.xls").Worksheets("Sheet1").Paste _
            Destination:=Workbooks("Temp.xls").Worksheets("Sheet1").Range("A1")

        Workbooks("Temp.xls").SaveCopyAs NameString & ".xls"
        Workbooks("Temp.xls").Close False

    End If

End Sub

You may benefit from these two tips: use Option Explicit and [avoid using `Select`](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — including avoiding using `Activate` — JustinJDavies, Jan 31 '13 at 14:25
see TLiebe's answer on [this post](http://stackoverflow.com/questions/2454552/whats-an-option-strict-and-explicit) on `Option Explicit` — JustinJDavies, Jan 31 '13 at 14:26
Thanks for the replies. I have removed all traces of select and activate, and have set option explicit (which was on before), and I still have the same problem. The entries which are pasted into the "RecordBook" workbook seem to be the last thing I copied using ctrl+c. — user2029301, Jan 31 '13 at 15:04
Please use the upvote system to say thank-you (as/when you are allowed to). — JustinJDavies, Jan 31 '13 at 15:06
Could you update the question with your new code? It should be much more readable now. — JustinJDavies, Jan 31 '13 at 15:16
You are trying to Paste a Worksheet into a Range which I believe is causing the error. Also, your variable NameString already has the ".xls" extension which you added again. And, I'm not sure if this is another issue, but how do you run macros from the workbook "InventoryControlSystemV1.1.xls" without the .xlsm extension? — deusxmach1na, Jan 31 '13 at 16:21
When you call `Worksheet.Copy` without a "Before/After" parameter it will always copy that sheet to a new workbook. You need to first open the destination worksbook and then copy the sheet Eg: `sheetToCopy.Copy After:=destWorkbook.sheets(1)` — Tim Williams, Jan 31 '13 at 18:26

score 0 · Answer 1 · answered Jun 19 '13 at 15:27

0

Replace

Workbooks("InventoryControlSystemV1.1.xls").Sheets("ReceivingRecords").Copy

with

Workbooks("InventoryControlSystemV1.1.xls").Sheets("ReceivingRecords").Cells.Copy

That should do it.

answered Jun 19 '13 at 15:27

Abe Gold

2,307
17
29

New workbook created unintentionally

1 Answers1