Excel VBA Copy and Pasting Variable Selection (changing worksheets) to a new Workbook and saving as CSV

0
votes

I nearly have this done, except nothing is being pasted. MY PROBLEM: The exporting and save functions seems to work as it creates a new workbook and saves, but it is empty.

I am having the user select which worksheet they want to extract a static range from (the variable is the worksheet). Each sheet is named by the week number (52 separate worksheets) plus a couple of background data sheets, which offsets the worksheet visible name from what excel calls the worksheet by 4. Meaning sheet1 is called "Labor", while sheet5 is called "1" - for the first week of the year.

Anyways, that variable is passed through the lstExportInvoiceWeek combobox by the user. from that selection, I want to copy a static range (BA6:BT200) and then paste it into a csv file.

Here is my code. The pasting isn't working. The new workbook is saving blank.

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
Worksheets(wsexport).Activate
Worksheets(wsexport).Unprotect

Range("BA6:BT200").Copy

Workbooks.Add Template:="Workbook"

Range("A1").Select

ActiveSheet.Paste

Dim file_name As Variant
file_name = Application.GetSaveAsFilename(FileFilter:="CSV (Comma delimited) (*.csv), *.csv")

If file_name <> False Then
    ActiveWorkbook.SaveAs Filename:=file_name
    MsgBox "File Saved!"
End If

Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.CutCopyMode = False
Workbooks(CurrentFileName).Activate
Application.ScreenUpdating = True
End Sub
1
excelvbacsv

1 Answers

0
votes

Avoid using ActiveSheet Instead use Workbooks("YourWorkbook.xls").Worksheets("Sheet1").Activate

You need to reference the Workbook target to paste the data.