excel - VBA复制范围到所有工作表都优于复制的一个

Question

Sub Button4_Click()

    Dim WS_Count As Integer
    Dim I As Integer
    WS_Count = ActiveWorkbook.Worksheets.Count

    Dim Source As Range

    Set Source = ThisWorkbook.Worksheets(1).Range("H4:AD600")

    ' Begin the loop.
    For I = 1 To WS_Count

        ThisWorkbook.worksheets(i).Select ' just select the sheet
        Source.Copy    
        Range("H4:AD600").Select
        ActiveSheet.Paste

    Next I

    End Sub

尝试将一系列单元格从 Sheet1 复制到工作簿中的所有其他工作表，但还需要确保它也复制公式。

标签： excelvbacopy

Answer 1

将范围从第一个复制到所有其他工作表

限制：使用前必须阅读

要从中复制的工作表必须是第一个，即选项卡中最左边的（第一个）工作表。如果您不小心或故意将其移动到另一个位置，则可能会对您的工作簿造成严重损坏。

工作簿必须只包含工作表，例如没有图表，否则代码将失败。

编码

Option Explicit

Sub Button4_Click()

    Const RangeAddress As String = "H4:AD600"  ' Source Range Address
    Dim SourceRange As Range                   ' Source Range
    Dim i As Long                              ' Worksheets Counter

    With ThisWorkbook
        ' Define and copy Source Range in First Worksheet to clipboard.
        Set SourceRange = .Worksheets(1).Range(RangeAddress)
        SourceRange.Copy
        ' Paste Source Range into the remaining worksheets.
        For i = 2 To .Worksheets.Count
            .Worksheets(i).Range(RangeAddress).PasteSpecial xlPasteFormulas
        Next i
        ' Select range 'A1' in all worksheets and activate first worksheet.
        For i = .Worksheets.Count To 1 Step -1
            .Worksheets(i).Activate
            .Worksheets(i).Range("A1").Select
        Next i
    End With

    ' Remove Source range from clipboard.
    Application.CutCopyMode = False
    ' Inform user that the operation has finished.
    MsgBox "Copied Range(" & RangeAddress & ") from the first to " _
      & "the remaining worksheets.", vbInformation, "Copy Range"

End Sub

---

研究

Sub Button4_ClickStudy()

    Const RangeAddress As String = "H4:AD600"

    Dim SourceRange As Range
    Dim i As Long

    ' In ThisWorkbook (the workbook containing this code).
    With ThisWorkbook

        ' Create a reference to the range specified by RangeAddress
        ' on the first worksheet (the left-most worksheet on the sheet tabs).
        Set SourceRange = .Worksheets(1).Range(RangeAddress)

        ' The following For Next block is only useful if you want to remove
        ' the previous formatting, since the values of the cells are going
        ' to be overwritten anyway.

        ' Loop through the rest of the worksheets.
        For i = 2 To .Worksheets.Count
             ' Clear the range specified by RangeAddress on the current
             ' worksheet. Clear is different than ClearContents (DEL).
             .Worksheets(i).Range(RangeAddress).Clear
Debug.Print "The range '" & RangeAddress & "' in worksheet '" & i _
  & "' (" & .Worksheets(i).Name & ") has just been cleared."
        Next i

        ' Copy SourceRange i.e. the range is copied to the clipboard (memory).
        ' Excel is displaying an animated moving border around the range.
        ' This is the same as if you would have selected the range and
        ' pressed CTRL+C in Excel.
        SourceRange.Copy

        ' Loop through the rest of the worksheets.
        For i = 2 To .Worksheets.Count
            ' In range of current worksheet
            With .Worksheets(i).Range(RangeAddress)
                '.PasteSpecial ' will paste (almost) everything, incl. formulas.
                '.PasteSpecial xlPasteFormuats
                .PasteSpecial xlPasteFormulas
                '.PasteSpecial xlPasteValues
                ' Column widths and comments are only pasted in this way:
                '.PasteSpecial xlPasteColumnWidths
                '.PasteSpecial xlPasteComments
Debug.Print "Sheet '" & i & "' (" & .Parent.Name _
  & ") has just been written (pasted) to."
            End With
        Next i

' The following will just show the name of the ActivSheet.
' In this case it will show that the first sheet is active.
Debug.Print "The ActiveSheet's name is '" & ActiveSheet.Name & "'."

        ' Loop through the rest of the worksheets from the last to the first
        ' because you would want to see the first sheet after the operation.
        For i = .Worksheets.Count To 1 Step -1 ' Remember to use negative Step!
            ' In current worksheet
            With .Worksheets(i)
                ' To select a range on a worksheet, you have to first
                ' activate the worksheet. There will be no error if it
                ' is already active (like in this case).
                .Activate
                .Range("A1").Select 'or Cells(1, 1) or Range("A1")
Debug.Print "Range 'A1' has just been selected on Sheet '" & i _
  & "' (" & .Name & ")."
            End With
        Next i

    End With

    ' Remove the range from the clipboard (memory).
    ' Removes the animated moving border around the range.
    ' This is the same as if you would have pressed ESC in Excel (after
    ' you have cut or copied a range).
    Application.CutCopyMode = False
Debug.Print "Range '" & RangeAddress & "' in sheet '1' (" _
  & ThisWorkbook.Worksheets(1).Name _
  & ")' has just been removed from the clipboard."

    ' When the code is a fast operation, you might not have noticed
    ' that anything had happened. So it is a good idea to inform yourself.
    MsgBox "Copied Range(" & RangeAddress & ") from the first to " _
      & "the remaining worksheets.", vbInformation, "Copy Range"

End Sub