我正在编写的宏有一个子集,需要一些修改&所有天才的帮助。
工作表"Regions!A6:R6包含从表中提取数据的公式。我希望将"Regions!A6:R6“复制为值,然后粘贴到”Temp!A1“中。”
您是否能够帮助修改此代码以实现此最终目标?
Sub Testing()
' Gets Number of Row in Regions Sheet
Sheets("Regions").Activate
LR1 = Cells(Rows.Count, "A").End(xlUp).Row
'Creates a New Worksheet and copy's the data from regions, interst the copied data into the
'New Temp Sheet and then removes the duplicates to create your list of unique items to filter on
'Creating the New WorkSheet
Sheets.Add.Name = "Temp"
'Copy the data
Sheets("Regions").Activate
Range("A6:R" & LR1).Select
Selection.Copy
'Paste the data into Temp Sheet
Sheets("Temp").Activate
Range("A1").Select
ActiveSheet.Paste
发布于 2017-03-07 01:36:58
我不是一个天才,但我是一个总是尝试应用好的编码实践的人。首先,我会去掉Select
和Activate
之类的东西,简化代码,然后赋值为Value
,而不是使用copy/paste,这会让你摆脱公式,只复制值。
Sub Testing()
Sheets.Add.Name = "Temp"
With Sheets("Regions").Range("A6:R" & Sheets("Regions").Cells(Rows.Count, "A").End(xlUp).Row)
Sheets("Temp").Range("A1").Resize(.Rows.Count, .Columns.Count).value = .value
End With
End Sub
发布于 2017-03-07 01:38:41
使用这个
Sub Testing()
'Creating the New WorkSheet
Sheets.Add.Name = "Temp"
'Copy the data
With Sheets("Regions")
With .Range("R6", .cells(.Rows.Count, "A").End(xlUp))
Sheets("Temp").Range("A1").Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
End With
End Sub
https://stackoverflow.com/questions/42631861
复制相似问题