Save clipboard when closing workbook VBA

Question

It seems like a easy question, yet I can't seem to find the correct answer on Google.

What I want to do is open a workbook, copy a section and then close the workbook while saving the section I just copied.

I'm aware of the function to disable the clipboard prompt:

Application.CutCopyMode = False
ActiveWindow.Close

But this does not save the clipboard. Thus far I have written the following code to do so:

Sub Input()

Application.ScreenUpdating = False

Dim wb As Workbook
Dim wbPad As String

On Error GoTo ErrHandler

wbPad = ThisWorkbook.Sheets("Voorblad").Range("C10").Value
    Set wb = Workbooks.Open(wbPad)

    Cells.Select
    Selection.Copy
    Windows("Masterfile.xlsm").Activate
    Worksheets("INPUT").Activate
    Cells.Select
    ActiveSheet.Paste
    Range("A1").Select
    Worksheets("Voorblad").Activate

Exit Sub

ErrHandler:
    MsgBox ("Bestand niet gevonden. Controleer de maand en de naam van het bestand dat je wilt openen")

End Sub

If this is not possible, I would like to .Activate the workbook I opened using the cell reference and close this.

What exactly are you trying to achive? Intead of `Cells.Select: Selection.Copy` why not copy the worksheet across? — Siddharth Rout, Jan 08 '20 at 13:11
`What I want to do is open a workbook, copy a section and then close the workbook while saving the section I just copied.` Another question - why do you need this? What do you want to do with this data further? — Vitaliy Prushak, Jan 08 '20 at 13:14
I’m trying to close the workbook I opened to copy data. And I can do this by closing it while the workbook is activated (the option to close and save clipboard) or close it at the end of the macro, but I can’t seem to select (or activate) the workbook I opened to copy the data anymore. — HoekPeter, Jan 08 '20 at 13:15
@HoekPeter I'm asking for purpose 'cos if you are going to use it further in Excel - most likely you won't need to use a clipboard at all. If you do need clipboard - check [this](https://stackoverflow.com/questions/35416662/text-to-clipboard-in-vba-windows-10-issue/42514269#42514269) post. — Vitaliy Prushak, Jan 08 '20 at 13:41
@VitaliyPrushak, this is the only time I will need to copy the data. Would you recommend using the clipboard or closing the workbook at the end of the macro? — HoekPeter, Jan 08 '20 at 13:43
@HoekPeter I can't recommend anything now, because I don't know where you are going to use this data further. — Vitaliy Prushak, Jan 08 '20 at 13:57
@VitaliyPrushak Hi Vitality, sorry for the late response. In this case it doesn't matter much what steps follow. I want to close the workbook that i defined in the beginning of the code: `wbPad = ThisWorkbook.Sheets("Voorblad").Range("C10").Value Set wb = Workbooks.Open(wbPad)` I cant seem to do this. — HoekPeter, Jan 09 '20 at 09:49
@HoekPeter So in this case you have to options. Option 1 - in case you leave Excel application running - put the data into a Data variable (e.g DataObject) and use `wb.close` to close that particular workbook. Data will stay in memory until you dispose that variable or quit Excel application. Option 2 - in case when you want to close the Excel application at all - you will need to access Window's clipboard and you will be able to do it only through Windows APIs. In this case - look [here](https://stackoverflow.com/questions/35416662/text-to-clipboard-in-vba-windows-10-issue/42514269#42514269) — Vitaliy Prushak, Jan 09 '20 at 10:18
@VitaliyPrushak, Thanks for your suggestions and the link. Any chance you can help me with my other obstacle; to select wbPad which I opened at the beginning of the code? I've tried: Workbooks("wbPad").Activate or wbPad.Activate, but that justs gives me errors. — HoekPeter, Jan 09 '20 at 10:29
You have assigned it to wb variable `Set wb = Workbooks.Open(wbPad)`, so you need to use `wb.activate` statement. BUT. Using `.Select` and `.Activate` is commonly a very bad practice, see [this thread](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Vitaliy Prushak, Jan 09 '20 at 10:49

score 0 · Answer 1 · answered Jan 08 '20 at 14:17

0

Maybe you could just skip the whole .select and .activate commands and use the optional Destination parameter of the .copy function.

(https://learn.microsoft.com/de-de/office/vba/api/excel.range.copy)

answered Jan 08 '20 at 14:17

fourthquartermagic

135
1
13

score 0 · Answer 2 · answered Jan 08 '20 at 19:10

Since you did not provide how you want to save the range, I've added multiple basic examples below.

OPT1 - Save as .xlsx or .csv

Dim cpyRng As Range, newWb As Workbook, sPath As String

Application.DisplayAlerts = False 'remove system alert prompts

Set cpyRng = ThisWorkbook.Sheets("Sheet1").Range("A1:C10") 'Change sheet and range as needed

sPath = ThisWorkbook.Path & "\"

    Set newWb = Workbooks.Add

    With newWb
        cpyRng.Copy
        .Sheets("Sheet1").Cells(1, 1).PasteSpecial Paste:=xlPasteValues
        .SaveAs Filename:=sPath & "Test" & "_" & Format(Date, "yyyymmdd") & ".xlsx", FileFormat:=51 'change file name to suit

        'If you want to save as .csv use
        '.SaveAs Filename:=sPath & "Test" & "_" & Format(Date, "yyyymmdd") & ".csv", FileFormat:=6

        .Close
    End With

    'save your workbook and quit Excel
    ThisWorkbook.Save = False 'use "True" if you want to save changes
    Application.Quit

    Application.DisplayAlerts = True 'Turn system alert prompts back on(best practice)

OPT2 - Save as .pdf

Dim cpyRng As Range, sPath As String

Application.DisplayAlerts = False 'remove system alert prompts

Set cpyRng = ThisWorkbook.Sheets("Sheet1").Range("A1:C10") 'Change sheet and range as needed

sPath = ThisWorkbook.Path & "\"

    'Change file name to suit
    cpyRng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sPath & "Test" & "_" & Format(Date, "yyyymmdd") & _
    ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

Application.DisplayAlerts = True 'Turn system alert prompts back on(best practice)

OPT3 - Save as Word Doc

Dim cpyRng As Range
Set cpyRng = ThisWorkbook.Sheets("Sheet1").Range("A1:C10") 'Change sheet and range as needed

Dim objWord As Object
Set objWord = CreateObject("Word.Application")

    cpyRng.Copy

    With objWord
        .Visible = True
        .Documents.Add
        .Selection.Paste
    End With

Application.CutCopyMode = False

Set objWord = Nothing

Thanks for the response GMalc. I'm not looking to save anything however. What I want to do at this point is the following: Select the workbook wbPad which i defined in the beginning with: `wbPad =ThisWorkbook.Sheets("Voorblad").Range("C10").Value` — HoekPeter, Jan 09 '20 at 09:56
@HoekPeter the title of your question is "Save clipboard when closing workbook" , check out this [SO Question](https://stackoverflow.com/questions/46772270/excel-vba-global-variable) concerning this issue, read Mathieu Guidon's answer. — GMalc, Jan 09 '20 at 12:57

Save clipboard when closing workbook VBA

2 Answers2