Duplicate lines N-1 times according to N string

Good morning everyone!

I am working with a file composed of 7 columns.
One of them, column B is housing Customer number(s). The aim of the file I am working with is to be SAP-compliant, hence only ONE customer number can be present in column B.
However, some clients have been associated with multiple client numbers, from 2 to 5. Customer numbers can be 6 or 8 digits.
When it is the case, those numbers are stored in column B separated by one space, everytime.

When facing multiple client numbers, let's call this number N, I have to manually copy the whole set of lines associated with this client and paste it N-1 times. Then I associate the first code with the first set of line, the second code to the second set of lines etc.
EXAMPLE: see image attached!

I was wondering how I could code this in VBA !
(1) How can I code something to detect how many strings separated by one space there is in a given cell ?
(2) How can I code "If there are N customer numbers then copy-paste all the lines associated N-1 times below
(3) How can I code "Then associate the first string/code with the first set of lines, and so on.. ?

Is this even possible in VBA ?

Waiting forward to see your comments on this scenario,
Thank you!

Hi,

Yes of course it's possible in VBA. I've just had a quick look and can see a reasonably straightforward solution. I'm just going out at the moment so will post something later - unless someone has stepped in in the meantime.

Hi,

See the attached file it uses the code below and some dynamic range names that I've added to the file.

The system starts by adding a new H column which carries the number of customers less 1 on a typical row. From this column H column J then gets populated with a unique list of these numbers.

The system then goes into a loop which iterates for as many times as there are unique lengths of customer strings.
Each time this loop is processed the ConsoSHeet data is filtered to a temporary data area in columns I:P on the Duplicated sheet.
An inner loop is then processed which iterates for as many times as there are customer numbers in the current filtered data. Each time this inner loop is processed the current customer number replaces the column J string of numbers in the temporary data and then the temporary data area is copied to the next available row in the cumulative area on the Duplicated sheet in columns A:G.

I added a small check table in J2:M7 to ensure that the number of rows output - 4010 on the duplicated sheet matched the calculation from the Conso Sheet

Sub CreateDuplicates()
    Dim lLastRow As Long, lRept As Long, arCust() As String, lCustNo As Long, x As Long
    Application.ScreenUpdating = False
    arCust() = Split(Range("J2"), " ")
    Sheet2.Range("A1").CurrentRegion.Offset(1, 0).ClearContents
    lLastRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
    Sheet1.Range("H2:H" & lLastRow) = "=LEN(B2)-LEN(SUBSTITUTE(B2,"" "", """"))"
    Range("Data").AdvancedFilter Action:=xlFilterCopy, copytorange:=Range("LengthList"), unique:=True

    For lRept = 1 To Range("LengthList").CurrentRegion.Rows.Count - 1

        Range("DataOut").CurrentRegion.Offset(1, 0).ClearContents
        Range("crit").Cells(2, 1) = Range("LengthList").Cells(lRept + 1, 1)
        Range("Data").AdvancedFilter Action:=xlFilterCopy, copytorange:=Range("DataOut"), criteriarange:=Range("crit")
        arCust() = Split(Sheet2.Range("J2"), " ")
        lCustNo = UBound(arCust()) + 1
        lLastRow = Sheet2.Range("I" & Rows.Count).End(xlUp).Row
        For x = 0 To lCustNo - 1
            Sheet2.Range("J2:J" & lLastRow) = arCust(x)
            Range("Data_Temp").Offset(1, 0).Copy Destination:=Sheet2.Range("A" & Rows.Count).End(xlUp).Cells(2, 1)
        Next x

    Next lRept
    Application.ScreenUpdating = True

End Sub

Hi,

See the attached which corrects the structural problem - I think, but check.

The dynamic range names used are

Data
Length List
DataOut
Crit

You can see the definition of these names in the Formula Names Manager menu item in the Ribbon

Works perfectly on the TEST file!
Thanks again Richard.


I am now trying to implement it to my previous macro and thus to my real file.
I will keep you updated.
----------------------------------------------------


I tried to adapt it to dynamic mode as I will be using it A LOT on different files.
I had a look into the Formula Name manager as advised. Then I tried to declare those ranges as variables and to set them as follows.
however I keep getting to errors such as "object required" pointing to this line :

 Sheet2.Range("A1").CurrentRegion.Offset(1, 0).ClearContents

i am aware that I have no sheet named Sheet2 but when I run your code in your file it works perfectly. Even if there is no "Sheet2" in the test file.
I'm quite puzzled ...

Option Explicit

Sub CreateDuplicates()
    Dim lLastRow As Long, lRept As Long, arCust() As String, lCustNo As Long, x As Long

Dim DataOut As Range
    Set DataOut = Range("Duplicated!$I$1:$P$1")
    
   Dim crit As Range
    Set crit = Range("=Duplicated!$R$1:$R$2")
    
    Dim LengthList As Range
    Set LengthList = Range("=ConsoSheet!$J$1")

   Dim Data As Range
    Set Data = Range("=OFFSET(ConsoSheet!$A$1;0;0;COUNTA(ConsoSheet!$A:$A);8)")


    Application.ScreenUpdating = False
    arCust() = Split(Range("J2"), " ")
    Sheet2.Range("A1").CurrentRegion.Offset(1, 0).ClearContents
    lLastRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
    Sheet1.Range("H2:H" & lLastRow) = "=LEN(B2)-LEN(SUBSTITUTE(B2,"" "", """"))"
    Range("Data").AdvancedFilter Action:=xlFilterCopy, copytorange:=Range("LengthList"), unique:=True

    For lRept = 1 To Range("LengthList").CurrentRegion.Rows.Count - 1

        Range("DataOut").CurrentRegion.Offset(1, 0).ClearContents
        Range("crit").Cells(2, 1) = Range("LengthList").Cells(lRept + 1, 1)
        Range("Data").AdvancedFilter Action:=xlFilterCopy, copytorange:=Range("DataOut"), criteriarange:=Range("crit")
        arCust() = Split(Sheet2.Range("J2"), " ")
        lCustNo = UBound(arCust()) + 1
        lLastRow = Sheet2.Range("I" & Rows.Count).End(xlUp).Row
        For x = 0 To lCustNo - 1
            Sheet2.Range("J2:J" & lLastRow) = arCust(x)
            Range("Data_Temp").Offset(1, 0).Copy Destination:=Sheet2.Range("A" & Rows.Count).End(xlUp).Cells(2, 1)
        Next x

    Next lRept
    Application.ScreenUpdating = True

