0

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.

Improve this question
12
  • What exactly are you trying to achive? Intead of Cells.Select: Selection.Copy why not copy the worksheet across? Commented Jan 8, 2020 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? Commented Jan 8, 2020 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. Commented Jan 8, 2020 at 13:15
  • @VitaliyPrushak - its a project i'm working on. Commented Jan 8, 2020 at 13:28
  • @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 post. Commented Jan 8, 2020 at 13:41

2 Answers 2

Reset to default
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)

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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
Improve this answer

2 Comments

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 the title of your question is "Save clipboard when closing workbook" , check out this SO Question concerning this issue, read Mathieu Guidon's answer.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.