Excel VBA Pasting a Range into New Workbook

Question

I am having some trouble with this one. I am trying to paste a range, that varies based on a selection from a userform, to a new workbook. The pasting is not working. I've gotten feedback that I need to eliminate the use of activesheet, and name the specific targeted workbook. I don't know how to name it if it is a new workbook. I also don't know how to save it to the users desktop.

Anyways. The code fails here (1st line):

Workbooks("Restaurant Manager -Master.xlsx").Worksheets(wsexport).Activate
Workbooks("Restaurant Manager -Master.xlsx").Worksheets(wsexport).Unprotect
Workbooks("Restaurant Manager -Master.xlsx").Worksheets(wsexport).Range("BA6::BT200").Copy

Below is the full code, with so you can see the action I am trying to take - passing the worksheet name variable from the userform cboExportInvoiceWeek. Ideally, the user clicks a button and a new csv file is created. The only variable I have is the worksheet (one for each week of year). The cell values are static.

Again, my issue is the actual pasting into the new workbook.

Private Sub cmbInvoicesExport_Click()

Application.ScreenUpdating = False

Dim CurrentFileName As String
CurrentFileName = ActiveWorkbook.Name
Debug.Print "Active File: " + CurrentFileName

Dim wsexport As String
wsexport = cboExportInvoiceWeek.Value    

Workbooks("Restaurant Manager -Master.xlsx").Worksheets(wsexport).Activate
Workbooks("Restaurant Manager -Master.xlsx").Worksheets(wsexport).Unprotect
Workbooks("Restaurant Manager -Master.xlsx").Worksheets(wsexport).Range("BA6::BT200").Copy

Set NewBook = Workbooks.Add

NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial (xlPasteValues)
NewBook.SaveAs Filename:=NewBook.Worksheets("Sheet1").Range("E3").Value

Application.DisplayAlerts = False
Application.DisplayAlerts = True
Application.CutCopyMode = False
Workbooks(CurrentFileName).Activate
Application.ScreenUpdating = True

End Sub

put Option Explicit at the top of your code and also, are you receiving any error messages? And your DIsplayAlerts code, as is, isn't doing anything. — QHarr
I tabbed out a line in between the display alerts to close the activeworkbook. I've since taken it all out. — jpend314
Maybe try to activate the workbook first then in a separate line the sheet within that workbook — QHarr

AcsErno AcsErno · Accepted Answer · 2017-12-30T08:52:00

Generally: I would check err.number after each suspicious step. In details:

VBA drops subscript error when either the file is not open or the referred worksheet does not exist. So I would split Workbooks("Restaurant Manager -Master.xlsx").Worksheets(wsexport).Activate to

Workbooks("Restaurant Manager -Master.xlsx").Activate

Worksheets(wsexport).Activate
For SaveAs I would put the desired filename to a variable and see how it looks. You might have to specify a path either. And you can see err.description too.
You can find the path to desktop in a few ways, e.g. so:

sPath=environ$("USERPROFILE") & "\desktop\"

See more on this here: enter link description here

Excel VBA Pasting a Range into New Workbook

1 Answers