Sub test()

Dim WBSource As Workbook

Dim WSSource As Worksheet

Dim sourceDataRange As Range

Dim rowoffset As Long

Dim sourceCell As Range

Dim destinationCell As Range

Dim WBDestination As Workbook

Dim WSDestination As Worksheet

Set WSDestination = ThisWorkbook.Worksheets("Sheet1")

   WSDestination.Activate

   WSDestination.Cells.ClearContents

Set destinationCell = WSDestination.Range("A1")

Set WBSource = Workbooks.Open("C:\ExMacro\read.xlsx")

For Each WSSource In WBSource.Worksheets

'Set sourceDataRange = WSSource.Range("A1:" & _

 '    WSSource.Range("A" & Rows.Count).End(xlUp).Address)

 WSSource.Activate

 Set sourceDataRange = WSSource.Range("A3", Range("A3").End(xlToRight))

    For Each sourceCell In sourceDataRange

        If Not IsEmpty(sourceCell) Then

          destinationCell.Offset(rowoffset, 0) = sourceCell

                 rowoffset = rowoffset + 1

        End If

    Next

 Next

 Set sourceDataRange = Nothing

WBSource.Close SaveChanges:=False

Set sourceCell = Nothing

Set destinationCell = Nothing

End Sub

/*Students marks processing (second)*/

Option Explicit

Sub MarGen()

Dim MainBook As Workbook

Dim MainMarkSheet As Worksheet

Dim DupBook As Workbook

Dim DupMarkSheet As Worksheet

Dim studentRowRangeinMainSheet As Range

Dim MainMarkcell As Range

Dim DupMarkcell As Range

Dim studentRollNum As String

Dim rowIndex As Long

Dim colIndex As Long

Dim RollIndex As Long

Set DupMarkSheet = ThisWorkbook.Worksheets("sheet1")

Set DupMarkcell = DupMarkSheet.Range("A1")

     DupMarkSheet.Activate

     DupMarkSheet.Cells.ClearContents

     Set MainBook = Workbooks.Open("c:\ExMacro\Marks.xlsx")

     Set MainMarkSheet = MainBook.Worksheets("sheet1")

     studentRollNum = InputBox(Prompt:="Enter Your Roll Number", Title:="Enter Roll Number", Default:="give your Roll Number")

     'RollIndex = MainMarkSheet.Application.WorksheetFunction.Match("11251A1222", Range("A1:A53"), 0)

     RollIndex = MainMarkSheet.Application.WorksheetFunction.Match(studentRollNum, Range("A1:A53"), 0)

     Set studentRowRangeinMainSheet = MainMarkSheet.Range("A" & RollIndex, Range("A" & RollIndex).End(xlToRight))

     MsgBox "Found at row " & RollIndex & " total elements " & studentRowRangeinMainSheet.Count

     rowIndex = colIndex = 1

     For Each MainMarkcell In studentRowRangeinMainSheet

            DupMarkcell.Offset(rowIndex, colIndex) = MainMarkcell

            rowIndex = rowIndex + 1

        Next

     MainBook.Close SaveChanges:=False

     Set DupMarkcell = Nothing

     Set MainMarkcell = Nothing

     Set studentRowRangeinMainSheet = Nothing

     End Sub

/*Students marks processing (second)*/

Option Explicit

Sub MarGen()

Dim MainBook As Workbook

Dim MainMarkSheet As Worksheet

Dim NewBook As Workbook

Dim NewMarkSheet As Worksheet

Dim DupBook As Workbook

Dim DupMarkSheet As Worksheet

Dim studentRowRangeinMainSheet As Range

Dim MainMarkcell As Range

Dim DupMarkcell As Range

Dim NewMarkcell As Range

Dim studentRollNum As String

Dim rowIndex As Long

Dim colIndex As Long

Dim RollIndex As Long

Set DupMarkSheet = ThisWorkbook.Worksheets("sheet1")

Set DupMarkcell = DupMarkSheet.Range("A1")

     DupMarkSheet.Activate

     DupMarkSheet.Cells.ClearContents

     Set NewBook = Workbooks.Add

        With NewBook

          .Title = "IT Marks"

          .Subject = "Marks"

          .SaveAs Filename:="Itmarks.xlsx"

      End With

     Set NewMarkSheet = NewBook.Worksheets("sheet1")

     Set NewMarkcell = NewMarkSheet.Range("A1")

     Set MainBook = Workbooks.Open("c:\ExMacro\Marks.xlsx")

     Set MainMarkSheet = MainBook.Worksheets("sheet1")

     studentRollNum = InputBox(Prompt:="Enter Your Roll Number", Title:="Enter Roll Number", Default:="give your Roll Number")

     'RollIndex = MainMarkSheet.Application.WorksheetFunction.Match("11251A1222", Range("A1:A53"), 0)

     RollIndex = MainMarkSheet.Application.WorksheetFunction.Match(studentRollNum, Range("A1:A53"), 0)

     Set studentRowRangeinMainSheet = MainMarkSheet.Range("A" & RollIndex, Range("A" & RollIndex).End(xlToRight))

     MsgBox "Found at row " & RollIndex & " total elements " & studentRowRangeinMainSheet.Count

     rowIndex = colIndex = 1

     For Each MainMarkcell In studentRowRangeinMainSheet

            DupMarkcell.Offset(rowIndex, colIndex) = MainMarkcell

            NewMarkcell.Offset(rowIndex, colIndex) = MainMarkcell

            rowIndex = rowIndex + 1

        Next

     MainBook.Close SaveChanges:=False

     NewBook.Close SaveChanges:=True

     Set DupMarkcell = Nothing

     Set MainMarkcell = Nothing

     Set studentRowRangeinMainSheet = Nothing

     End Sub

