Another loop problem

[SIMBAtheCAT]

Hi,

I am making a loop to insert values in every Range("A1") of every sheet corresponding. However, when I run the code, it doesn't insert any value.

Please Login or Register  to view this content.

[Norie]

You aren't looping through sheets, you are looping through the range A1:A10000 on the sheet 'Présentation'.

Does that range have sheet names?

[SIMBAtheCAT]

Yes, the ranges correspond to sheet names.

There is many more range in this sheet that I want to select the value that is why I'm looping through the sheet.

[Norie]

To loop through the sheets in a workbook you would normally use something like this.

Please Login or Register  to view this content.

If you have the names of the sheets you want to loop through in column A of a particular sheet then you would do it like this.

Please Login or Register  to view this content.

[SIMBAtheCAT]

Great, now it copies the value but the wrong ones. Could you help to copy the value just one cell up of the sheet name in the main sheet. Here is how the "Présentation" sheet looks like :


- 3 -
CRTC - SOMMAIRE FINANCIER - SERVICES SPÉCIALISÉS ET PAYANTS

# D'ENTREPRISE Nom du service Titulaire Propriétaire ultime Type Année
tv channel blabla Communications Co- Vidéo sur demande 2013'That is the value I'm looking the copy for every sheet corresponding in Range("A1")
Regina operative Limited operative Limited
Feuille12'That is the sheet name
TCAC = Taux de croissance annuel composé


- 4 -
CRTC - FINANCIAL SUMMARY - PAID SERVICES

# D'ENTREPRISE Nom du service Titulaire Propriétaire ultime Type Année
tvchannel1 inc. Service spécialisé (cat. A) 2013 'That is the value I'm looking the copy for every sheet corresponding in Range("A1")
Feuille13 'That is the sheet name
TCAC = Taux de croissance annuel composé

etcetera ... to "Feuille263" or in English "Sheet263"

Please Login or Register  to view this content.

[Norie]

To get the cell one row above use Offset(,-1).

Please Login or Register  to view this content.

[SIMBAtheCAT]

Perfect, one last issue. It seems to jump a sheet so the last one "Feuille263" is empty and all the titles are on the wrong sheet.

[SIMBAtheCAT]

Worst, now the code run but simply does nothing. Here is the file to sort this issue faster (File barely exceed the limit size of the forum) :

[Norie]

Can you upload an example workbook here?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

[SIMBAtheCAT]

Sure, but keep in mind that I had to delete a couple of sheets to make it lighter.

[Norie]

Oops, mixed up rows/columns in the offset.

Please Login or Register  to view this content.

[SIMBAtheCAT]

Perfect! Thanks for your help Norie.