VBA excel从工作表中复制公式并将其粘贴到多个工作表中 [英] VBA excel to copy formula from a worksheet and paste to multiple worksheets

问题描述

VBA擅长从工作表复制公式并将其粘贴到工作表数组中.例如，我要从工作表Data！C4:CX204复制数据并将其粘贴到工作表Test1:Test50.来自Test1:Test50的工作表将紧接彼此.我以为可以命名一个单元格Test1和另一个单元格Test 50，并链接到这些单元格，以使阵列更灵活.

VBA excel to copy formula from a sheet and paste to an array of worksheets. As an example, I want to copy data from worksheet Data!C4:CX204 and paste to worksheets Test1:Test50. The Worksheets from Test1:Test50 will be right after each other. I was thinking I can name one cell Test1 and another Test 50 and link to those cells in order to make the array more flexible.

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

推荐答案

根据您发布的代码，您可能想要这样的东西:

Based on the code you posted, you probably want something like this:

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

这篇关于VBA excel从工作表中复制公式并将其粘贴到多个工作表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！