Master sheet to split sheets back to another master sheet

Hi - you can cycle through the worksheets and do something with them unless they are the MIS or OUTPUT sheet. For copying columns there are lots of ways to do it. If you want to copy different rows per column you can check the row of each and copy the columns one by one; if you just want to copy down to the last row of the sheet you can use UsedRange.Rows.Count.

If you step through the code below you should be able to follow it demonstrate both options:

Private Sub CycleAndCopy()
Dim wks As Worksheet, wksMIS As Worksheet
Dim i As Long, lngRow As Long
Dim rng As Range
Dim arrSourceCols As Variant, arrTargetCols As Variant

    arrSourceCols = Array("A", "F", "G", "H", "I", "J")
    arrTargetCols = Array("A", "B", "C", "D", "E", "F")
    
    'cycle through worksheets:
    For Each wks In ThisWorkbook.Worksheets
        If wks.Name <> "MIS" And wks.Name <> "OUTPUT" Then
        
            'either: copy paste specific rows from each col one by one:
            For i = LBound(arrSourceCols) To UBound(arrSourceCols)
                'last row in each column:
                lngRow = wks.Range(arrSourceCols(i) & Rows.Count).End(xlUp).Row
                'copy the range:
                Set rng = wks.Range(arrSourceCols(i) & 1 & ":" & arrSourceCols(i) & lngRow)
                rng.Copy Destination:=wksMIS.Range(arrTargetCols(i) & 1 & ":" & arrTargetCols(i) & lngRow)
            Next i
            
            'or: copy paste in one hit using last row in sheet:
            i = wks.UsedRange.Rows.Count
            Set rng = wks.Range("A1:A" & i, "F1:J" & i)
            rng.Copy Destination:=wksMIS.Range("A1:F" & i)
        End If
    Next wks
    
End Sub