gpt4 book ai didi

vba - 根据一个单元格值并引用​​另一单元格值复制行并粘贴到新工作表上

转载 作者:行者123 更新时间:2023-12-02 07:38:29 25 4
gpt4 key购买 nike

我必须创建一个报告,在其中获取包含交易列表的原始数据,我需要我的宏根据 C 列中的投资组合名称将每笔交易发送到其各自的工作表

我设法做到了这一点,但现在我需要将下面给出的引用表中属于“现金”的诺基亚交易粘贴到“诺基亚 - 现金”表下

Raw Data Workbook UPDATED

Reference Sheet

有人可以帮我构建代码的第二部分,这有助于将“如果 C=诺基亚且 J = 半付费”转移到“诺基亚 - 现金”吗?

最佳答案

这与我回答的上一个问题类似。

您不必担心创建工作表并命名它们,代码会处理它。它还会跳过引用表中未找到的项目。

它将描述项目引用表中的项目相匹配,然后将卡片名称匹配项目的类别名称 以便命名相关工作表。如果此工作表不存在,它将创建并传递行数据,否则只需传递行数据。

Sub MyClients()
Dim lastrow As Long, lastcol As Long, matchrow As Long, i As Long, j As Long
Dim wsname As String
lastrow = Worksheets("Raw").Cells(Worksheets("Raw").Rows.Count, 1).End(xlUp).Row
lastcol = Worksheets("Raw").Cells(1, Worksheets("Raw").Columns.Count).End(xlToLeft).Column

Application.ScreenUpdating = False
For i = 2 To lastrow
On Error Resume Next
matchrow = Application.WorksheetFunction.Match(Worksheets("Raw").Cells(i, 10).Value, Worksheets("Reference").Range("A:A"), 0)
If Err.Number = 1004 Then
MsgBox "Couldn't find item: '" & Worksheets("Raw").Cells(i, 10).Value & "' within reference sheet. Skipping row no: " & i
GoTo skip:
End If
wsname = Worksheets("Raw").Cells(i, 3).Value & " - " & Worksheets("Reference").Cells(matchrow, 2).Value
On Error Resume Next
Worksheets(wsname).Cells(Worksheets(wsname).Rows.Count, 1).End(xlUp).Offset(1, 0) = Worksheets("Raw").Cells(i, 1).Value
For j = 1 To lastcol - 1
Worksheets(wsname).Cells(Worksheets(wsname).Rows.Count, 1).End(xlUp).Offset(0, j) = Worksheets("Raw").Cells(i, j).Value
Next j
If Err.Number = 9 Then
Sheets.Add(After:=Sheets(Sheets.Count)).Name = wsname
For j = 1 To lastcol
Worksheets(wsname).Cells(1, j) = Worksheets("Raw").Cells(1, j).Value
Next j
Worksheets(wsname).Cells(Worksheets(wsname).Rows.Count, 1).End(xlUp).Offset(1, 0) = Worksheets("Raw").Cells(i, 1).Value
For j = 1 To lastcol - 1
Worksheets(wsname).Cells(Worksheets(wsname).Rows.Count, 1).End(xlUp).Offset(0, j) = Worksheets("Raw").Cells(i, j).Value
Next j
End If
skip:
Next i
Worksheets("Raw").Activate
Application.ScreenUpdating = True
End Sub

关于vba - 根据一个单元格值并引用​​另一单元格值复制行并粘贴到新工作表上,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/43991458/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com