而不是循环通过一个工作表“RVP Local GAAP”如何将其更改为两个工作表和两个范围？然后在两个工作表中粘贴值？ [英] Instead of looping through one worksheet "RVP Local GAAP" how do i change it to two worksheets and two ranges? and then pasting values in two worksheets?
问题描述
而不是循环遍历一个工作表"RVP Local GAAP"如何更改以下代码以循环显示两个工作表以及两个范围？不仅是"CurrentTaxPerLocalGAAP"而且但是在WS2中还有一个名为CurrentTaxPerGroupGAAP
的范围，然后还将值粘贴到不仅仅是RVP本地GAAP而且还粘贴到WS2。
下面有效，但是它检查一个范围并将其复制到一个工作表中。我想在不同的工作表中将范围更改为两个范围。
Sub Button4_Click（）
Dim strFileName As String
Dim wb1 As Workbook
Dim ws1 As Worksheet
Dim wb2 As Workbook
Dim ws2 As Worksheet
Dim cell As Range
Dim rng As Range
Dim rng2 As Range
Dim RangeName As String
Dim CellName作为字符串
Dim ValueToFind
Dim dstRng As Range
''设置wb2 = ActiveWorkbook
''设置ws2 = wb2.Sheet（"输出"）
''ws2.Range（" D1：D12"）。复制
''设置wb1 = ActiveWorkbook
strFileName = CreateObject（" WScript.Shell"）。specialfolders（" Desktop"）& " \ BAC GVP - Template_Update_121917.xlsm"
如果Dir（strFileName）<> vbNullString然后是
设置wb1 = Workbooks.Open（strFileName）
其他
MsgBox"抱歉，此时桌面上不存在该文件，请将副本放到桌面上来自服务器！"
结束如果
''设置wb2 = ThisWorkbook
''设置ws2 = wb2.Sheets（" Output"）
''设置ws1 = wb1.Sheets（" RVP Local GAAP"））
' 'ws2.Range（" D4：D12"）。复制
''ws1.Range（" G13：G21"）。PasteSpecial xlPasteValues
''RangeName =" myData"
''CellName =" G11：G83"
''设置单元格=工作表（"RVP本地GAAP"）。范围（CellName）
''ThisWorkbook.Names.Add Name：= RangeName，RefersTo：= cell
''RangeName =" NamedRange"
''CellName =" C4：C12" b
设置wb2 = ThisWorkbook
设置ws2 = wb2.Sheets（"输出"）
设置ws1 = wb1.Sheets（" RVP Local GAAP"）
设置rng =范围（"CurrentTaxPerLocalGAAPProvision" ;）
''设置rng2 =范围（"NamedRange"））
''设置rng2 = ValueToFind
''ValueToFind =（" ; NamedRange"）
'循环遍历NamedRange中的所有值
每个rng in ws2.Range（" NamedRange"）
设置dstRng =无结果
On Error Resume Next
设置dstRng = ws1.Range（rng.Value）
On Error GoTo 0
'检查目的地表中是否存在范围
如果没有dstRng则没有，那么就是
'检查相应区域是否存在范围
如果不相交（dstRng，ws1.Range（" CurrentTaxPerLocalGAAPProvision"））则Nothing Then
'将值从下一栏转移到适当的范围中。
'目标表
结束如果是
结束如果
下一个
结束次要
Instead of looping through one worksheet "RVP Local GAAP" how do I change the below code to loop through two worksheets along with two ranges? Not only "CurrentTaxPerLocalGAAP" but also a range called CurrentTaxPerGroupGAAP
in WS2 and then also pasting values into not only RVP Local GAAP but also WS2.
The below works but it checks one range and copies it into one worksheet. I want to change the range to two ranges in different worksheets.
Sub Button4_Click()
Dim strFileName As String
Dim wb1 As Workbook
Dim ws1 As Worksheet
Dim wb2 As Workbook
Dim ws2 As Worksheet
Dim cell As Range
Dim rng As Range
Dim rng2 As Range
Dim RangeName As String
Dim CellName As String
Dim ValueToFind
Dim dstRng As Range
''Set wb2 = ActiveWorkbook
''Set ws2 = wb2.Sheet("Output")
''ws2.Range("D1:D12").Copy
''Set wb1 = ActiveWorkbook
strFileName = CreateObject("WScript.Shell").specialfolders("Desktop") & "\BAC GVP - Template_Update_121917.xlsm"
If Dir(strFileName) <> vbNullString Then
Set wb1 = Workbooks.Open(strFileName)
Else
MsgBox "Sorry, the file does not exist on your Desktop at this time, please drop a copy to your Desktop from server!"
End If
''Set wb2 = ThisWorkbook
''Set ws2 = wb2.Sheets("Output")
''Set ws1 = wb1.Sheets("RVP Local GAAP")
''ws2.Range("D4:D12").Copy
''ws1.Range("G13:G21").PasteSpecial xlPasteValues
''RangeName = "myData"
''CellName = "G11:G83"
''Set cell = Worksheets("RVP Local GAAP").Range(CellName)
''ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell
''RangeName = "NamedRange"
''CellName = "C4:C12"
Set wb2 = ThisWorkbook
Set ws2 = wb2.Sheets("Output")
Set ws1 = wb1.Sheets("RVP Local GAAP")
Set rng = Range("CurrentTaxPerLocalGAAPProvision")
''Set rng2 = Range("NamedRange")
''Set rng2 = ValueToFind
''ValueToFind = ("NamedRange")
'Loop through all the values in NamedRange
For Each rng In ws2.Range("NamedRange")
Set dstRng = Nothing
On Error Resume Next
Set dstRng = ws1.Range(rng.Value)
On Error GoTo 0
'Check that the range exists in destination sheet
If Not dstRng Is Nothing Then
'Check that the range exists in the appropriate area
If Not Intersect(dstRng, ws1.Range("CurrentTaxPerLocalGAAPProvision")) Is Nothing Then
'Transfer the value from the next column to the appropriate range in the
'destination sheet
dstRng.Value = rng.Offset(0, 1).Value
End If
End If
Next
End Sub
推荐答案
您好
jane778，
您已经提到过，"而不是循环遍历一个工作表"RVP Local GAAP"如何将其更改为两个工作表和两个范围？然后粘贴两个工作表中的值"
You had mentioned that,"Instead of looping through one worksheet "RVP Local GAAP" how do i change it to two worksheets and two ranges? and then pasting values in two worksheets"
您可以尝试使用'Sheets（Sheet_Name）'然后引用该表中的范围来粘贴值。
You can try to use 'Sheets(Sheet_Name)' and then refer to range in that sheet to paste the values.
通过这种方式，您可以将值粘贴到多个表格中。
By this way you can paste the values to multiple Sheets.
您可以参考以下示例，可以帮助您理解。
You can refer example below may help you to understand.
以下代码将Sheet1列A与Sheet2列A匹配，如果值匹配，则它将粘贴Sheet3和Sheet4中的值。
Following code will match Sheet1 Column A with Sheet2 Column A and if the values get match then it will paste the values in Sheet3 and Sheet4.
Sub demo()
Dim lRow, x As Long
Sheets("Sheet1").Select
lRow = Range("A1").End(xlDown).Row
For Each cell In Range("A2:A" & lRow)
x = 2
Do
If cell.Value = Sheets("Sheet2").Cells(x, "A").Value Then
cell.EntireRow.Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
cell.EntireRow.Copy Sheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
x = x + 1
Loop Until IsEmpty(Sheets("Sheet2").Cells(x, "A"))
Next
End Sub
输出：
您可以尝试实施您的代码中的上述建议是为了满足您的要求。
You can try to implement the above suggestion in your code to fulfil your requirement.
如果您有任何其他问题，那么您可以尝试发布您的样本工作簿。
If you have any further questions then you can try to post your sample workbook.
我们将尝试使用您上面发布的代码对其进行测试，并尝试提供解决问题的建议。
We will try to make a test with it with your above posted code and try to provide a suggestions to solve the issue.
问候
Deepak
Deepak
这篇关于而不是循环通过一个工作表“RVP Local GAAP”如何将其更改为两个工作表和两个范围？然后在两个工作表中粘贴值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
