Do Until Loop logic help

[VBABEGINER]

Do Until lx = 12
    
Loop

12 is Col L.

how do i mention lx is the column to increment..??

 

[Joe4]

You need to have an incrementer in your loop like this:

Do Until lx = 12
    ...
    lx = lx + 1
Loop

If you are trying to reference or update some range within your loop, you would reference it like this:
Cells(row reference, lx)

 

[VBABEGINER]

Hi Joe,

Many Thanks for your reply. Can you please help me build below code please..

'current lx = 8 i.e. Col H
    lx = 8
    Cells(2, lx).Select
    
    Do Until lx = 12
    
    x = Sheets.Count
    
    For Shx = 1 To x
    
        j = Range("F" & Rows.Count).End(xlUp).Row
    
        'Here lx = 8
        Range(Cells(2, lx), Cells(j, lx)).Select
        
        Selection.Copy
        
        Worksheets("Combine").Select
        
        k = Range("F" & Rows.Count).End(xlUp).Row
        
        Cells(k + 1, lx).Select
        
        ActiveSheet.Paste
        
        Application.CutCopyMode = False
        
        'go to next sheet here..
    Next
    
        'do not increament loop..
    
    lx = lx + 1
    Loop

 

[Joe4]

Please explain exactly what you want to code to do.
I don't like trying to figure it out from errant code, as that often leads to incorrect assumptions.

 

[VBABEGINER]

I have total now 8 sheets.


At the end I want to add one sheet, named "Final Data"


In every sheet there are monthly wise data available.






Sheet structure are like this,


Col A to Col F are fix part in all the sheet's.




Col G onwards,


Col G = Jan 18
Col H = Feb 18
Col I = Mar 18
Col J = Apr 18
Col K = YTD 2018 ....(next month col K will be May 18)
Col L = Comments....(next month col L will be YTD 2018)




Now, output require in this format..


Sheet1 data Jan 18
Sheet2 data Jan 18
Sheet3 data Jan 18
Sheet4 data Jan 18
Sheet5 data Jan 18
Sheet6 data Jan 18
Sheet7 data Jan 18
Sheet1 data Feb 18
Sheet2 data Feb 18
Sheet3 data Feb 18
Sheet4 data Feb 18
.
.
.
and so on...


In the 1st part of code, i have did collation of data till Jan'18.




Stcuked in 2nd part, need help in design logic or code in this part..




Please revert me, in case the explanation not clear from my side.


Thanks again, for your valuable time..!!

Entire Code-

Sub fnCombine()
Dim i As Long, j As Long, LC As Integer
Dim ws As Worksheet
Dim x As Integer, z As Integer


Application.ScreenUpdating = False
Application.DisplayAlerts = False


Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Combine"


'1st part
For Each ws In ThisWorkbook.Sheets
    If ws.Name = "Combine" Then
    Else
        ws.Activate
        i = Range("F" & Rows.Count).End(xlUp).Row
        LC = Cells(1, Columns.Count).End(xlToLeft).Column
        If ws.Index = 1 Then
            Range("A1:G" & i).Select
            Selection.Copy
            Worksheets("Combine").Select
            Range("A1").Select
            ActiveSheet.Paste
        Else
        Range("A2:G" & i).Select
        Selection.Copy
        Worksheets("Combine").Select
        j = Range("F" & Rows.Count).End(xlUp).Row
        Range("A" & j + 1).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        End If
    End If
Next
'/1st part


    
'2nd part
'current lx = 8 i.e. Col H
    lx = 8
    Cells(2, lx).Select
    
    Do Until lx = 12
    
    x = Sheets.Count
    
    For Shx = 1 To x
    
        j = Range("F" & Rows.Count).End(xlUp).Row
    
        'Here lx = 8
        Range(Cells(2, lx), Cells(j, lx)).Select
        
        Selection.Copy
        
        Worksheets("Combine").Select
        
        k = Range("F" & Rows.Count).End(xlUp).Row
        
        Cells(k + 1, lx).Select
        
        ActiveSheet.Paste
        
        Application.CutCopyMode = False
        
        'go to next sheet here..
    Next
    
        'do not increament loop..
    
    lx = lx + 1
    Loop
'/2nd part


    
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

 

[Joe4]

Just trying to understand...

In Part 1, why are there two different parts of the IF clause, if the Index is 1 or not?
What exactly is Part 1 copying?

And then in your second part, what exactly is that copying? Is it just supposed to be an extension of the first part?

It might be helpful to see a small sample of your actual data and then your desired result.

 

[VBABEGINER]

Sure..

Headers from A1

[TABLE="width: 1191"]
<tbody>[TR]
[TD="class: xl65, width: 100"]Sr. No.[/TD]
[TD="class: xl65, width: 143"]Data Responsibilty[/TD]
[TD="class: xl65, width: 97"]Source[/TD]
[TD="class: xl65, width: 111"]Metric Type[/TD]
[TD="class: xl65, width: 126"]Metric Name[/TD]
[TD="class: xl65, width: 101"]BU[/TD]
[TD="class: xl65, width: 77"]Jan '18[/TD]
[TD="class: xl65, width: 77"]Feb '18[/TD]
[TD="class: xl65, width: 77"]Mar '18[/TD]
[TD="class: xl65, width: 77"]Apr '18[/TD]
[TD="class: xl65, width: 77"]YTD 2018[/TD]
[TD="class: xl65, width: 128"]Comments, If any.[/TD]
[/TR]
</tbody>[/TABLE]

A2 to A24 - Merge rows
Sr No = 1

B2 to B24 - Merge rows
Data Responsibilty = Name of the Manager

C2 to C24 - Merge rows
Source = ABC

D2 to D24 - Merge rows
Metric Type = Type 1

E2 to E24 - Merge rows
Metric Name = Type 1 name

F2 to F24
BU names (BU means Business Unit name) *which are 24 for all the Metric Type (i.e. Col D)
A2 = europe
A3 = america
A4 = russia
.
.
.
A24 = India


So, from Col F onward data are in every cell.

Till Col F, data for every sheet are common..

So on sheet 1, manager have only one metric (i.e. in Col D) hence data only till row 24.

on sheet 2, manager have only one metric, hence data only till row 24.

on sheet 3, manager have only 5 metric's, hence data till row 2-24, 25-47, 48-70, 71-93,94-116.

 

[VBABEGINER]

So if you look at my first, i have copied till Jan'18 data in Combine sheet from all the sheet's.

Now, again go sheet 1, copy from A2 to F & last rows, paste in combine sheet --> rows.count).end(xlup).row +1

again go to sheet 1 copy Feb month data into combine sheet.

This loop do till last column of sheet which is before to "Combine" sheet.

So I will get output like this..

Sheet1 data Jan 18
Sheet2 data Jan 18
Sheet3 data Jan 18
Sheet4 data Jan 18
Sheet5 data Jan 18
Sheet6 data Jan 18
Sheet7 data Jan 18
Sheet1 data Feb 18
Sheet2 data Feb 18
Sheet3 data Feb 18
Sheet4 data Feb 18
.
.
.
and so on...

 

[Joe4]

OK, this has gotten infinitely more complex than your original question, and I think I am getting lost in the partial details (without having access to the actual data, it is difficult to fully comprehend it).

However, perhaps your issue is simple. When looping through sheets, the sheets are NOT automatically selected/activated. That means that if you do not select or activate the sheet, or use sheet references in your ranges references, all your activity will be happening to the same sheet! (specifically, the ActiveSheet when the loop is first called).

To show this, try this simple example and see what happens:

Sub Test1()

    Dim x As Long
    Dim Shx As Long
    
    x = Sheets.Count
    
    For Shx = 1 To x
        MsgBox "Shx = " & Shx & vbCrLf & _
                "Performing code on sheet: " & ActiveSheet.Name
    Next Shx
        
End Sub

See how it never actually moves off of the ActiveSheet you started on?

Now make this change and see what happens:

Sub Test2()

    Dim x As Long
    Dim Shx As Long
    
    x = Sheets.Count
    
    For Shx = 1 To x
[COLOR=#ff0000]        Sheets(Shx).Activate[/COLOR]
        MsgBox "Shx = " & Shx & vbCrLf & _
                "Performing code on sheet: " & ActiveSheet.Name
    Next Shx
        
End Sub

The other option (instead of selecting/activating the sheet), is to use sheet references with all your ranges, i.e.
instead of:

Range("A1")

use:

Sheets(Shx).Range("A1")

See if that helps solve the issue you are having.

 

[VBABEGINER]

Happy to see your reply. But, Im not that so much techee guy.. If you could help me in write the code, will much better for me.

Are my rest code was correct or enough to loop within the column..?