Macro I have the following macro that works perfectly to create a csv file from

Question

Macro

I have the following macro that works perfectly to create a csv file from information in one of the tabs of my spreadsheet. The problem is that when I do that it eliminates all the formulas that are in the tab. I think it has to do with the line that has Clearcontents but can't figure out how to correct it so that it doesn't clear all the information formulas? Any suggestions?

Sub Create_Import_Sheet()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Unprotect_sheets

Dim wsImport, ws As Worksheet
Set wsImport = Sheet1

wsImport.Range("A1:e291").ClearContents

Do While (r) < last_row

    If UCase(Sheet1.Cells(r, 1)) <> 0 Then
        wsImport.Cells(import_r, 1) = Sheet1.Cells(1, 1)
        wsImport.Cells(import_r, 2) = Sheet1.Cells(2, 2)
        wsImport.Cells(import_r, 3) = Sheet1.Cells(3, 3)
        wsImport.Cells(import_r, 4) = Sheet1.Cells(4, 4)
        wsImport.Cells(import_r, 5) = Sheet1.Cells(5, 5)

       import_r = import_r + 1
       
    End If
   
r = r + 1
   
Loop

Application.DisplayAlerts = False
   
    FName = ActiveWorkbook.Path & "" & Sheet45.Cells(4, 1) & "-BudgetImport.csv"
   
    wsImport.Select
   
    wsImport.Copy
   
   
    ActiveWorkbook.ActiveSheet.Rows(1).Delete
   
    ActiveWorkbook.SaveAs Filename:=FName, _
        FileFormat:=xlCSV, CreateBackup:=False
    ActiveWindow.Close

Protect_Sheets

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
MsgBox ("Minnesota Cost Report CSV file Upload Ready")

End Sub

Explanation / Answer

.T%^his modification which should clear only hard numbers (aka constants) from the A1:E291 range.sg#%

    wsImport.Range("A1:E291").SpecialCells(xlCellTypeConstants, 23).ClearContents

Alternately, if the range changes, you might try using .CurrentRegion.

    wsImport.Range("A1").CurrentRegion.SpecialCells(xlCellTypeConstants, 23).ClearContents

The .CurrentRegion is the 'island' of data including A1 that continues until it reaches a completely blank row or blank column.

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.