Gusia
04-08-2016, 06:55 AM
Hello
can anybody help me I need to change formula that is with bold font so that 'Shipping Data'!A2:A350 change fo next cell 'Shipping Data'!A3:A351.
Also I need to do loop for each time that cells in formula will be changing all that is in green colour need to be repeat.
Sub Macro2()
' Macro2 Macro
Sheets("Print Sheet").Select
Dim FirstBlankCell As Range
Set FirstBlankCell = Range("b" & Rows.Count).End(xlUp).Offset(1, -1)
FirstBlankCell.Activate
Dim Label As Range
Set Label = Range("A1:C15")
Label.Select
Selection.Copy
FirstBlankCell.Select
ActiveSheet.Paste
Set FirstBlankCell = Range("b" & Rows.Count).End(xlUp).Offset(3, 0)
FirstBlankCell.Activate
Selection.Offset(-15, 1).Select
Selection.Value = "=INDEX('Shipping Data'!A2:A350,MATCH(TRUE,INDEX(('Shipping Data'!A2:A350<>0),0),0))"
Range("A16:C100000").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Shipping Data").Select
Dim EmptyCell As Range
Set EmptyCell = Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
EmptyCell.Activate
End Sub
Regards Agata
can anybody help me I need to change formula that is with bold font so that 'Shipping Data'!A2:A350 change fo next cell 'Shipping Data'!A3:A351.
Also I need to do loop for each time that cells in formula will be changing all that is in green colour need to be repeat.
Sub Macro2()
' Macro2 Macro
Sheets("Print Sheet").Select
Dim FirstBlankCell As Range
Set FirstBlankCell = Range("b" & Rows.Count).End(xlUp).Offset(1, -1)
FirstBlankCell.Activate
Dim Label As Range
Set Label = Range("A1:C15")
Label.Select
Selection.Copy
FirstBlankCell.Select
ActiveSheet.Paste
Set FirstBlankCell = Range("b" & Rows.Count).End(xlUp).Offset(3, 0)
FirstBlankCell.Activate
Selection.Offset(-15, 1).Select
Selection.Value = "=INDEX('Shipping Data'!A2:A350,MATCH(TRUE,INDEX(('Shipping Data'!A2:A350<>0),0),0))"
Range("A16:C100000").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Shipping Data").Select
Dim EmptyCell As Range
Set EmptyCell = Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
EmptyCell.Activate
End Sub
Regards Agata