get specific data from selected sheets vba

[vjraja]

the following is the code which i am using for getting specific data from all the sheets in a workbook. but i want to select some sheets only from which i have to get specific data from the selected sheets in the workbook.

tkf.xlsm
	A	B	C	D	E	F	G	H	I
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
14530 to 14583-RMS-TI-880

Dim ws As Worksheet, x As String
    Dim i As Long, lastrow As Long, f As Long
    Dim z As Long
    Application.DisplayAlerts = False
    'Application.ScreenUpdating = False
    
    For Each ws In ThisWorkbook.Worksheets
    If ws.Name = "Summary" Then
      ws.Delete
    End If
Next ws
    
    With ThisWorkbook
        Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
        ws.Name = "Summary"
   
   End With
   
   Dim DestSh As Worksheet
  
   Set DestSh = Worksheets("Summary")
   For Each ws In Worksheets
      lastrow = DestSh.Cells(Rows.Count, "A").End(xlUp).Row
      If ws.Name <> DestSh.Name Then
      
      
      DestSh.Range("A1").Value = "BOS"
     
      DestSh.Range("C1").Value = "FORTIFIED GR A"
      DestSh.Range("D1").Value = "FORTIFIED COMMON"
      DestSh.Range("E1").Value = "RAW RICE GR A"
      DestSh.Range("F1").Value = "RAW RICE COMMON"
      DestSh.Range("G1").Value = "BOILED RICE GRADE A"
      DestSh.Range("H1").Value = "BOILED RICE COMMON"
      DestSh.Range("I1").Value = "AMOUNT"
      'DestSh.Range("J1").Value = "Entry Date"
     
      
      
      DestSh.Range("A" & Rows.Count).End(xlUp).Offset(1).Value = ws.Name
      
      
      If ws.Range("AB12") <> 0 And ws.Range("W12").Value = "FORTIFIED RAW RICE:GR A" Then
      

      
      DestSh.Range("C" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AB12").Value
      
      DestSh.Range("D" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AB13").Value
      DestSh.Range("E" & Rows.Count).End(xlUp).Offset(1).Value = 0
      DestSh.Range("F" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AB13").Value
      DestSh.Range("G" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AB14").Value
      DestSh.Range("H" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AB15").Value
      DestSh.Range("I" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AG16").Value
      'DestSh.Range("J" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("B7").Value
      DestSh.Range("A1" & ":I" & lastrow + 1).Borders.LineStyle = xlContinuous
      
    End If
    
    If ws.Range("AB13") <> 0 And ws.Range("W13").Value = "FORTIFIED RAW RICE:COMMON" Then
      

      
      DestSh.Range("C" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AB12").Value
      
      DestSh.Range("D" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AB13").Value
      DestSh.Range("E" & Rows.Count).End(xlUp).Offset(1).Value = 0
      DestSh.Range("F" & Rows.Count).End(xlUp).Offset(1).Value = 0
      DestSh.Range("G" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AB14").Value
      DestSh.Range("H" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AB15").Value
      DestSh.Range("I" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AG16").Value
      'DestSh.Range("J" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("B7").Value
      DestSh.Range("A1" & ":I" & lastrow + 1).Borders.LineStyle = xlContinuous
      
    End If
    
    
    
     
     If ws.Range("AB12") <> 0 And ws.Range("W12").Value = "RAW RICE:GR A" And ws.Range("AB13") <> 0 And ws.Range("W13").Value = "RAW RICE: COMMON" Then
      
     
      DestSh.Range("C" & Rows.Count).End(xlUp).Offset(1).Value = 0
      DestSh.Range("D" & Rows.Count).End(xlUp).Offset(1).Value = 0
      DestSh.Range("E" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AB12").Value
      DestSh.Range("F" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AB13").Value
      DestSh.Range("G" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AB14").Value
      DestSh.Range("H" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AB15").Value
      DestSh.Range("I" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AG16").Value
      'DestSh.Range("J" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("B7").Value
      DestSh.Range("A1" & ":I" & lastrow + 1).Borders.LineStyle = xlContinuous
      
  
      
       ElseIf ws.Range("AB12") <> 0 And ws.Range("W12").Value = "RAW RICE:GR A" Then
      
      

      DestSh.Range("C" & Rows.Count).End(xlUp).Offset(1).Value = 0
      DestSh.Range("D" & Rows.Count).End(xlUp).Offset(1).Value = 0
      DestSh.Range("E" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AB12").Value
      DestSh.Range("F" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AB13").Value
      DestSh.Range("G" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AB14").Value
      DestSh.Range("H" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AB15").Value
      DestSh.Range("I" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AG16").Value
      'DestSh.Range("J" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("B7").Value
      DestSh.Range("A1" & ":I" & lastrow + 1).Borders.LineStyle = xlContinuous
      
   
        
      
    ElseIf ws.Range("AA13") <> 0 Then
    
    'ws.Range("W13").Copy DestSh.Range("B" & Rows.Count).End(xlUp).Offset(1)
    
      DestSh.Range("C" & Rows.Count).End(xlUp).Offset(1).Value = 0
      DestSh.Range("D" & Rows.Count).End(xlUp).Offset(1).Value = 0
      DestSh.Range("E" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AB12").Value
      DestSh.Range("F" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AB13").Value
      DestSh.Range("G" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AB14").Value
      DestSh.Range("H" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AB15").Value
      DestSh.Range("I" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AG16").Value
      'DestSh.Range("J" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("B7").Value
      DestSh.Range("A1" & ":I" & lastrow + 1).Borders.LineStyle = xlContinuous
    
    
    ElseIf ws.Range("AA14") <> 0 Then
    'ws.Range("W14").Copy DestSh.Range("B" & Rows.Count).End(xlUp).Offset(1)
    
      DestSh.Range("C" & Rows.Count).End(xlUp).Offset(1).Value = 0
      DestSh.Range("D" & Rows.Count).End(xlUp).Offset(1).Value = 0
      DestSh.Range("E" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AB12").Value
      DestSh.Range("F" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AB13").Value
      DestSh.Range("G" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AB14").Value
      DestSh.Range("H" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AB15").Value
      DestSh.Range("I" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AG16").Value
      'DestSh.Range("J" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("B7").Value
      DestSh.Range("A1" & ":I" & lastrow + 1).Borders.LineStyle = xlContinuous
    
    ElseIf ws.Range("AA15") <> 0 Then
    'ws.Range("W15").Copy DestSh.Range("B" & Rows.Count).End(xlUp).Offset(1)
    
      DestSh.Range("C" & Rows.Count).End(xlUp).Offset(1).Value = 0
      DestSh.Range("D" & Rows.Count).End(xlUp).Offset(1).Value = 0
      DestSh.Range("E" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AB12").Value
      DestSh.Range("F" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AB13").Value
      DestSh.Range("G" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AB14").Value
      DestSh.Range("H" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AB15").Value
      DestSh.Range("I" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("AG16").Value
      'DestSh.Range("J" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("B7").Value
      DestSh.Range("A1" & ":I" & lastrow + 1).Borders.LineStyle = xlContinuous
    
    End If
    End If
    
    
    DestSh.Range("A1:I1").EntireColumn.AutoFit
   Next ws
   
   DestSh.Activate
   DestSh.Range("A1:I1").EntireColumn.AutoFit
   lastrow = DestSh.Cells(Rows.Count, "A").End(xlUp).Row
   DestSh.Range("A1" & ":H" & lastrow).Borders.LineStyle = xlContinuous
   
   DestSh.Range("C" & lastrow + 1).Value = WorksheetFunction.Sum(DestSh.Range("C2" & ":C" & lastrow))
   DestSh.Range("D" & lastrow + 1).Value = WorksheetFunction.Sum(DestSh.Range("D2" & ":D" & lastrow))
   DestSh.Range("E" & lastrow + 1).Value = WorksheetFunction.Sum(DestSh.Range("E2" & ":E" & lastrow))
   DestSh.Range("F" & lastrow + 1).Value = WorksheetFunction.Sum(DestSh.Range("F2" & ":F" & lastrow))
   DestSh.Range("G" & lastrow + 1).Value = WorksheetFunction.Sum(DestSh.Range("G2" & ":G" & lastrow))
   DestSh.Range("H" & lastrow + 1).Value = WorksheetFunction.Sum(DestSh.Range("H2" & ":H" & lastrow))
   DestSh.Range("I" & lastrow + 1).Value = WorksheetFunction.Sum(DestSh.Range("I2" & ":I" & lastrow))
   DestSh.Range("I" & lastrow + 1).Value = WorksheetFunction.Sum(DestSh.Range("I2" & ":I" & lastrow))
   DestSh.Range("A" & lastrow + 1).Value = "SUMMARY"

   DestSh.Range("A1" & ":I" & lastrow + 1).Borders.LineStyle = xlContinuous
   
 
   DestSh.Range("C2" & ":H" & lastrow + 1).NumberFormat = "0.00.00"
   DestSh.Range("I2" & ":I" & lastrow + 1).NumberFormat = "0.00"
   
lastrow = DestSh.Cells(Rows.Count, "A").End(xlUp).Row

Worksheets("Summary").Range("B" & lastrow).Value = ""


Worksheets("Summary").Range("B" & lastrow + 1 & ":I" & lastrow + 1).Delete Shift:=xlShiftUp
Worksheets("Summary").Range("I" & lastrow).Value = ""

 Worksheets("Summary").Range("B:B").EntireColumn.Hidden = True
  Worksheets("Summary").Range("M:M").EntireColumn.Hidden = True
  Worksheets("Summary").Range("AB:AB").EntireColumn.Hidden = True


z = 1
Dim Part_text As String
Dim Part2_text As String
Dim Part3_text As String
Dim myrange As Range
DestSh.Activate
lastrow = DestSh.Cells(Rows.Count, "A").End(xlUp).Row
Part_text = "RMS"


Set myrange = Worksheets("Summary").Range("A1" & ":A" & lastrow)

For Each cell In myrange

If InStr(UCase(cell.Value), UCase(Part_text)) <> 0 Then
cell.Interior.ColorIndex = 6
For z = 1 To 7
cell.Offset(0, z).Interior.ColorIndex = 6

Next

End If
Next

      DestSh.Range("L1").Value = "BOS"
      DestSh.Range("N1").Value = "FORTIFIED GR A"
      DestSh.Range("O1").Value = "FORTIFIED COMMON"
      DestSh.Range("P1").Value = "RAW RICE GR A"
      DestSh.Range("Q1").Value = "RAW RICE COMMON"
      DestSh.Range("R1").Value = "BOILED RICE GRADE A"
      DestSh.Range("S1").Value = "BOILED RICE COMMON"
      DestSh.Range("T1").Value = "AMOUNT"
      'DestSh.Range("U1").Value = "Entry Date"
For k = 2 To lastrow

        Part2_text = Worksheets("Summary").Cells(k, 1).Value

        If InStr(1, Part2_text, "KMS", vbTextCompare) > 0 Then

            Worksheets("Summary").Range("A" & k).Copy
            
            Worksheets("Summary").Range("L" & k).PasteSpecial
            
            Worksheets("Summary").Range("C" & k).Copy
            
            Worksheets("Summary").Range("N" & k).PasteSpecial
            
            Worksheets("Summary").Range("D" & k).Copy
            
            Worksheets("Summary").Range("O" & k).PasteSpecial
            
            Worksheets("Summary").Range("E" & k).Copy
            
            Worksheets("Summary").Range("P" & k).PasteSpecial
            
            Worksheets("Summary").Range("F" & k).Copy
            
            Worksheets("Summary").Range("Q" & k).PasteSpecial
            
            Worksheets("Summary").Range("G" & k).Copy
            
            Worksheets("Summary").Range("R" & k).PasteSpecial
            
            Worksheets("Summary").Range("H" & k).Copy
            
            Worksheets("Summary").Range("S" & k).PasteSpecial
            
            Worksheets("Summary").Range("I" & k).Copy
            
            Worksheets("Summary").Range("T" & k).PasteSpecial
            
            Worksheets("Summary").Range("J" & k).Copy
            
            Worksheets("Summary").Range("U" & k).PasteSpecial
            
            Worksheets("Summary").Range("K" & k).Copy
            
            Worksheets("Summary").Range("V" & k).PasteSpecial


Else
 GoTo nextiteration1
    
    
    End If
nextiteration1:
    
    Next k
    
    
    lastrow2 = Worksheets("Summary").Cells(Rows.Count, "L").End(xlUp).Row
            
            DestSh.Range("L1" & ":T" & lastrow + 1).Borders.LineStyle = xlContinuous
            Worksheets("Summary").Range("L1:Z1").EntireColumn.AutoFit
        
   DestSh.Range("N" & lastrow2 + 1).Value = WorksheetFunction.Sum(DestSh.Range("N2" & ":N" & lastrow2))
   DestSh.Range("O" & lastrow2 + 1).Value = WorksheetFunction.Sum(DestSh.Range("O2" & ":O" & lastrow2))
   DestSh.Range("P" & lastrow2 + 1).Value = WorksheetFunction.Sum(DestSh.Range("P2" & ":P" & lastrow2))
   DestSh.Range("Q" & lastrow2 + 1).Value = WorksheetFunction.Sum(DestSh.Range("Q2" & ":Q" & lastrow2))
   DestSh.Range("R" & lastrow2 + 1).Value = WorksheetFunction.Sum(DestSh.Range("R2" & ":R" & lastrow2))
   DestSh.Range("S" & lastrow2 + 1).Value = WorksheetFunction.Sum(DestSh.Range("S2" & ":S" & lastrow2))
   DestSh.Range("T" & lastrow2 + 1).Value = WorksheetFunction.Sum(DestSh.Range("T2" & ":T" & lastrow2))
   DestSh.Range("L" & lastrow2 + 1).Value = "KHARIFF SUMMARY"

   'Worksheets("Summary").Range("L1" & ":T" & lastrow2 + 1).Borders.LineStyle = xlContinuous
   
 
   DestSh.Range("N2" & ":S" & lastrow2 + 1).NumberFormat = "0.00.00"
   DestSh.Range("T2" & ":T" & lastrow2 + 1).NumberFormat = "0.00"
    
  
    
    lastrow2 = Worksheets("Summary").Cells(Rows.Count, "L").End(xlUp).Row
For k = 2 To lastrow2 + 1

    If Worksheets("Summary").Range("L" & k).Value > 0 Then

        GoTo nextiteration2
    
    Else
    
        Worksheets("Summary").Range("L" & k & ":U" & k).Delete Shift:=xlShiftUp
    
            k = k - 1
  
  
    End If
                k = k + 1
                
nextiteration2:

Next
    
    
    For k = 2 To lastrow2
        If Worksheets("Summary").Range("L" & k) = 0 Then
            Worksheets("Summary").Range("L" & k & ":U" & k).Delete Shift:=xlShiftUp
        End If
    Next k
    
  

      DestSh.Range("AA1").Value = "BOS"
      
      DestSh.Range("AC1").Value = "FORTIFIED GR A"
      DestSh.Range("AD1").Value = "FORTIFIED COMMON"
      DestSh.Range("AE1").Value = "RAW RICE GR A"
      DestSh.Range("AF1").Value = "RAW RICE COMMON"
      DestSh.Range("AG1").Value = "BOILED RICE GRADE A"
      DestSh.Range("AH1").Value = "BOILED RICE COMMON"
      DestSh.Range("AI1").Value = "AMOUNT"
      'DestSh.Range("AJ1").Value = "Entry Date"




For k = 2 To lastrow

        Part3_text = Worksheets("Summary").Cells(k, 1).Value

        If InStr(1, Part3_text, "RMS", vbTextCompare) > 0 Then


            Worksheets("Summary").Range("A" & k).Copy
            
            Worksheets("Summary").Range("AA" & k).PasteSpecial
            
            'Worksheets("Summary").Range("B" & k).Copy
            
            'Worksheets("Summary").Range("AB" & k).PasteSpecial
            
            Worksheets("Summary").Range("C" & k).Copy
            
            Worksheets("Summary").Range("AC" & k).PasteSpecial
            
            Worksheets("Summary").Range("D" & k).Copy
            
            Worksheets("Summary").Range("AD" & k).PasteSpecial
            
            Worksheets("Summary").Range("E" & k).Copy
            
            Worksheets("Summary").Range("AE" & k).PasteSpecial
            
            Worksheets("Summary").Range("F" & k).Copy
            
            Worksheets("Summary").Range("AF" & k).PasteSpecial
            
            Worksheets("Summary").Range("G" & k).Copy
            
            Worksheets("Summary").Range("AG" & k).PasteSpecial
            
            Worksheets("Summary").Range("H" & k).Copy
            
            Worksheets("Summary").Range("AH" & k).PasteSpecial
            
            Worksheets("Summary").Range("I" & k).Copy
            
            Worksheets("Summary").Range("AI" & k).PasteSpecial
            
            Worksheets("Summary").Range("J" & k).Copy
            
            Worksheets("Summary").Range("AJ" & k).PasteSpecial

        
            
            
       lastrow3 = DestSh.Cells(Rows.Count, "AA").End(xlUp).Row
            
            DestSh.Range("AA1" & ":AI" & lastrow3 + 1).Borders.LineStyle = xlContinuous
            Worksheets("Summary").Range("AA1:AI1").EntireColumn.AutoFit
        
   

   DestSh.Range("AA1" & ":AI" & lastrow3 + 1).Borders.LineStyle = xlContinuous
   
 
   DestSh.Range("AC2" & ":AH" & lastrow3 + 1).NumberFormat = "0.00.00"
   DestSh.Range("AI2" & ":AI" & lastrow3 + 1).NumberFormat = "0.00"

   
   
   
    
    End If
    

    Next k

 lastrow3 = Worksheets("Summary").Cells(Rows.Count, "AA").End(xlUp).Row
For k = 2 To lastrow3 + 1

    If Worksheets("Summary").Range("AA" & k).Value > 0 Then

        GoTo nextiteration3
    
    ElseIf Worksheets("Summary").Range("AA" & k).Value = 0 Then
    
        Worksheets("Summary").Range("AA" & k & ":AI" & k).Delete Shift:=xlShiftUp
    
            k = k - 1
  
  
    End If
                k = k + 1
                
nextiteration3:

Next




    lastrow3 = Worksheets("Summary").Cells(Rows.Count, "AI").End(xlUp).Row

 For k = 2 To lastrow3
        If Worksheets("Summary").Range("AI" & k) = 0 Then
            Worksheets("Summary").Range("AA" & k & ":AI" & k).Delete Shift:=xlShiftUp
        End If
    Next k
    
For k = 2 To lastrow3
        If Worksheets("Summary").Range("AI" & k) = 0 Then
            Worksheets("Summary").Range("AA" & k & ":AI" & k).Delete Shift:=xlShiftUp
        End If
    Next k
    

lastrow3 = Worksheets("Summary").Cells(Rows.Count, "AA").End(xlUp).Row


    
  Worksheets("Summary").Range("B:B").EntireColumn.Hidden = True
  Worksheets("Summary").Range("M:M").EntireColumn.Hidden = True
  Worksheets("Summary").Range("AB:AB").EntireColumn.Hidden = True
  
  


   DestSh.Range("AC" & lastrow3 + 1).Value = WorksheetFunction.Sum(DestSh.Range("AC2" & ":AC" & lastrow3))
   DestSh.Range("AD" & lastrow3 + 1).Value = WorksheetFunction.Sum(DestSh.Range("AD2" & ":AD" & lastrow3))
   DestSh.Range("AE" & lastrow3 + 1).Value = WorksheetFunction.Sum(DestSh.Range("AE2" & ":AE" & lastrow3))
   DestSh.Range("AF" & lastrow3 + 1).Value = WorksheetFunction.Sum(DestSh.Range("AF2" & ":AF" & lastrow3))
   DestSh.Range("AG" & lastrow3 + 1).Value = WorksheetFunction.Sum(DestSh.Range("AG2" & ":AG" & lastrow3))
   DestSh.Range("AH" & lastrow3 + 1).Value = WorksheetFunction.Sum(DestSh.Range("AH2" & ":AH" & lastrow3))
   DestSh.Range("AI" & lastrow3 + 1).Value = WorksheetFunction.Sum(DestSh.Range("AI2" & ":AI" & lastrow3))
   DestSh.Range("AA" & lastrow3 + 1).Value = "RABI SUMMARY"



DestSh.Range("AC2" & ":AH" & lastrow3 + 1).NumberFormat = "0.00.00"
   DestSh.Range("AI2" & ":AI" & lastrow3 + 1).NumberFormat = "0.00"



lastrow3 = DestSh.Cells(Rows.Count, "AA").End(xlUp).Row

For k = 2 To lastrow3 + 1
        If Worksheets("Summary").Range("AI" & k) = 0 Then
            Worksheets("Summary").Range("AA" & k & ":AI" & k).Delete Shift:=xlShiftUp
        End If
    Next k

lastrow3 = DestSh.Cells(Rows.Count, "AA").End(xlUp).Row



lastrow3 = DestSh.Cells(Rows.Count, "AA").End(xlUp).Row


Worksheets("Summary").Range("AA1" & ":AI" & lastrow3 + 1).Borders.LineStyle = xlContinuous

DestSh.Range("I" & lastrow).Value = WorksheetFunction.Sum(DestSh.Range("I2" & ":I" & lastrow))

 DestSh.Range("A1:AI" & lastrow).WrapText = True
 lastrow = DestSh.Cells(Rows.Count, "A").End(xlUp).Row

    DestSh.PageSetup.PrintTitleRows = ActiveSheet.Rows(1).Address
DestSh.Range("A1:AI" & lastrow).WrapText = True

With ActiveSheet.PageSetup
    
    .FitToPagesWide = False
    
    .Zoom = 100
    .LeftMargin = Application.InchesToPoints(0.75)
    .RightMargin = Application.InchesToPoints(0.19)
    .BottomMargin = Application.InchesToPoints(0.75)
    .TopMargin = Application.InchesToPoints(0.75)

    .PaperSize = xlPaperLegal
    .Orientation = xlLandscape
    
    End With
                
    Worksheets("Summary").Range("A1:J" & lastrow).Select
    ActiveSheet.Range("A1:I" & lastrow).PrintOut Copies:=1, Preview:=True, Collate:=True
    ThisWorkbook.Save
    
 
    
    DestSh.Range("A1:AH" & lastrow).WrapText = True
 lastrow2 = DestSh.Cells(Rows.Count, "L").End(xlUp).Row

    DestSh.PageSetup.PrintTitleRows = ActiveSheet.Rows(1).Address
DestSh.Range("L1:T" & lastrow2).WrapText = True

With ActiveSheet.PageSetup
    
    .FitToPagesWide = False
    
    .Zoom = 100
    .LeftMargin = Application.InchesToPoints(0.75)
    .RightMargin = Application.InchesToPoints(0.19)
    .BottomMargin = Application.InchesToPoints(0.75)
    .TopMargin = Application.InchesToPoints(0.75)

    .PaperSize = xlPaperLegal
    .Orientation = xlLandscape
    
    End With
                
    DestSh.Range("L1:T" & lastrow2).Select
    ActiveSheet.Range("L1:T" & lastrow2).PrintOut Copies:=1, Preview:=True, Collate:=True
    ThisWorkbook.Save
    
 
    
     DestSh.Range("A1:AI" & lastrow).WrapText = True
 lastrow3 = DestSh.Cells(Rows.Count, "AA").End(xlUp).Row

    DestSh.PageSetup.PrintTitleRows = ActiveSheet.Rows(1).Address
DestSh.Range("AA1:AI" & lastrow3).WrapText = True

With ActiveSheet.PageSetup
    
    .FitToPagesWide = False
    
    .Zoom = 100
    .LeftMargin = Application.InchesToPoints(0.75)
    .RightMargin = Application.InchesToPoints(0.19)
    .BottomMargin = Application.InchesToPoints(0.75)
    .TopMargin = Application.InchesToPoints(0.75)

    .PaperSize = xlPaperLegal
    .Orientation = xlLandscape
    
    End With
                
    DestSh.Range("AA1:AI" & lastrow3).Select
    ActiveSheet.Range("AA1:AI" & lastrow3).PrintOut Copies:=1, Preview:=True, Collate:=True
    ThisWorkbook.Save

 

[vjraja]

tkf.xlsm
	A	B	C	D	E	F	G	H	I	J					O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
4
5
6	Bill No.	Bill Date	Mill Code	Name of the Mill	From	Ack No.	Date	Acceptance No.	Acceptance Date	GODOWN					BAGS	Gr-A (QTLS)	BAGS	Common (QTLS)	Amount	MOISTURE	CONSIGNMENT NUMBER	VARIETY
7	14584	09-08-2021	SB14-267	VENKATESWARA PAR BOILED MODERN RICE MILL THOTACHERLA	THOTACHERLA	1255	26-06-2021	1294	26-06-2021	SWC JAGGAYYAPETA					580	290.00.00			905261.10	13.15	1
8	14585	09-08-2021	SB14-267	VENKATESWARA PAR BOILED MODERN RICE MILL THOTACHERLA	THOTACHERLA	1279	30-06-2021	1297	30-06-2021	SWC JAGGAYYAPETA					580	290.00.00			905261.10	13.55	12
9	14586	09-08-2021	SB14-267	VENKATESWARA PAR BOILED MODERN RICE MILL THOTACHERLA	THOTACHERLA	1286	03-07-2021	3611	03-07-2021	SWC JAGGAYYAPETA					580	290.00.00			905261.10	14.95	15
10	14587	09-08-2021	SB14-267	VENKATESWARA PAR BOILED MODERN RICE MILL THOTACHERLA	THOTACHERLA	1294	13-07-2021	3612	13-07-2021	SWC JAGGAYYAPETA					580	290.00.00			905261.10	14.45	18
11	14588	09-08-2021	SB14-267	VENKATESWARA PAR BOILED MODERN RICE MILL THOTACHERLA	THOTACHERLA	1296	16-07-2021	3615	16-07-2021	SWC JAGGAYYAPETA					580	289.50.00			903700.31	14.55	19		DESCRIPTION				No.of Bags	Qty (in Qtls)	UNIT PRICE				AMOUNT
12	14589	09-08-2021	SB14-267	VENKATESWARA PAR BOILED MODERN RICE MILL THOTACHERLA	THOTACHERLA	1297	16-07-2021	3617	16-07-2021	SWC JAGGAYYAPETA					580	290.00.00			905261.10	14.85	20		RAW RICE:GR A				0	000.00.00	3179.90				0.00
13	14590	09-08-2021	SB14-267	VENKATESWARA PAR BOILED MODERN RICE MILL THOTACHERLA	THOTACHERLA	1301	17-07-2021	3616	17-07-2021	SWC JAGGAYYAPETA					580	289.70.00			904324.62	14.15	21		RAW RICE:COMMON				0	000.00.00	3147.15				0.00
14	14591	09-08-2021	SB14-267	VENKATESWARA PAR BOILED MODERN RICE MILL THOTACHERLA	THOTACHERLA	1303	19-07-2021	3614	19-07-2021	SWC JAGGAYYAPETA					580	289.70.00			904324.62	14.95	24		BOILED RICE : Gr.A				31320	15644.88.00	3121.59				48836900.96
15	14592	09-08-2021	SB14-267	VENKATESWARA PAR BOILED MODERN RICE MILL THOTACHERLA	THOTACHERLA	1304	19-07-2021	3504	19-07-2021	SWC JAGGAYYAPETA					580	289.50.00			903700.31	14.95	23		BOILED RICE : Common				0	000.00.00	3089.61				0.00
16	14593	09-08-2021	SB14-267	VENKATESWARA PAR BOILED MODERN RICE MILL THOTACHERLA	THOTACHERLA	1306	19-07-2021	3613	19-07-2021	SWC JAGGAYYAPETA					580	289.60.00			904012.46	14.85	25		Total				31320	15644.88.00	AMOUNT				48836900.96
17	14540	09-08-2021	SB14-395	RAJYALAKSHMI RAW AND PAR B R M RAMANAPUDI	RAMANAPUDI	4822	23-07-2021	4822	23-07-2021	BSC GUDIVADA					580	290.03.00			905354.75	13.60	42
18	14541	09-08-2021	SB14-395	RAJYALAKSHMI RAW AND PAR B R M RAMANAPUDI	RAMANAPUDI	4872	31-07-2021	4872	31-07-2021	BSC GUDIVADA					580	290.03.00			905354.75	14.00	50
19	14542	09-08-2021	SB14-395	RAJYALAKSHMI RAW AND PAR B R M RAMANAPUDI	RAMANAPUDI	4858	28-07-2021	4858	28-07-2021	BSC GUDIVADA					580	289.73.00			904418.27	14.00	48
20	14543	09-08-2021	SB14-388	PV AND CO.CON SREE RAMA R AND GNOM GUDIVADA	GUDIVADA	4713	07-07-2021	4713	07-07-2021	BSC GUDIVADA					580	289.48.00			903637.87	14.00	112
21	14544	09-08-2021	SB14-388	PV AND CO.CON SREE RAMA R AND GNOM GUDIVADA	GUDIVADA	4718	08-07-2021	4718	07-07-2021	BSC GUDIVADA					580	289.73.00			904418.27	13.80	113
22	14545	09-08-2021	SB14-388	PV AND CO.CON SREE RAMA R AND GNOM GUDIVADA	GUDIVADA	4721	08-07-2021	4721	07-07-2021	BSC GUDIVADA					580	289.43.00			903481.79	13.70	114
23	14546	09-08-2021	SB14-388	PV AND CO.CON SREE RAMA R AND GNOM GUDIVADA	GUDIVADA	4763	14-07-2021	4763	14-07-2021	BSC GUDIVADA					580	290.03.00			905354.75	13.80	124
24	14547	09-08-2021	SB14-388	PV AND CO.CON SREE RAMA R AND GNOM GUDIVADA	GUDIVADA	4766	14-07-2021	4766	14-07-2021	BSC GUDIVADA					580	290.03.00			905354.75	13.80	125
25	14548	09-08-2021	SB14-388	PV AND CO.CON SREE RAMA R AND GNOM GUDIVADA	GUDIVADA	4795	19-07-2021	4795	19-07-2021	BSC GUDIVADA					580	289.53.00			903793.95	14.00	126
26	14549	09-08-2021	SB14-912	VEERAIAH PARA BOILED RICE MILL GUDIVADA	GUDIVADA	4683	03-07-2021	4683	03-07-2021	BSC GUDIVADA					580	289.58.00			903950.03	13.90	1
27	14550	09-08-2021	SB14-912	VEERAIAH PARA BOILED RICE MILL GUDIVADA	GUDIVADA	4693	05-07-2021	4693	05-07-2021	BSC GUDIVADA					580	289.53.00			903793.95	13.40	102
28	14551	09-08-2021	SB14-912	VEERAIAH PARA BOILED RICE MILL GUDIVADA	GUDIVADA	4774	15-07-2021	4774	15-07-2021	BSC GUDIVADA					580	289.23.00			902857.48	13.80	108
29	14552	09-08-2021	SB14-912	VEERAIAH PARA BOILED RICE MILL GUDIVADA	GUDIVADA	4775	15-07-2021	4775	15-07-2021	BSC GUDIVADA					580	290.03.00			905354.75	13.80	109
30	14553	09-08-2021	SB14-912	VEERAIAH PARA BOILED RICE MILL GUDIVADA	GUDIVADA	4778	16-07-2021	4778	15-07-2021	BSC GUDIVADA					580	289.80.00			904636.78	13.70	110
31	14554	09-08-2021	SB14-912	VEERAIAH PARA BOILED RICE MILL GUDIVADA	GUDIVADA	4779	16-07-2021	4779	16-07-2021	BSC GUDIVADA					580	290.03.00			905354.75	13.50	111
32	14555	09-08-2021	SB14-912	VEERAIAH PARA BOILED RICE MILL GUDIVADA	GUDIVADA	4782	16-07-2021	4782	16-07-2021	BSC GUDIVADA					580	290.03.00			905354.75	13.90	112
33	14556	09-08-2021	SB14-912	VEERAIAH PARA BOILED RICE MILL GUDIVADA	GUDIVADA	4785	16-07-2021	4785	16-07-2021	BSC GUDIVADA					580	290.03.00			905354.75	13.70	113
34	14557	09-08-2021	SB14-912	VEERAIAH PARA BOILED RICE MILL GUDIVADA	GUDIVADA	4786	16-07-2021	4786	16-07-2021	BSC GUDIVADA					580	290.03.00			905354.75	13.80	114
35	14558	09-08-2021	SB14-912	VEERAIAH PARA BOILED RICE MILL GUDIVADA	GUDIVADA	4787	17-07-2021	4787	17-07-2021	BSC GUDIVADA					580	290.03.00			905354.75	13.80	115
36	14559	09-08-2021	SB14-912	VEERAIAH PARA BOILED RICE MILL GUDIVADA	GUDIVADA	4788	17-07-2021	4788	17-07-2021	BSC GUDIVADA					580	290.03.00			905354.75	13.80	116
37	14560	09-08-2021	SB14-912	VEERAIAH PARA BOILED RICE MILL GUDIVADA	GUDIVADA	4793	17-07-2021	4793	17-07-2021	BSC GUDIVADA					580	289.58.00			903950.03	13.90	117
38	14561	09-08-2021	SB14-912	VEERAIAH PARA BOILED RICE MILL GUDIVADA	GUDIVADA	4803	19-07-2021	4803	19-07-2021	BSC GUDIVADA					580	289.23.00			902857.48	14.30	118
39	14562	09-08-2021	SB14-912	VEERAIAH PARA BOILED RICE MILL GUDIVADA	GUDIVADA	4804	19-07-2021	4804	19-07-2021	BSC GUDIVADA					580	289.28.00			903013.56	13.80	119
40	14563	09-08-2021	SB14-912	VEERAIAH PARA BOILED RICE MILL GUDIVADA	GUDIVADA	4806	20-07-2021	4806	20-07-2021	BSC GUDIVADA					580	289.43.00			903481.79	14.00	120
41	14564	09-08-2021	SB14-912	VEERAIAH PARA BOILED RICE MILL GUDIVADA	GUDIVADA	4809	20-07-2021	4809	20-07-2021	BSC GUDIVADA					580	289.43.00			903481.79	14.00	121
42	14565	09-08-2021	SB14-912	VEERAIAH PARA BOILED RICE MILL GUDIVADA	GUDIVADA	4812	20-07-2021	4812	20-07-2021	BSC GUDIVADA					580	289.63.00			904106.11	14.00	122
43	14566	09-08-2021	SB14-912	VEERAIAH PARA BOILED RICE MILL GUDIVADA	GUDIVADA	4815	20-07-2021	4815	20-07-2021	BSC GUDIVADA					580	289.43.00			903481.79	13.90	123
44	14567	09-08-2021	SB14-912	VEERAIAH PARA BOILED RICE MILL GUDIVADA	GUDIVADA	4818	22-07-2021	4818	22-07-2021	BSC GUDIVADA					580	289.73.00			904418.27	14.00	124
45	14568	09-08-2021	SB14-912	VEERAIAH PARA BOILED RICE MILL GUDIVADA	GUDIVADA	4820	23-07-2021	4820	23-07-2021	BSC GUDIVADA					580	289.80.00			904636.78	14.00	125
46	14569	09-08-2021	SB14-912	VEERAIAH PARA BOILED RICE MILL GUDIVADA	GUDIVADA	4819	22-07-2021	4819	22-07-2021	BSC GUDIVADA					580	289.83.00			904730.43	14.00	126
47	14570	09-08-2021	SB14-912	VEERAIAH PARA BOILED RICE MILL GUDIVADA	GUDIVADA	4826	23-07-2021	4826	23-07-2021	BSC GUDIVADA					580	290.03.00			905354.75	13.90	127
48	14571	09-08-2021	SB14-912	VEERAIAH PARA BOILED RICE MILL GUDIVADA	GUDIVADA	4825	23-07-2021	4825	23-07-2021	BSC GUDIVADA					580	290.03.00			905354.75	13.70	128
49	14572	09-08-2021	SB14-691	SRI VIJAYALAKSHMI MODERN RICE MILL POLUKONDA	POLUKONDA	4839	24-07-2021	4839	24-07-2021	BSC GUDIVADA					580	289.73.00			904418.27	14.60	57
50	14573	09-08-2021	SB14-691	SRI VIJAYALAKSHMI MODERN RICE MILL POLUKONDA	POLUKONDA	4845	27-07-2021	4845	27-07-2021	BSC GUDIVADA					580	289.73.00			904418.27	14.00	65
51	14574	09-08-2021	SB14-691	SRI VIJAYALAKSHMI MODERN RICE MILL POLUKONDA	POLUKONDA	4854	27-07-2021	4854	27-07-2021	BSC GUDIVADA					580	289.53.00			903793.95	14.20	66
52	14575	09-08-2021	SB14-691	SRI VIJAYALAKSHMI MODERN RICE MILL POLUKONDA	POLUKONDA	4844	27-07-2021	4844	27-07-2021	BSC GUDIVADA					580	289.53.00			903793.95	14.10	67
53	14576	09-08-2021	SB14-691	SRI VIJAYALAKSHMI MODERN RICE MILL POLUKONDA	POLUKONDA	4857	28-07-2021	4857	28-07-2021	BSC GUDIVADA					580	289.73.00			904418.27	14.50	70
54	14577	09-08-2021	SB14-691	SRI VIJAYALAKSHMI MODERN RICE MILL POLUKONDA	POLUKONDA	4873	31-07-2021	4873	31-07-2021	BSC GUDIVADA					580	289.63.00			904106.11	14.30	77
55	14578	09-08-2021	SB14-691	SRI VIJAYALAKSHMI MODERN RICE MILL POLUKONDA	POLUKONDA	4777	16-07-2021	4777	15-07-2021	BSC GUDIVADA					580	289.80.00			904636.78	14.90	36
56	14579	09-08-2021	SB14-691	SRI VIJAYALAKSHMI MODERN RICE MILL POLUKONDA	POLUKONDA	4814	20-07-2021	4814	20-07-2021	BSC GUDIVADA					580	289.48.00			903637.87	13.80	52
57	14580	09-08-2021	SB14-691	SRI VIJAYALAKSHMI MODERN RICE MILL POLUKONDA	POLUKONDA	4823	23-07-2021	4823	23-07-2021	BSC GUDIVADA					580	289.43.00			903481.79	14.60	55
58	14581	09-08-2021	SB14-691	SRI VIJAYALAKSHMI MODERN RICE MILL POLUKONDA	POLUKONDA	4808	20-07-2021	4808	20-07-2021	BSC GUDIVADA					580	289.53.00			903793.95	14.00	50
59	14582	09-08-2021	SB14-691	SRI VIJAYALAKSHMI MODERN RICE MILL POLUKONDA	POLUKONDA	4829	23-07-2021	4829	23-07-2021	BSC GUDIVADA					580	289.53.00			903793.95	14.50	56
60	14583	09-08-2021	SB14-691	SRI VIJAYALAKSHMI MODERN RICE MILL POLUKONDA	POLUKONDA	4833	24-07-2021	4833	24-07-2021	BSC GUDIVADA					580	289.43.00			903481.79	14.60	58
61															31320	15644.88.00	0	000.00.00	48836900.96
62
63
14530 to 14583-RMS-TI-880

Cell Formulas
Range	Formula
W12	W12	=IF(K3="FORTIFIED RAW RICE:GR A", "FORTIFIED RAW RICE:GR A","RAW RICE:GR A")
W13	W13	=IF(K3="FORTIFIED RAW RICE:COMMON", "FORTIFIED RAW RICE:COMMON","RAW RICE:COMMON")
AC12	AC12	=IF(K3="FORTIFIED RAW RICE:GR A",(3179.9+73),3179.9)
AC13	AC13	=IF(K3="FORTIFIED RAW RICE:COMMON",(3147.15+73),3147.15)
AA16	AA16	=AA12+AA13+AA14+AA15
AB16	AB16	=AB15+AB14+AB13+AB12
S7:S60	S7	=((IF(AND((V7="FORTIFIED RAW RICE GRADE-A"),(L7>0)),L7*(3179.9+73),L7*3179.9))+(IF(AND(V7="FORTIFIED RAW RICE:COMMON", N7>0),N7*(3147.15+73),N7*3147.15))+P7*3121.59+R7*3089.61)

the above is the sample sheet i am using which converted to xl2bb as is suggested by the authorities in the forum.