首页 文章

VBA擅长从工作表复制公式并粘贴到多个工作表

提问于
浏览
1

VBA擅长将工作表中的公式复制并粘贴到工作表数组中 . 例如,我想从工作表Data!C4:CX204中复制数据并粘贴到工作表Test1:Test50 . Test1:Test50的工作表将在彼此之后 . 我想我可以命名一个单元格Test1和另一个Test 50并链接到那些单元格以使阵列更灵活 .

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("C10") 

' Begin the loop. 
For I = 1 To WS_Count 
    ThisWorkbook.worksheets(i).Select ' just select the sheet 
    Source.Copy Range("C11:C300").Select 
    ActiveSheet.Paste 
Next I 

End Sub

2 回答

  • 0

    根据您发布的代码,您可能需要以下内容:

    Sub Button4_Click()
    Dim src As Range, sel As Range
    Dim i as long
    
    Set sel = Selection
    Set src = ActiveWorkbook.Sheets("Data").Range("C10")
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    For i = 1 to 50
        src.Copy ActiveWorkbook.Sheets("Test" & i).Range("C11:C300")
    Next i
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    
    Application.Goto sel, False
    
    End Sub
    
  • 1

    这是你想要的? i = 1到工作表计数可以更改为您需要的 . 复制/粘贴的第二部分是目的地 .

    Sub Button4_Click()
    Dim I As Integer
    
    ' Begin the loop.
    For I = 1 To ActiveWorkbook.Worksheets.Count - 1
    Worksheets(1).Range("C11:C300").Copy Worksheets("Test" & I).Range("C11:C300")
    Next I
    
    End Sub
    

相关问题