总共是两个表格(一个是“源数据”:数据的来源地 另一个就是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