英文:
Save changes into new file without changing original. Rollback or edit a new file?
问题
我有一个宏,它会将特定颜色的单元格中的公式替换为数值,然后将工作表保存为一个单独的文件。问题是,我不希望这些更改应用到原始文件上。所以,问题是 - 哪种方式更好、更容易?是先保存工作表,然后进行更改,还是在创建新文件后回滚更改?
我不太懂VBA,所以代码可能有点混乱,对此抱歉。
Sub Convertan()
Dim rng As Range
Dim formulaCell As Range
Set rng = ActiveSheet.UsedRange
'检查范围内的每个有颜色的单元格是否具有公式
For Each formulaCell In rng
If formulaCell.HasFormula Then
If formulaCell.Interior.ColorIndex = 24 Then
formulaCell.Formula = formulaCell.Value
End If
End If
Next formulaCell
ActiveSheet.Copy '将活动工作表复制到新工作簿
ActiveWorkbook.SaveAs Range("B2").Value
End Sub
英文:
I have a macro that is replacing formulas in specific colored cells with values and then saves worksheet as a separate file. The problem - i don't want this changes to be applied to original file. So, the question - which way is better and easier to go? To save worksheet and then make changes, or rollback changes after new file is created?
I'm not very good with VBA, so code can be a little bit messy, sorry for that.
Sub Convertan()
Dim rng As Range
Dim formulaCell As Range
Set rng = ActiveSheet.UsedRange
'Check each colored cell in the range if it has a formula
For Each formulaCell In rng
If formulaCell.HasFormula Then
If formulaCell.Interior.ColorIndex = 24 Then
formulaCell.Formula = formulaCell.Value
End If
End If
Next formulaCell
ActiveSheet.Copy ' Copies active sheet to a new workbook
ActiveWorkbook.SaveAs Range("B2").Value
End Sub
答案1
得分: 1
导出工作表至新工作簿
- 在新工作簿中创建工作表的副本,并处理该副本,保持原始工作表不变。
Sub Convertan()
If ActiveSheet Is Nothing Then Exit Sub ' 没有可见工作簿打开
If Not TypeOf ActiveSheet Is Worksheet Then Exit Sub ' 不是工作表
ActiveSheet.Copy
Dim wb As Workbook: Set wb = Workbooks(Workbooks.Count)
Dim ws As Worksheet: Set ws = wb.Worksheets(1)
Dim urg As Range, cell As Range
For Each cell In ws.UsedRange.Cells
If cell.HasFormula Then
If cell.Interior.ColorIndex = 24 Then
If urg Is Nothing Then
Set urg = cell
Else
Set urg = Union(urg, cell)
End If
End If
End If
Next cell
If Not urg Is Nothing Then
Dim arg As Range
For Each arg In urg.Areas
arg.Value = arg.Value
Next arg
'urg.Interior.ColorIndex = xlNone ' 更改颜色
End If
Application.DisplayAlerts = False ' 无需确认覆盖
wb.SaveAs ws.Range("B2").Value
Application.DisplayAlerts = True
Dim FilePath As String: FilePath = wb.FullName
wb.Close SaveChanges:=False ' 刚刚保存
MsgBox "工作表已导出至 " & FilePath & ".", vbInformation
End Sub
英文:
Export a Worksheet to a New Workbook
- Create a copy of the worksheet in a new workbook and process the copy leaving the original intact.
<!-- language: lang-vb -->
Sub Convertan()
If ActiveSheet Is Nothing Then Exit Sub ' no visible workbooks open
If Not TypeOf ActiveSheet Is Worksheet Then Exit Sub ' not a worksheet
ActiveSheet.Copy
Dim wb As Workbook: Set wb = Workbooks(Workbooks.Count)
Dim ws As Worksheet: Set ws = wb.Worksheets(1)
Dim urg As Range, cell As Range
For Each cell In ws.UsedRange.Cells
If cell.HasFormula Then
If cell.Interior.ColorIndex = 24 Then
If urg Is Nothing Then
Set urg = cell
Else
Set urg = Union(urg, cell)
End If
End If
End If
Next cell
If Not urg Is Nothing Then
Dim arg As Range
For Each arg In urg.Areas
arg.Value = arg.Value
Next arg
'urg.Interior.ColorIndex = xlNone ' change the color
End If
Application.DisplayAlerts = False ' overwrite without confirmation
wb.SaveAs ws.Range("B2").Value
Application.DisplayAlerts = True
Dim FilePath As String: FilePath = wb.FullName
wb.Close SaveChanges:=False ' just got saved
MsgBox "Worksheet exported to """ & FilePath & """.", vbInformation
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论