将工作表复制到其他工作簿中，以替换当前数据 [英] Copy worksheet into different workbook replacing current data

问题描述

我希望有人能帮助我.

我有两个工作簿.Wb1 = FAC试用Wb2 =罗马人

I have two workbooks. Wb1 = FAC Trial Wb2 = ROMAN

两个工作簿中都有许多工作表.我想发生的事情是将Wb2工作表中名为Format的数据复制到Wb1工作表Format中.

There are numerous worksheets within both workbooks. What I would like to happen is that the data in Wb2, worksheet named Format is copied into Wb1 worksheet named Format.

Wb1中的工作表已被命名，并将每天从Wb2中进行更新.Wb1工作表格式的数据每次都会被覆盖.我一直在寻找，这将在单击命令按钮时发生.单击命令按钮时，Wb2不会打开.

The worksheet in Wb1 is already named and is to be updated on a daily basis from Wb2. The data in Wb1 worksheet Format would be overwritten each time. I was looking that this would happen at the click of a Command Button. Wb2 would not be open at the time of clicking the Command Button.

我找到了一些代码，但是每次运行代码时它都会创建一个新的工作表，我不确定如何更改它以满足我的需求.

I have found some code but it creates a new worksheet each time the code is run and I am unsure of how to change it to suit my needs.

在此先感谢您的帮助

推荐答案

只需在Developer功能区上使用记录宏"，您就可以轻松地记录一段代码来自己完成这项工作.

You can easily record a piece of code to do this yourself, simply use 'Record Macro' on the Developer ribbon.

现在，下面应该做的是您要做的(记住要更新工作簿2的文件路径).

For now, below should do what you're after (remember to update the filepath for workbook 2).

我刚刚从ROMAN工作簿中复制了格式"工作表，然后删除了现有的格式"工作表，并将新的工作表重命名为格式".

I have just copied the "Format" worksheet from the ROMAN workbook, then deleted the existing "Format" worksheet and re-named the new one to "Format".

Sub wsCopy()
    Dim wb1 As Workbook, wb2 As Workbook, ws1Format As Worksheet

    Application.ScreenUpdating = False
    Set wb1 = ThisWorkbook
    Set wb2 = Workbooks.Open("[ENTER FILE PATH]\ROMAN.xlsx")
    Set ws1Format = wb1.Sheets("Format")
    Set ws2Format = wb2.Sheets("Format")

    '' Copy the "Format" worksheet to wb1.
    ws2Format.Copy Before:=ws1Format
    wb2.Close

    '' Delete existing "Format" worksheet.
    Application.DisplayAlerts = False
    ws1Format.Delete
    Application.DisplayAlerts = True

    '' Rename new sheet to "Format".
    wb1.Sheets("Format (2)").Name = "Format"
    Application.ScreenUpdating = True
End Sub

这篇关于将工作表复制到其他工作簿中，以替换当前数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！