合并两个宏 [英] Merging two macros
问题描述
我有一个包含原始数据的工作表,称为原始数据",我想将此数据复制到另一个名为数据"的工作表中.
I have a sheet with raw data called "Raw Data" I want to copy this data into another sheet called "Data".
在原始数据"中,我有一个名为"RawTab1"的命名范围在数据"中,我有一个名为"DataTable"的表,我要在其中粘贴"RawTab1"范围内的数据,但不粘贴"RawTab1"范围内的前两行
In "Raw Data" I have a Named Range called "RawTab1" In "Data" I have a Table called "DataTable" where I want to paste the data from range "RawTab1" but not the two first two rows from the range "RawTab1"
在粘贴RawTab1中的新数据之前,A已经使此宏清除了DataTable中的数据:
A have made this macro to cleare the data in the DataTable before pasting the new data from RawTab1:
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Data")
Range("A3:M3", sht.Range("A3:M3").End(xlDown)).ClearContents
End Sub
这是用于将RawTab1范围中的数据复制到DataTable中的宏
And this is the macro to copy the data from RawTab1 range into the DataTable
Sub CopyRawTab1()
Application.Goto Reference:="RawTab1"
Selection.Copy
Sheets("Data").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
所以我的问题是:
- 如何合并这两个宏?
- 如何避免不将RawTab1的前两行粘贴到DataTable中?
推荐答案
应该类似于以下内容:
Option Explicit
Public Sub Combined()
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Data")
sht.Range("A3:M3", sht.Range("A3:M3").End(xlDown)).ClearContents
With Worksheets("Raw Data").Range("RawTab1")
'copy everything from RawTab1 but not the first 2 rows
.Resize(RowSize:=.Rows.Count - 2).Offset(RowOffset:=2).Copy
End With
sht.Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
请注意,我使用了范围的组合.Resize属性和 Range.Offset属性,以在复制之前删除 RawTab1
范围的第一行.
Note that I used a combination of the Range.Resize property and the Range.Offset property to remove the first to rows of the RawTab1
range before copying it.
这篇关于合并两个宏的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!