/*Student marks three*/

Option Explicit

Sub MarGen()

Dim MainBook As Workbook

Dim MainMarkSheet As Worksheet

Dim NewBook As Workbook

Dim NewMarkSheet As Worksheet

Dim DupBook As Workbook

Dim DupMarkSheet As Worksheet

Dim studentRowRangeinMainSheet As Range

Dim MainMarkcell As Range

Dim DupMarkcell As Range

Dim NewMarkcell As Range

Dim studentRollNum As String

Dim rowIndex As Long

Dim colIndex As Long

Dim RollIndex As Long

Dim Iter As Long

Set DupMarkSheet = ThisWorkbook.Worksheets("sheet1")

Set DupMarkcell = DupMarkSheet.Range("A1")

     DupMarkSheet.Activate

     DupMarkSheet.Cells.ClearContents

     'Set NewBook = Workbooks.Add

      '  With NewBook

       '   .Title = "IT Marks"

        '  .Subject = "Marks"

         ' .SaveAs Filename:="Itmarks.xlsx"

      'End With

    ' Set NewMarkSheet = NewBook.Worksheets("sheet1")

     'Set NewMarkcell = NewMarkSheet.Range("A1")

     Set MainBook = Workbooks.Open("c:\ExMacro\Marks.xlsx")

     Set MainMarkSheet = MainBook.Worksheets("sheet1")

     studentRollNum = InputBox(Prompt:="Enter Your Roll Number", Title:="Enter Roll Number", Default:="Enter your Roll Number here")

     'RollIndex = MainMarkSheet.Application.WorksheetFunction.Match("11251A1222", Range("A1:A53"), 0)

     RollIndex = MainMarkSheet.Application.WorksheetFunction.Match(studentRollNum, Range("A1:A53"), 0)

     Set studentRowRangeinMainSheet = MainMarkSheet.Range("A" & RollIndex, Range("A" & RollIndex).End(xlToRight))

     MsgBox "Found at row " & RollIndex & " total elements " & studentRowRangeinMainSheet.Count

     rowIndex = 1

     colIndex = 14

     DupMarkcell.Offset(rowIndex, 2) = studentRowRangeinMainSheet.Range("A" & RollIndex)

     For Each MainMarkcell In studentRowRangeinMainSheet

            If rowIndex > 1 Then

            DupMarkcell.Offset(rowIndex, colIndex) = MainMarkcell

            colIndex = colIndex + 1

            End If

            If colIndex > 13 Then

            rowIndex = rowIndex + 1

            colIndex = 10

            End If

            'NewMarkcell.Offset(rowIndex, colIndex) = MainMarkcell

        Next

     MainBook.Close SaveChanges:=False

     'NewBook.Close SaveChanges:=True

     Set DupMarkcell = Nothing

     Set MainMarkcell = Nothing

     'Set NewMarkcell = Nothing

     Set studentRowRangeinMainSheet = Nothing

     End Sub

/* 99 sheets creation*/

Option Explicit

Sub testsheet()

Dim i As Long

Dim testSheetBook As Workbook

Dim testSheetSheet As Worksheet

Set testSheetBook = Workbooks.Add

  With testSheetBook

    .Title = "TestSheet"

    .Subject = "Marks"

    .SaveAs Filename:="ITSheets.xlsx"

   End With

   testSheetBook.Worksheets(1).Name = "1201"

   testSheetBook.Worksheets(2).Name = "1202"

   testSheetBook.Worksheets(3).Name = "1203"

   For i = 4 To 99

     If i < 10 Then

         testSheetBook.Worksheets.Add(After:=testSheetBook.Worksheets(testSheetBook.Worksheets.Count)).Name = "120" & i

     Else

        testSheetBook.Worksheets.Add(After:=testSheetBook.Worksheets(testSheetBook.Worksheets.Count)).Name = "12" & i

    End If

   Next i

   testSheetBook.Close SaveChanges:=True

End Sub

//end of creating 99 sheets