将一系列数据复制到工作簿的所有工作表中 [英] Copy a range of data to all sheets of the workbook

问题描述

我有一个包含数据库的工作簿.

I have a workbook that contains a database .

在该数据库上，有几行数据我想复制并粘贴到所有工作表中.

On that database there is a certain row of data that i would like to copy and paste to all sheets.

复制范围随着数据库中行数据的更改而变化，但是每个位置的粘贴范围保持不变.

The copying range varies as the row data on the database changes, but the paste range for each location remains the same.

到目前为止，我已经完成了一个代码，但是它只能逐页复制粘贴内容，而我无法在代码中定义固定范围.

I have a code that i have done so far but it only allows to copy paste sheet by sheet and i could not define a fixed range in the code.

在这种情况下，我希望将所选数据粘贴到每张纸的B1:N1.

In this case i would like the selected data to be pasted to B1:N1 of each sheet.

需要一些帮助才能将一个目标粘贴到所有工作表上.

Some help would be needed to paste at one goal to all sheets.

这是我的代码:

Dim rng As Range, inp As Range
Set rng = Nothing
Set inp = Selection
inp.Interior.ColorIndex = 37
On Error Resume Next
Set rng = Application.InputBox("Copy to", Type:=8)
On Error GoTo 0
If TypeName(rng) <> "Range" Then
    MsgBox "Cancelled", vbInformation
    Exit Sub
Else
    rng.Parent.Activate
    rng.Select
    inp.Copy
    Worksheets("Sheet2").Paste Link:=True
End If

Application.CutCopyMode = 0

推荐答案

所有工作表都需要循环吗?

do you need a Loop for all worksheets ?

    Dim ws as Worksheet
    For Each ws in ActiveWorkbook.Worksheets
        If Not ws.Name = "*Name of the database workbook *" Then    
            Call ws.Range("B1:N1").PasteSpecial(xlPasteAll, xlPasteSpecialOperationNone)
        End If
    Next

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