tigerpathon
虎锅
采纳率0%
2021-03-24 18:51

VBA宏,求救贴(行会变,列固定表头)

总共是两个表格(一个是“源数据”:数据的来源地     另一个就是6个逻辑文件:需要将“源数据的”对应内容放进6个模块中)
总共是13项步骤,小弟录制过宏,但是碰到其他源数据的列不一致就不行了。(其中碰到的最严峻的问题是小弟不会写数据对应的行数怎么把常量变成变量,让实际情况的数据自动填充)所以来请求大佬们做做趣味题~救命!
1.Y列(库存)筛选带有字符“FBA库存”的数据并且排除"FBA库存:0"的数据

2.这一部分数据连表头一起A-Y列复制进“日本6个逻辑”内的“FBA-5”里面,并且下拉Z:AB三列的公式

3.针对下拉Z列出错的数据,直接等于O列“your price”的数据。Z列 AA列 AB列 三列等于O列“your price”

4.回到“源数据”,将第一步里面的这些数据删除(保留表头)

5.在Z2的地方输入公式“=VLOOKUP(E2,'[日本6个逻辑.xlsx]FBA-5'!$E:$E,1,FALSE)”并下拉至有数据的最后一行

6.筛选Z列带有“B0”的数据,并对这部分数据A-Y列复制进“日本6个逻辑”内的“FBM-6”里面,并且下拉Z:AB三列的公式

7.回到“源数据”,将第六步里面的这些数据删除(保留表头)

8.筛选D列“货源状态”不带有“正常”部分的数据,并对这部分数据A-Y列复制进“日本6个逻辑”内的“断货-4”里面,并且下拉Z:AB三列的公式

9.回到“源数据”,将第八步里面的这些数据删除(保留表头)

10.筛选U列“7天销量”大于0的数据,并对这部分数据A-Y列复制进“日本6个逻辑”内的“出单提价+持平3”里面,并且下拉Z:AB三列的公式

11.回到“源数据”,将第十步里面的这些数据删除(保留表头)

12.筛选R列“利润率”不包含“-”的数据,并对这部分数据A-Y列复制进“日本6个逻辑”内的“正常不出单2”里面,并且下拉Z:AB三列的公式

13.回到“源数据”,筛选R列“利润率”包含“-”的数据,并对这部分数据A-Y列复制进“日本6个逻辑”内的“无利润率1”里面,并且下拉Z:AB三列的公式


以下附上我手动录制宏的呆瓜操作。主要是不能把读取行和复制粘贴行的那个数据写出来。自动录制的都是实际的行数,不能变化
 

