Copy range and paste multiple times after editing

[Ajayvarrier]

Hi All,
I have a scenario in my Work.

I need to do different types of pricing for variable number of items every week and enter in to the system say:

Price:

Wholesale Price:

Commission Price:

Special Price:

Please help to create a macro to do this task.

IMP: Number of lines will be different all the time, Example files Attached
__________________________________________________________________

Mod edit : thread moved to appropriate forum !​

 

[p45cal]

Press the button in the attached file.
Otherwise run the macro blah after making the source sheet the active sheet (by selecting it). It will add a new sheet with the new data in it.

For those interested, the code:

Sub blah()
'Set SceData = Sheets("myron-cristina").Cells(1).CurrentRegion.Columns(1)
Set SceData = ActiveSheet.Cells(1).CurrentRegion.Columns(1)
Set NewSht = Sheets.Add(after:=Sheets(Sheets.Count))
Set Destn = NewSht.Cells(1): ofset = 0
priceTypes = Array("aed", "Wholesale", "Commision", "special")  'note 'commision' is spelt wrongly.
rates = Array(1, 0.65, 1.1, 0.75)
d3 = Date
d6 = "each"
i = LBound(priceTypes)
For Each priceType In priceTypes
  d2 = "price" & IIf(priceType = "aed", "", "_" & priceType)
  d4 = priceType
  For Each cll In SceData.Cells
    d1 = cll.Value
    d5 = cll.Offset(, 1).Value
    d8 = cll.Offset(, 2).Value * rates(i)
    Destn.Offset(ofset).Resize(, 8).Value = Array(d1, d2, d3, d4, d5, d6, , d8)
    ofset = ofset + 1
  Next cll
  i = i + 1
Next priceType
NewSht.Columns("A:H").EntireColumn.AutoFit

End Sub

It could be made faster.

 

[Ajayvarrier]

Thank you soooo much,

It will save days in my life. I was doing this manually using formula's and it is obviously time consuming.

Just One more Question.

I Tried using Mround in the same macro, but its showing

"Sub or Function not Defined".

Why its Like that. I have already checked atpvbaen.xls in reference. Still showing error.

This is what I did

  d8 = MRound((cll.Offset(, 2).Value / rates), 0.5)(i)

 

[p45cal]

try:
d8 = Application.MRound((cll.Offset(, 2).Value * rates(i)), 0.5)

 

[Ajayvarrier]

Awesome!!!! Thank You Very much

Its a huge help you've provided

Thank You Very much

Ajay