Unique record creation using pairs of columns each loop

[SammyCRX]

Hi all,

I've been tasked with replicating a billing tool made by somebody who has long since left the company. I looked at their code and I think amending it to match our new style reports would simply be too complicated, not least of all because I don't fully understand a lot of what they've done (I'm strictly amateur, not a solid coder as they were). The first problem is to create a unique row for each record and cost centre. My solution below is to copy column A:G from CLI Lookup and paste that in my 'full list'. From this point, I need to place pairs of columns from my CLI Lookup tab alongside these records. The first pair of columns is H:I, which I've dealt with in the code below. This works absolutely fine, but as you've probably guessed I have far more than two columns to do this for (otherwise I would've just gone with A:I in the first place). On the CLI Lookup tab, I have cost centre information in pairs of columns J:K, L:M, N:O... you get the picture! Currently this runs to BH:BI and potentially could expand, although it seems unlikely. So what I need it to do is to replicate what I've done below but over and over. I need to paste all the information for the Range("A2:G" & LastRowWithValueInColumnA).Select below each batch and add the next pair of cost centre columns next to it until it has completed all cost centres. Once I have that I plan to remove a few columns near the left, then remove any blank rows where no cost centres exist (some of these records will have just one cost centre, others have loads).

Please could you advise the best way to loop this? I realise I could just keep writing the code over and over, adding in a new 'nextbatch' reference etc. but that's clearly absurd. I think I need to use 'for each' or something like that, along with 'next'. That sort of thing. I have done this sort of stuff in the past, but with it being non-sequential (pairs of columns) I've got my brain in a complete muddle! Any help would be greatly appreciated.

Thanks,
Sam

Sub Unique_Row_Generation()
'
' Unique_Row_Generation Macro
' Creates a unique row for each CLI and cost centre
'

'
'Puts an empty record in the full list to force it to start on row 3.
Sheets("CLI Full List").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "BLANK"

' Finds last row of data on the spreadsheet
Sheets("CLI Lookup").Select
LastRowWithValueInColumnA = Columns("A").Find("*", , xlValues, , xlRows, xlPrevious).Row
'First section pastes the general data in line with CLI, although much of this is to be removed later.
Range("A2:G" & LastRowWithValueInColumnA).Select
Selection.Copy
Sheets("CLI Full List").Select
'Finds the last row in column A with a value
NextBatch = Columns("A").Find("*", , xlValues, , xlRows, xlPrevious).Row
'...then pastes below
Range("A" & NextBatch).Offset(1).Select
ActiveSheet.Paste

'This copies the cost centre columns
Sheets("CLI Lookup").Select
Range("H2:I" & LastRowWithValueInColumnA).Select
Application.CutCopyMode = False
Selection.Copy
'This pastes the cost centre column
Sheets("CLI Full List").Select
Range("H" & NextBatch).Offset(1).Select
ActiveSheet.Paste


End Sub

 

[SammyCRX]

If nobody has any ideas then what I might do is to create however many batches of cells A:G by looping 'x' number of times and then VLOOKUP into the original file, just with the formula looking up an extra 2 columns across for each batch to fill in the information to the right. That would be horribly slow I think, but I guess it would work!