End Sub

Hi,

I think you're confusing the sheet tab name and the sheet VBA code name. Sheet2 is the VBA code name for the sheet tab named "Duplicated". Similarly Sheet1 is the VB code name for the "ConsoSheet" tab.

It's always good practice to use VBA Sheet code names in code since sheet tab names can easily be changed and if you hard code the tab name in the macro and someone renames the sheet tab then you're stuffed. The sheet code name can only be changed in the VB Environment so is usually well away from the users.

Incidentally I realise that the helper column H on the ConsoSheet is not required and hence the line of code

Sheet1.Range("H2:H" & lLastRow) = "=LEN(B2)-LEN(SUBSTITUTE(B2,"" "", """"))"

Can be removed. As a consequence the range name 'Data' should be changed to

Formula:

=OFFSET(ConsoSheet!$A$1,0,0,COUNTA(ConsoSheet!$A:$A),7)

i.e. it's now only 7 columns wide rather than 8. And the range name 'DataOut' should be changed to

Formula:

=Duplicated!$I$1:$O$1

rather than

Formula:

=Duplicated!$I$1:$P$1

Hi,

Did you change J1 on the Conso sheet and R1 on the Duplicated sheet to 'Order #' ?

I think you're confusing the two separate ideas of defining a range and defining a range name.
with code like

Dim DataOut As Range
Set DataOut = Range("=Duplicated!$I$1:$O$1")

you are attempting to create a range name and also define where that range should be. What the 'Set' instruction does is just define the range of cells that should be covered by the range name it doesn't create the range name. As written the code doesn't know where the range DataOut is since you haven't yet defined it.

To define a range name in a macro the code would be

Formula:

Sheet2.Range('Duplicated'!$I$1:$O$1").Name = "DataOut"

In this system the Range("DataOut") is only referred to once in the code I gave you so there's no reason to also Dim it as a range variable since you're only referring to it once. If a macro uses the range several times then it makes sense to define it as a variable so that the code is not jumping back to the Excel Application to find what "DataOut" refers to. Jumping backwards and forwards between VBA and the Excel App carries a time overhead - (one reason why for instance we often use Application.ScreenUpdating = False to avoid repainting the screen every time code changes something).


All that said and on a general point though there's no reason to use VBA to define the range names unless they change their range. Just create them in the Application and forget about them.
Where the range may be subject to change as in your system when the Conso sheet will have varying numbers of rows we use what's called a 'Dynamic' range name. i.e. one that knows it needs to adjust to changing numbers of rows or columns. In your workbook the range 'Data' is one such dynamic range name so even here we can define it in the Excel Application as

Formula:

=OFFSET(ConsoSheet!$A$1,0,0,COUNTA(ConsoSheet!$A:$A),7)

In summary just create the range names in the App with the name manager and forget about your DIM statements for these ranges. Incidentally it's regarded as good practice to put all DIM statements at the very top of each procedure rather than immediately prior to the code which first uses them.

If you still have problems upload a desensitived copy of your workbook

Hi,

Must have been a forum glitch. I don't know why post #8 touched down when I was still writing it. Post # 11 is essentially the same. But as I say don't bother trying to define your range names in the code, just create them directly with the Name Manager. But note the additional Q as the first point in #11

Originally Posted by Quisemar

Ok Richard, understood. I was really convinced that Set would set my range as described after the "=", but that is not the case and it explains why I am having troubles!

I will have to declare my ranges in VBA anyway as I wont be the only user of the macro, two other people will use it on their own computer, so as far as I understood it won't work for them if I only name my ranges through the Name Manager of my computer!

Thank you for your patience and your clear explanations!

Hi,

I'm somewhat confused. The Name Manager is an integral part of the Excel Application found in the Ribbon Formula menu and then in the 'Defined Names' area of the ribbon. It's not some external tool. Once the names are created in the Excel App you can forget about them. Mention of multiple computers is irrelevant since the name is integral to the workbook for anyone who uses it irrespective of which computer they're using.

You still seem to be trying to define the names with code. Did you try naming the ranges in your production workbook in the same way as they were defined in the example workbook?

I'll take a look at your workbook shortly.

Regards,

Hi,

See attached. All the range names have been defined in the Excel App not the code. I've also removed numerous names which were evaluating as #REF Errors and just cluttering things up. There was also an additional name that I'd defined in my original but hadn't listed in post #6.

The file is of course a .xlsm file. Your original was just a .xlsx so had no code. I've added the original code I gave you. You need to run the 'CreateDuplicates' macro.

Originally Posted by Richard Buttrey

Mention of multiple computers is irrelevant since the name is integral to the workbook for anyone who uses it irrespective of which computer they're using.

This is exactly where my problem sits. The macro I am willing to have will be used on multiple workbooks that any user can generate (via another macro that is already working) on its computer.
The Name Managers allows to save range names for a given workbook no matter on which computer the workbook will be working, that is what I learnt from you, however the current macro we are trying to build is not to be workbook specific. It really has to be operational on any workbook generated , provided that the said-workbook has the corresponding layout of course.

Do you understand my problematic ?
I am sorry for it seems I wasn't clear from the beginning !

The context is the following:
Those workbooks generated corresponds to price loads that will be used by SAP. Basically a new workbook of that type is generated by users every two weeks. It ALWAYS has the same layout (7 colomns, everytime the same info is displayed in the same column, e.g. Price is always in column D, Currency in E...)
In order for SAP to read those informations, info has to be displayed as it is in the workbooks I showed you. The problem with my current macro is that it does not separate customer numbers. SAP will not accept multiple client numbers for the same line of information, that is why I needed your precious help. Now that it has perfectly worked on the test workbook it would be wonderful to be able to run this macro on any generated workbook so there is no need of manual intervention to get the file SAP compliant.

Are you saying that the workbook is generated automatically by SAP (or some mother process perhaps) and that this process creates a virgin workbook and adds the data.

SAP generates multiple customer price lists, usually around 40 workbooks containing 3 worksheets which contains price informations.
Then we use a consolidation macro that takes the needed information from those 40 workbooks and consolidate it into one workbook but in a very messy way.
That is where I run my macro that classifies the info, put the right layout, add some information regarding currency and validity dates of prices.
When this is done I want to apply your macro in order to separate customer numbers so I can upload the content of this workbook back into SAP.

Is that clearer ?

So to answer your question clearly, yes this workbook is generated through SAP and a consolidation macro, formatted into 7 columns by another macro and I only need to deal with this customer number splitting then I can put it back into SAP !

Hi,

In that case the final workbook is not created by SAP since you are consolidating the 40 workbooks into a single workbook and presumably the macro process that does this is resident in the consolidation workbook.

If that single workbook is essentially the file you uploaded here then why can't you just incorporate the macro I gave you as the final macro after your consolidation macro has run.

Or am I missing the point completely?

Hi,

I don't see why creating the names won't work. You just need to ensure that the name you're trying to create is fully defined, i.e. that means ensuring you specify not only the range and worksheet but also the workbook. Remember there are two instructions that are relevant here, the ActiveWorkbook and ThisWorkbook. The ActiveWorkbook as its name implies refers to the workbook which is currently active when the macro is processed, ThisWorkbook refers to the workbook that contains the VBA code. Mostly the two are the same but when you have two workbooks open in memory you need to consider this.

Before, where I gave you code like

Sheet2.Range('Duplicated'!$I$1:$O$1").Name = "DataOut"

for creating a name with a macro you need to qualify this. So in your macro workbook you will need to have a line of code to first Activate the consolidated workbook that presumably contains the SAP data and then use code like the following to create all the relevant names needed

ActiveWorkbook.Sheets(Sheet1.CodeName).Range($I$1:$O$1").Name = "DataOut"

followed by the rest of the code for processing the macro I wrote.

You may need to change the reference to Sheet1 (which is the VB CodeName) if there is more than one sheet in the Consolidate workbook, and obviously the range. In other words the Sheets(Sheet1.CodeName) evaluates to the equivalent of Sheets("tab_name_for_the_VB_Sheet1_CodeName")