Hi All,
i'm very new to VBA, i need some help here and this is my code
my question is, i enter a date in A2 at sheet3 and run the first macro, the first result is prefect and exactly what i want
however, i get A1 filled as well when i run the second macro with a new date at the second times
what's wrong with my code and how could i fix?
thank you very much
i'm very new to VBA, i need some help here and this is my code
my question is, i enter a date in A2 at sheet3 and run the first macro, the first result is prefect and exactly what i want
however, i get A1 filled as well when i run the second macro with a new date at the second times
what's wrong with my code and how could i fix?
VBA Code:
Sub Macro1()
'
' NORMAL STORE
'
'
'filter store and location
Sheets("Sheet1").Select
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$V$1").AutoFilter Field:=2, Criteria1:="NORMAL"
ActiveSheet.Range("$A$1:$V$1").AutoFilter Field:=9, Criteria1:="<>"
Cells.Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("A1").Select
Selection.AutoFilter
'copy order id
Sheets("Sheet2").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Sheet3").Select
Range("F2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'fill date, code, company name
Sheets("Sheet3").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]+2"
Selection.AutoFill Destination:=Range("B2:B" & Range("F" & Rows.Count).End(xlUp).Row)
Range("D2").Select
ActiveCell.FormulaR1C1 = "1234567"
Selection.AutoFill Destination:=Range("D2:D" & Range("F" & Rows.Count).End(xlUp).Row)
Range("E2").Select
ActiveCell.FormulaR1C1 = "ABC"
Selection.AutoFill Destination:=Range("E2:E" & Range("F" & Rows.Count).End(xlUp).Row)
Range("G2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(Sheet2!RC[12],"" "",Sheet2!RC[11])"
Selection.AutoFill Destination:=Range("G2:G" & Range("F" & Rows.Count).End(xlUp).Row)
'fill address
Sheets("Sheet2").Select
Range("U2:V1000").Select
Selection.Copy
Sheets("Sheet3").Select
Range("H2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'fill phone
Sheets("Sheet2").Select
Range("T2:T1000").Select
Selection.Copy
Sheets("Sheet3").Select
Range("L2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet2").Select
Range("H2:H1000").Select
Selection.Copy
Sheets("Sheet3").Select
Range("M2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'fill box, quantity, weight
Sheets("Sheet3").Select
Range("Q2").Select
ActiveCell.FormulaR1C1 = "box"
Selection.AutoFill Destination:=Range("Q2:Q" & Range("F" & Rows.Count).End(xlUp).Row)
Range("R2").Select
ActiveCell.FormulaR1C1 = "1"
Selection.AutoFill Destination:=Range("R2:R" & Range("F" & Rows.Count).End(xlUp).Row)
Range("S2").Select
ActiveCell.FormulaR1C1 = "3"
Selection.AutoFill Destination:=Range("S2:S" & Range("F" & Rows.Count).End(xlUp).Row)
Range("S3").Select
'fill date
Sheets("Sheet3").Select
Dim Lr As Long
Lr = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & Lr, Range("B" & Rows.Count).End(xlUp).Offset(, -1)).FillDown
'value
Sheets("Sheet3").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
'clear data in sheet2 if any
Sheets("Sheet2").Select
Cells.Select
Selection.Delete Shift:=xlUp
With ActiveWindow
.SplitColumn = 0
.SplitRow = 0
End With
Range("A1").Select
Sheets("Sheet1").Select
Selection.AutoFilter
Range("A1").Select
'delete object and move
Sheets("Sheet3").Select
ActiveSheet.DrawingObjects.Select
Selection.Delete
ActiveSheet.Copy
Sheets("Sheet3").Name = "normal store"
'clear data in sheet3 if any
Windows("store.xlsm").Activate
Sheets("Sheet3").Select
Rows("2:1000").Select
Selection.Delete
Range("A2").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.499984740745262
.PatternTintAndShade = 0
End With
End Sub
Book1 | ||||
---|---|---|---|---|
A | B | |||
1 | Ship out date | Consignee's_Request Date | ||
2 | 01 April, 2022 | 03 April, 2022 | ||
3 | 01 April, 2022 | 03 April, 2022 | ||
normal store |
Book1 | ||||
---|---|---|---|---|
A | B | |||
1 | 20 April, 2022 | Consignee's_Request Date | ||
2 | 20 April, 2022 | 22 April, 2022 | ||
3 | 20 April, 2022 | 22 April, 2022 | ||
limited store |
thank you very much