Got Stuck at the logic of copying from sheet 1 to sheet2 after getting range address of formulated serial nos in col A of sheet1

[SamDsouza]

Hello
In sheet 1:
Cols : A to C are fixed
Header Row = 3 with Col A =Sr No: Col B :Main Prod code and Col C: Prod Description
in Col A Sr No are calculated with formula ie A4= =1 A8= =A4+1 and so on
With the below code I am able to get Range of Each Formulated Serial No. from sheet1
Also the file link is provided below

Option Explicit

Private Sub UserForm_Initialize()
Load UserForm1
UserForm1.Show vbModeless
Call AddSheetCopyColsRangeSrNos
End Sub

Public Sub AddSheetCopyColsRangeSrNos()

Dim wks As Worksheet
Set wks = Worksheets("Sheet1")

  Dim Ray() As String
  Dim c As Range, LastA As Range
  Dim rws As Long, k As Long
  wks.Activate
  Set LastA = wks.Range("A" & Range("C" & Rows.Count).End(xlUp).Row)
  For Each c In wks.Range("A4", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlFormulas)
    rws = 1
    If IsEmpty(c.Offset(1).Value) And c.Address <> LastA.Address Then rws = rws + Range(c, LastA).SpecialCells(xlBlanks).Areas(1).Rows.Count
    k = k + 1
    ReDim Preserve Ray(1 To k)
    Ray(k) = c.Value & " " & c.Resize(rws, 3).Address(0, 0)
  Next c
     ComboBox1.List = Ray
     ComboBox1.Text = Ray(1)
End Sub

Ie in combobox1 dropdown list
1 A4:C7
2 A8:C10
3 A11:C12
4 A13:C13
5 A14:C23 and so on
In the range A14:C23 there are optional offers range for the products which are in range from B4 to B14 for which I would like to copy for after Each Prodcut with coding in sheet 2
I got stuck in logic for coding to copy after getting the range address with above code.
In Sheet1 Optional Offer are different for Each Product as you can see
In Sheet 2 I ve added two columns ie ST/OP and “Prod Code with OP/Offers” the reason to add two columns was to use Filter in future
So what I want is to copy the range of Optional Offer after each Formulated Serial No of Main Prod

Eg Copy Optional Offers of Prod 1 ie range FromB16:C22 From Sheet1 to sheet 2 in range D8 to E14
And in Sheet2
range B4: to B14 to have only values “Prod1”
Range c4:C7 to have only values “ST”
Range C8:C14 to have only values “OP”
Sheet 2 is displayed for what I desire
Basically there are 10 products and the above was shown for 1 product
PFA link to download

Product-Optional-Offer.xlsm

Shared with Dropbox

www.dropbox.com

Thanks
SamD
106

 

[SamDsouza]

Hello Anyone

What i need is to get the range of Optional Offer after Each Formulated Sr.No as per above code mentioned in post #1 i need to get the range address of Optional Offers
If get the range address of Optional offers after Each Formulated Sr.No Range then copy syntax would be manageable
Would request you to pl study the file Sheet 1 and Output as in sheet2
Or if you could give a ref of any other thread which would be as similar as mine would also appreciate.
Also i am open to any other idea of coding apart from above ie if my logic is incorrect to what i desire
Representing output as per sheet 2 manually is task taking.

Will really appreciate if some replies are initiated

SamD
107

 

[SamDsouza]

Can anyone help me ? I am really struggling

SamD
108

 

[SamDsouza]

Guys do you want me to post new thread for the similar type of question.
Or guide me to make still better clarity for you to understand the thread.
Am really strugling to get the range address from word Optional offers uptil the first blank row after getting the range address of formlated sr.nos
file attached in #post1 of this thread

SamD
109