data redirection from one Excel sheet towards another one with a macro

[Max_F]

Dear all,

I have some trouble with an Excel macro. I could find the command to get data from one sheet (sheet 1) to paste them to another one (sheet 2) to do so cell by cell. Now, I would like to write a loop to make it automatic on all the data of my sheet 1 (which are all in one single column) so that all these data are put into my sheet 2 but in one single row and only in one out of three cells. Here attached is a simple example of what I want to do with this macro.

Here is the code I could write but it doesn't work as I want:

Sub boucle_for()

N = 20 'number of lines in my data source file
initialization_number_line_file_data_source = number_line_file_data_source - 1

   For i = 1 To N
               
       Range("H9:BQ9").FormulaR1C1 = _"=[name_file_data_source.xlsx]Sheet1!Rinitialization_number_line_file_data_source+iC2"
               
   Next

EndSub

If someone can help me with an example it would be very nice of you.

Cheers,

Max

 

[prasaddn]

Hi Max,

The explanation provided and the example does not seem to be quite done with one line command.

this would need two loops of VBA Code, but to write the same, can you upload with some real data that you are going to use. As it vba code may need to take turn in loops based on contents and coding them with A1, B1 or Serie may not work with your real data.

Regards,
Prasad DN