0

I have the following VBA code that copys specific values from the open workbook and then pastes them to another and then saves it. Recently this macro stopped working and throws an error message. I would appreciate some help fixing this.

Error Message

Run-time error: 1004: To copy all cells from another worksheet to this worksheet, make sure you paste them into the first cell (A1 or R1C1)

VBA CODE

Sub ExportImportFile()
Worksheets("Estimate Import Calc").Activate
    ActiveWorkbook.Worksheets("Estimate Import Calc").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Estimate Import Calc").Sort.SortFields.Add2 Key:= _
        Range("n2:n19"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Estimate Import Calc").Sort
        .SetRange Range("A1:o19")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
'Reset LastRow
LastRow = Range("n" & Rows.Count).End(xlUp).Row
'Remove Zeros
ActiveSheet.Range("$A$1:$n$" & LastRow).AutoFilter Field:=14, Criteria1:="0", Operator:=xlOr
FirstRow = Worksheets("Estimate Import Calc").AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells().Row
Rows(FirstRow).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Delete
ActiveSheet.ShowAllData
Range("A2:o19").Select
    Cells.Select
    Selection.Copy
        Sheets("Estimate Import").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
         :=False, Transpose:=False
        Sheets("Estimate Import").Copy
        Worksheets("Estimate Import").Rows(1).Delete
            ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Range("A1").Value & "_" & "ProjectBudget_EstimateImport.CSV", FileFormat:=xlCSV, CreateBackup:=False
            ActiveWorkbook.ActiveSheet.Columns("A").Delete
            ActiveWorkbook.Save
            ActiveWorkbook.Close
End Sub
mullinsjo
  • 5
  • 2
  • Pretty much requisite reading for Excel + VBA: [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BigBen Oct 05 '22 at 18:34
  • `Cells.Select`, `Selection.Copy` copies the entire sheet. Do you want to do that? – BigBen Oct 05 '22 at 18:35
  • I was not aware of the issue with the select function. Thanks for the tip! The person on my team selected the whole sheet because the number of rows vary depending on the values on the main sheet. Is there a way to dynamically get the last row? – mullinsjo Oct 05 '22 at 18:41
  • That's also pretty much requisite reading for Excel + VBA: [how to find the last used Cell](https://stackoverflow.com/questions/11169445/find-last-used-cell-in-excel-vba). You already have it with `LastRow = Range("n" & Rows.Count).End(xlUp).Row`, however it appears that you never actually use `LastRow`. – BigBen Oct 05 '22 at 18:42
  • It is apparently evident I am a beginner haha. Thanks for the help! I'll make those updates. – mullinsjo Oct 05 '22 at 18:52
  • It is great to see beginners make an effort! Keep up the good work. – BigBen Oct 05 '22 at 18:54

0 Answers0