Sub 日本完整宏()
'
' 日本完整宏 Macro
' 宏由 Administrator 录制,时间: 2021/03/23
'

    Selection.AutoFilter
    ActiveWorkbook.Names.Add Name:="new!_FilterDatabase", RefersTo:="=new!$A$1:$Y$127546", Visible:=False
    Range("A1:Y127546").AutoFilter Field:=25, Criteria1:="=*fba*", Operator:=xlAnd
    ActiveWorkbook.Names.Add Name:="new!_FilterDatabase", RefersTo:="=new!$A$1:$Y$127546", Visible:=False
    Range("A1:Y62489").Select
    ActiveWindow.ScrollRow = 62423
    Selection.Copy
    Range("A1:A62435").Select
    Selection.Activate
    ActiveWindow.ScrollRow = 1
    Range("A1:Y62489").Select
    ActiveWindow.ScrollRow = 62423
    Columns("A:Y").Select
    ActiveWindow.ScrollRow = 1048510
    Selection.Copy
    Windows("listing数据模板(6LJ)-日本.xlsx").Activate
    Sheets("FBA-5").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Windows("new.csv").Activate
    ActiveWindow.ScrollRow = 1
    Rows("62256:62256").Select
    ActiveWindow.ScrollColumn = 16348
    Rows("62256:62489").Select
    ActiveWindow.ScrollRow = 62423
    ActiveWindow.ScrollColumn = 1
    Selection.Delete Shift:=xlShiftUp
    ActiveWindow.ScrollRow = 1
    Selection.AutoFilter
    Selection.AutoFilter
    ActiveWorkbook.Names.Add Name:="new!_FilterDatabase", RefersTo:="=new!$A$62256:$AF$62489", Visible:=False
    Range("X1").Select
    Selection.AutoFilter
    Selection.AutoFilter
    ActiveWorkbook.Names.Add Name:="new!_FilterDatabase", RefersTo:="=new!$A$1:$Y$127312", Visible:=False
    Range("Z2").Select
    Windows("listing数据模板(6LJ)-日本.xlsx").Activate
    Windows("new.csv").Activate
    ActiveSheet.Select
    ActiveWindow.SelectedSheets.Select Replace:=True
    Selection = "=VLOOKUP(E2,'[listing数据模板(6LJ)-日本.xlsx]FBA-5'!$E:$E,1,FALSE)"
    Range("Z2").Select
    Selection.AutoFill Destination:=Range("Z2:Z127312"), Type:=xlFillDefault
    Range("Z2:Z127312").Select
    ActiveWindow.ScrollRow = 1
    Range("Z1").Select
    Selection.AutoFilter
    Selection.AutoFilter
    ActiveWorkbook.Names.Add Name:="new!_FilterDatabase", RefersTo:="=new!$A$1:$Z$127312", Visible:=False
    Range("A1:Z127312").AutoFilter Field:=26, Criteria1:="=*B0*", Operator:=xlAnd
    ActiveWorkbook.Names.Add Name:="new!_FilterDatabase", RefersTo:="=new!$A$1:$Z$127312", Visible:=False
    Range("A1:Y78893").Select
    ActiveWindow.ScrollRow = 64949
    Selection.Copy
    Range("B1:B70932").Select
    Selection.Activate
    ActiveWindow.ScrollRow = 1
    Range("A1:Y78893").Select
    ActiveWindow.ScrollRow = 64949
    Columns("A:Y").Select
    ActiveWindow.ScrollRow = 1048510
    Selection.Copy
    Windows("listing数据模板(6LJ)-日本.xlsx").Activate
    Sheets("FBM-6").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Windows("new.csv").Activate
    Range("Y1048523").Select
    ActiveWindow.ScrollRow = 1
    Rows("16641:16641").Select
    ActiveWindow.ScrollColumn = 16348
    Rows("16641:78893").Select
    ActiveWindow.ScrollRow = 64949
    ActiveWindow.ScrollColumn = 1
    Selection.Delete Shift:=xlShiftUp
    ActiveWindow.ScrollRow = 1
    Range("A1:Z127003").AutoFilter Field:=26, Criteria1:="=*asin*", Operator:=xlAnd
    ActiveWorkbook.Names.Add Name:="new!_FilterDatabase", RefersTo:="=new!$A$1:$Z$127003", Visible:=False
    Rows("20751:20751").Select
    ActiveWindow.ScrollColumn = 16348
    Rows("20751:126480").Select
    ActiveWindow.ScrollColumn = 1
    Selection.Delete Shift:=xlShiftUp
    Range("A1:Z126991").AutoFilter Field:=26
    ActiveWorkbook.Names.Add Name:="new!_FilterDatabase", RefersTo:="=new!$A$1:$Z$126991", Visible:=False
    Range("A1:Z126991").AutoFilter Field:=4, Criteria1:="<>*正常*", Operator:=xlAnd
    ActiveWorkbook.Names.Add Name:="new!_FilterDatabase", RefersTo:="=new!$A$1:$Z$126991", Visible:=False
    Range("A1:Y126984").Select
    ActiveWindow.ScrollRow = 123950
    Columns("A:Y").Select
    ActiveWindow.ScrollRow = 1048510
    Selection.Copy
    Windows("listing数据模板(6LJ)-日本.xlsx").Activate
    Sheets("断货4").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Windows("new.csv").Activate
    ActiveWindow.ScrollRow = 1
    Rows("62:62").Select
    ActiveWindow.ScrollColumn = 16348
    Rows("62:126984").Select
    ActiveWindow.ScrollRow = 123950
    ActiveWindow.ScrollColumn = 1
    Selection.Delete Shift:=xlShiftUp
    ActiveWindow.ScrollRow = 1
    Range("A1:Z125657").AutoFilter Field:=4
    ActiveWorkbook.Names.Add Name:="new!_FilterDatabase", RefersTo:="=new!$A$1:$Z$125657", Visible:=False
    Range("A1:Z125657").AutoFilter Field:=21, Criteria1:=">0", Operator:=xlAnd
    ActiveWorkbook.Names.Add Name:="new!_FilterDatabase", RefersTo:="=new!$A$1:$Z$125657", Visible:=False
    Range("A1:Y121330").Select
    ActiveWindow.ScrollRow = 62007
    Selection.Copy
    Windows("listing数据模板(6LJ)-日本.xlsx").Activate
    Sheets("出单提价+持平3").Activate
    Range("A1").Select
    Windows("new.csv").Activate
    ActiveWindow.ScrollRow = 61716
    Range("I1:I63333").Select
    Selection.Activate
    ActiveWindow.ScrollRow = 1
    Range("A1:Y121330").Select
    ActiveWindow.ScrollRow = 62007
    Columns("A:Y").Select
    ActiveWindow.ScrollRow = 1048510
    Selection.Copy
    Windows("listing数据模板(6LJ)-日本.xlsx").Activate
    ActiveSheet.Paste
    Windows("new.csv").Activate
    ActiveWindow.ScrollRow = 1
    Range("A1:Z125657").AutoFilter Field:=21
    ActiveWorkbook.Names.Add Name:="new!_FilterDatabase", RefersTo:="=new!$A$1:$Z$125657", Visible:=False
    Range("A1:Z125657").AutoFilter Field:=21, Criteria1:="=", Operator:=xlAnd
    ActiveWorkbook.Names.Add Name:="new!_FilterDatabase", RefersTo:="=new!$A$1:$Z$125657", Visible:=False
    Range("A1:Z125657").AutoFilter Field:=18, Criteria1:="<>*-*", Operator:=xlAnd
    ActiveWorkbook.Names.Add Name:="new!_FilterDatabase", RefersTo:="=new!$A$1:$Z$125657", Visible:=False
    Range("A1:Y125657").Select
    ActiveWindow.ScrollRow = 125591
    Columns("A:Y").Select
    ActiveWindow.ScrollRow = 1048510
    Selection.Copy
    Windows("listing数据模板(6LJ)-日本.xlsx").Activate
    Sheets("正常不出单2").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("无利润率1").Activate
    Windows("new.csv").Activate
    Range("Y1048532").Select
    ActiveWindow.ScrollRow = 1048480
    Range("Q125657:Q1048507").Select
    Selection.Activate
    ActiveWindow.ScrollRow = 125636
    Range("V117677:V125652").Select
    Selection.Activate
    ActiveWindow.ScrollRow = 1
    Range("V1").Select
    Range("A1:Z125657").AutoFilter Field:=18, Criteria1:="=*-*", Operator:=xlAnd
    ActiveWorkbook.Names.Add Name:="new!_FilterDatabase", RefersTo:="=new!$A$1:$Z$125657", Visible:=False
    Range("A1:Y125503").Select
    ActiveWindow.ScrollRow = 123215
    Columns("A:Y").Select
    ActiveWindow.ScrollRow = 1048510
    Selection.Copy
    Windows("listing数据模板(6LJ)-日本.xlsx").Activate
    Range("A1").Select
    ActiveSheet.Paste
End Sub
 

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • tigerpathon 虎锅 2月前
    标题
    标题

     

    点赞 评论 复制链接分享

相关推荐