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
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
Last edited:
