VBA Code Loop Copy Paste Transpose

[Jeanpierre]

Good day, I am newish to Visual Basic, apologies if this has been posted but i have searched through most of the forums and could not find this specific one. Basically what I need to do is a data sort from a file that information is not aligned properly.

It comes with information in Cell A, Cell B, Cell C, then Cell C2 (this cell needs to be text to column, comma sorted and then transposed), which I need to put in Cell A Cell B and Cell C.
The first part is easy, create a new data page, change data into columns (not sure if can avoid this step) as below:

Columns("C:C").Select
    Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2)), TrailingMinusNumbers:=True
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "Data Output"
    Sheets("Demo").Select
    Range("C1").Select
    Sheets("Data Output").Select
    ActiveCell.FormulaR1C1 = "Cell A"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Cell B"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Cell C"
    Range("A2").Select
    Sheets("Demo").Select
    Range("B1").Select
    Selection.Copy
    Sheets("Data Output").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

Now i need to copy and paste the data from the demo sheet neatly into data output sheet but the cells in C2 need to be copied and transposed on data output sheet and then this code needs to be looped for every repeat of cell C1 & Cell C2 till no data in column C.

   Range("B2").Select
    Sheets("Demo").Select
    Range("C1").Select
    Selection.Copy
    Sheets("Data Output").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("C2").Select
    Sheets("Demo").Select
    Range("C2:V2").Select
    Selection.Copy
    Sheets("Data Output").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Range("B2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("B3:B5").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A1").Select
  
  Application.CutCopyMode = False

Images attached shows before and after, so I just need to loop it, and the code above might be primitive to the professionals, done with recording. Thank you.

 

[johnnyL]

@Jeanpierre, your included pics are not that clear. Can you upload the file represented in image1 to here <--- and then provide us the link here.

 

[Peter_SSs]

Welcome to the MrExcel board!

Can you show us more of the expected results sheet?

 

[johnnyL]

Ok, without being able to test any data thus far, I think the following shortened code will handle the first section of code:

'   Destination Range, DataType, FieldInfo, TrailingMinusNumbers ... the '2' in the arrays = xlTextFormat
'   Defaults are: ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=False
'
    ActiveSheet.Range("C:C").TextToColumns , Range("C1"), xlDelimited, xlDoubleQuote, , True, , True, , , _
        FieldInfo:=Array(Array(1, xlTextFormat), Array(2, xlTextFormat), Array(3, xlTextFormat), Array(4, xlTextFormat), Array(5, xlTextFormat)), _
        TrailingMinusNumbers:=True
'
    Sheets.Add(After:=ActiveSheet).Name = "Data Output"
'
    Range("A1").FormulaR1C1 = "Cell A"                      ' Write "Cell A" to cell A1 on Sheet 'Data Output'
    Range("B1").FormulaR1C1 = "Cell B"                      ' Write "Cell B" to cell B1 on Sheet 'Data Output'
    Range("C1").FormulaR1C1 = "Cell C"                      ' Write "Cell C" to cell C1 on Sheet 'Data Output'
'
    Sheets("Demo").Range("B1").Copy Sheets("Data Output").Range("A2")   ' Copy Range("B1") from the 'Demo' sheet to Range("A2") on sheet 'Data Output"

 

[Jeanpierre]

Good day Gentlemen, thank you for response, apologies for delay, will try out the code thank you Johnny appreciate it.

testfile001.xlsx
	A	B	C
1	Cell 1	224457783	22445786
2			5050004454,55447778866,5544478993,5555552247
3			22445786
4			5050004454,55447778866,5544478993,5555552247
5			22445786
6			5050004454,55447778866,5544478993,5555552247
7			22445786
8			5050004454,55447778866,5544478993,5555552247
9	Cell 2	224457784	22445787
10			5050004455,55447778865,5544478924,5555552244
11			22445787
12			5050004455,55447778865,5544478924,5555552244
13			22445787
14			5050004455,55447778865,5544478924,5555552244
15			22445787
16			5050004455,55447778865,5544478924,5555552244
Demo

So this is sheet 1, below is how it needs to pop out:

testfile001_step1.xlsx
	A	B	C
1	Cell A	Cell B	Cell C
2	224457783	22445786	5050004454
3		22445786	55447778866
4		22445786	5544478993
5		22445786	5555552247
Data Output

So cell c, might run up to 1000 lines etc, it may only be 20. So with the short ones its easy, but if need to repeat it gets tedious. So basically it must just run until cell c comes to blank cell, but must put data onto new sheet every 4 rows.

 

[Peter_SSs]

Can you show us more of the expected results sheet?

The expected results are still not clear to me.

Will the next 4 rows of results be
- the same as rows 2-5 shown above since there are 4 sets of identical rows for 224457783 in the Demo sheet, or
- move straight on to 224457784 at row 9 of the Demo sheet?

 

[Jeanpierre]

Yes sorry, so cell structure on output file must be:

B1 (demo) to A2 (data output), C1 (demo) to B2 (data output) then this must be copied down to B5, then C2 (demo) to C2 (data output) transpose up to C5, then repeat loop,

will then take B1 (demo) to A6 (data output), C3 (demo) to B6 (data output) copy down to B9, C4 (demo) to C6 (data output) transpose to C9, then repeat until cell c (demo) is blank.

If sheet is small its easy to run like a manual script, but cannot find a the loop code to do this.

 

[Peter_SSs]

structure on output file must be:

Can you show us?

 

[johnnyL]

So cell c, might run up to 1000 lines etc, it may only be 20. So with the short ones its easy, but if need to repeat it gets tedious. So basically it must just run until cell c comes to blank cell, but must put data onto new sheet every 4 rows.

New sheet after every 4th row? That would be 250 sheets if you have 1000 lines of C.

 

[johnnyL]

Since there is still confusion of what the sample output would look like, Is the following what the goal is:

Book1
	A	B	C
1	Cell A	Cell B	Cell C
2	224457783	22445786	5050004454
3		22445786	55447778866
4		22445786	5544478993
5		22445786	5555552247
6		22445786	5050004454
7		22445786	55447778866
8		22445786	5544478993
9		22445786	5555552247
10		22445786	5050004454
11		22445786	55447778866
12		22445786	5544478993
13		22445786	5555552247
14		22445786	5050004454
15		22445786	55447778866
16		22445786	5544478993
17		22445786	5555552247
18	224457784	22445787	5050004455
19		22445787	5050004455
20		22445787	5050004455
21		22445787	5050004455
22		22445787	5050004455
23		22445787	5050004455
24		22445787	5050004455
25		22445787	5050004455
26		22445787	5050004455
27		22445787	5050004455
28		22445787	5050004455
29		22445787	5050004455
30		22445787	5050004455
31		22445787	5050004455
32		22445787	5050004455
33		22445787	5050004455
Data Output

??? Its not formatted properly, but is that the jist of it @Jeanpierre ?