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
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
Thanks
SamD
106
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
VBA Code:
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
Dropbox
www.dropbox.com
SamD
106
Last edited: