使用VBA从主工作簿创建副本 [英] Creating Copies from a Master Workbook using VBA

问题描述

我有两个文件，一个是我要在其中运行宏的文件，另一个是外部文件.

I have two files, one the file in which I want to run the macro in and another external file.

在运行宏的文件(以下称为主"文件)中，看起来像这样:

Within the file that the macro is running in (henceforth the "master" file), there is something that looks like this:

到目前为止，我的代码是:

The code that I have so far is this:

Sub test()
For i = 1 To 3
    If Not Range("B" & i).Value = "X" Then

        Range("C2").Value = Range("A" & i).Value
        Calculate 'updates the formula
        Range("B" & i).Value = "X" 'update the check

        Range("D2").Copy 'this is the tricky part - this is what is needed. The formula links needs to be broken so that only the values remain
        Range("D2").PasteSpecial xlPasteValues

        ActiveWorkbook.SaveCopyAs "C:\Users\n0269777\Desktop\" & Range("A" & i).Value & ".xlsm" 'the problem with SaveCopyAs is that the formula originally is now overwritten.
        'thus I need some way to refer back to the 'master' workbook, the one where the formula has not yet been overwritten

    End If
Next i
End Sub

我要实现的是宏将循环遍历并检查是否已使用A列中的名称创建了工作簿.然后，它将更新"C2"中的值.最后，保存副本-公式将覆盖其值，而不是保留公式.这是一个困难，因为我不能简单地保存工作簿的副本-宏运行后，该公式将被覆盖.

What I want to achieve is that the macro will loop through and check to see if a workbook has been created with the names in column A. Then, it will update the value in "C2". Finally, a copy is saved -- and the formula is overwriten to its value, rather than remain a formula. This is the difficulty in that I cannot simply save a copy of the workbook -- the formula would have been overwritten after the run of the macro.

这是在运行宏后在Type3.xlsm中发生的情况.如您所见，"D2"中的值为1，而应为3.

This is what happens in Type3.xlsm after running the macro. As you can see, the value in "D2" is 1, whereas it should be 3.

我也考虑过这种方法:

Sub test2()
For i = 1 To 3
    If Not Range("B" & i).Value = "X" Then

        Range("C2").Value = Range("A" & i).Value
        Calculate 'updates the formula
        Range("B" & i).Value = "X" 'update the check

        Set wboor = ActiveWorkbook
        fileaddress = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

        Range("D2").Copy 'this is the tricky part - this is what is needed. The formula links needs to be broken so that only the values remain
        Range("D2").PasteSpecial xlPasteValues

        wboor.SaveCopyAs "C:\Users\n0269777\Desktop\" & Range("A" & i).Value & ".xlsm" 'Perhaps I can save a copy first? Then close the workbook, so the formula is preserved
        wboor.Close
        Workbooks.Open Filename:=fileaddress 'but then, how do I call the original file, and then loop the macro to run again?

    End If
Next i
End Sub

任何建议/帮助将不胜感激！

Any suggestions/help would be appreciated!

推荐答案

不确定是否有必要提出新的问题，但是无论如何都可以尝试.

Not sure it was necessary to start a new question, but anyway try this.

Sub test()

Dim wb As Workbook, s As String, i As Long

For i = 1 To 3
    If Not Range("B" & i).Value = "X" Then
        Range("C2").Value = Range("A" & i).Value
        Calculate 'updates the formula
        Range("B" & i).Value = "X" 'update the check
        s = "C:\Users\n0269777\Desktop\" & Range("A" & i).Value & ".xlsm"
        ActiveWorkbook.SaveCopyAs s
        Set wb = Workbooks.Open(s)
        wb.Sheets(1).UsedRange.Value = wb.Sheets(1).UsedRange.Value
        wb.Close True
    End If
Next i

End Sub

这篇关于使用VBA从主工作簿创建副本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！