如何用VBA在excel中保存格式和数值的数据？

Sub SaveDataWithFormat()
    Dim rng As Range
    Dim newDataSheet As Worksheet
    Dim dataValues() As Variant
    Dim dataFormats() As Variant
    Dim i As Long, j As Long
    
    ' 定义需要保存的数据范围
    Set rng = Range("A1:E10")
    
    ' 将数据和格式分别保存到数组中
    dataValues = rng.Value
    dataFormats = rng.NumberFormat
    
    ' 添加一个新的工作表来保存数据和格式
    Set newDataSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    
    ' 将保存的数据和格式写入到新的工作表中
    With newDataSheet
        For i = 1 To rng.Rows.Count
            For j = 1 To rng.Columns.Count
                .Cells(i, j).Value = dataValues(i, j)
                .Cells(i, j).NumberFormat = dataFormats(i, j)
            Next j
        Next i
    End With
    
    ' 可以根据需要自定义保存的文件名和路径
    newDataSheet.SaveAs "保存路径\文件名.xlsx"
    newDataSheet.Close
    
    MsgBox "数据保存完成！"
End Sub