get specific data from selected sheets vba

vjraja

New Member
Joined
Aug 4, 2021
Messages
9
Office Version
  1. 2007
Platform
  1. Windows
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
ABCDEFGHI
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
14530 to 14583-RMS-TI-880



VBA Code:
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

vjraja

New Member
Joined
Aug 4, 2021
Messages
9
Office Version
  1. 2007
Platform
  1. Windows
tkf.xlsm
ABCDEFGHIJOPQRSTUVWXYZAAABACADAEAFAG
4
5
6Bill No.Bill DateMill CodeName of the MillFromAck No.DateAcceptance No.Acceptance DateGODOWNBAGSGr-A (QTLS)BAGSCommon (QTLS)AmountMOISTURECONSIGNMENT NUMBERVARIETY
71458409-08-2021SB14-267VENKATESWARA PAR BOILED MODERN RICE MILL THOTACHERLATHOTACHERLA125526-06-2021129426-06-2021SWC JAGGAYYAPETA580290.00.00905261.1013.151
81458509-08-2021SB14-267VENKATESWARA PAR BOILED MODERN RICE MILL THOTACHERLATHOTACHERLA127930-06-2021129730-06-2021SWC JAGGAYYAPETA580290.00.00905261.1013.5512
91458609-08-2021SB14-267VENKATESWARA PAR BOILED MODERN RICE MILL THOTACHERLATHOTACHERLA128603-07-2021361103-07-2021SWC JAGGAYYAPETA580290.00.00905261.1014.9515
101458709-08-2021SB14-267VENKATESWARA PAR BOILED MODERN RICE MILL THOTACHERLATHOTACHERLA129413-07-2021361213-07-2021SWC JAGGAYYAPETA580290.00.00905261.1014.4518
111458809-08-2021SB14-267VENKATESWARA PAR BOILED MODERN RICE MILL THOTACHERLATHOTACHERLA129616-07-2021361516-07-2021SWC JAGGAYYAPETA580289.50.00903700.3114.5519DESCRIPTIONNo.of BagsQty (in Qtls)UNIT PRICEAMOUNT
121458909-08-2021SB14-267VENKATESWARA PAR BOILED MODERN RICE MILL THOTACHERLATHOTACHERLA129716-07-2021361716-07-2021SWC JAGGAYYAPETA580290.00.00905261.1014.8520RAW RICE:GR A0000.00.003179.900.00
131459009-08-2021SB14-267VENKATESWARA PAR BOILED MODERN RICE MILL THOTACHERLATHOTACHERLA130117-07-2021361617-07-2021SWC JAGGAYYAPETA580289.70.00904324.6214.1521RAW RICE:COMMON0000.00.003147.150.00
141459109-08-2021SB14-267VENKATESWARA PAR BOILED MODERN RICE MILL THOTACHERLATHOTACHERLA130319-07-2021361419-07-2021SWC JAGGAYYAPETA580289.70.00904324.6214.9524BOILED RICE : Gr.A3132015644.88.003121.5948836900.96
151459209-08-2021SB14-267VENKATESWARA PAR BOILED MODERN RICE MILL THOTACHERLATHOTACHERLA130419-07-2021350419-07-2021SWC JAGGAYYAPETA580289.50.00903700.3114.9523BOILED RICE : Common0000.00.003089.610.00
161459309-08-2021SB14-267VENKATESWARA PAR BOILED MODERN RICE MILL THOTACHERLATHOTACHERLA130619-07-2021361319-07-2021SWC JAGGAYYAPETA580289.60.00904012.4614.8525Total3132015644.88.00AMOUNT48836900.96
171454009-08-2021SB14-395RAJYALAKSHMI RAW AND PAR B R M RAMANAPUDIRAMANAPUDI482223-07-2021482223-07-2021BSC GUDIVADA580290.03.00905354.7513.6042
181454109-08-2021SB14-395RAJYALAKSHMI RAW AND PAR B R M RAMANAPUDIRAMANAPUDI487231-07-2021487231-07-2021BSC GUDIVADA580290.03.00905354.7514.0050
191454209-08-2021SB14-395RAJYALAKSHMI RAW AND PAR B R M RAMANAPUDIRAMANAPUDI485828-07-2021485828-07-2021BSC GUDIVADA580289.73.00904418.2714.0048
201454309-08-2021SB14-388PV AND CO.CON SREE RAMA R AND GNOM GUDIVADAGUDIVADA471307-07-2021471307-07-2021BSC GUDIVADA580289.48.00903637.8714.00112
211454409-08-2021SB14-388PV AND CO.CON SREE RAMA R AND GNOM GUDIVADAGUDIVADA471808-07-2021471807-07-2021BSC GUDIVADA580289.73.00904418.2713.80113
221454509-08-2021SB14-388PV AND CO.CON SREE RAMA R AND GNOM GUDIVADAGUDIVADA472108-07-2021472107-07-2021BSC GUDIVADA580289.43.00903481.7913.70114
231454609-08-2021SB14-388PV AND CO.CON SREE RAMA R AND GNOM GUDIVADAGUDIVADA476314-07-2021476314-07-2021BSC GUDIVADA580290.03.00905354.7513.80124
241454709-08-2021SB14-388PV AND CO.CON SREE RAMA R AND GNOM GUDIVADAGUDIVADA476614-07-2021476614-07-2021BSC GUDIVADA580290.03.00905354.7513.80125
251454809-08-2021SB14-388PV AND CO.CON SREE RAMA R AND GNOM GUDIVADAGUDIVADA479519-07-2021479519-07-2021BSC GUDIVADA580289.53.00903793.9514.00126
261454909-08-2021SB14-912VEERAIAH PARA BOILED RICE MILL GUDIVADAGUDIVADA468303-07-2021468303-07-2021BSC GUDIVADA580289.58.00903950.0313.901
271455009-08-2021SB14-912VEERAIAH PARA BOILED RICE MILL GUDIVADAGUDIVADA469305-07-2021469305-07-2021BSC GUDIVADA580289.53.00903793.9513.40102
281455109-08-2021SB14-912VEERAIAH PARA BOILED RICE MILL GUDIVADAGUDIVADA477415-07-2021477415-07-2021BSC GUDIVADA580289.23.00902857.4813.80108
291455209-08-2021SB14-912VEERAIAH PARA BOILED RICE MILL GUDIVADAGUDIVADA477515-07-2021477515-07-2021BSC GUDIVADA580290.03.00905354.7513.80109
301455309-08-2021SB14-912VEERAIAH PARA BOILED RICE MILL GUDIVADAGUDIVADA477816-07-2021477815-07-2021BSC GUDIVADA580289.80.00904636.7813.70110
311455409-08-2021SB14-912VEERAIAH PARA BOILED RICE MILL GUDIVADAGUDIVADA477916-07-2021477916-07-2021BSC GUDIVADA580290.03.00905354.7513.50111
321455509-08-2021SB14-912VEERAIAH PARA BOILED RICE MILL GUDIVADAGUDIVADA478216-07-2021478216-07-2021BSC GUDIVADA580290.03.00905354.7513.90112
331455609-08-2021SB14-912VEERAIAH PARA BOILED RICE MILL GUDIVADAGUDIVADA478516-07-2021478516-07-2021BSC GUDIVADA580290.03.00905354.7513.70113
341455709-08-2021SB14-912VEERAIAH PARA BOILED RICE MILL GUDIVADAGUDIVADA478616-07-2021478616-07-2021BSC GUDIVADA580290.03.00905354.7513.80114
351455809-08-2021SB14-912VEERAIAH PARA BOILED RICE MILL GUDIVADAGUDIVADA478717-07-2021478717-07-2021BSC GUDIVADA580290.03.00905354.7513.80115
361455909-08-2021SB14-912VEERAIAH PARA BOILED RICE MILL GUDIVADAGUDIVADA478817-07-2021478817-07-2021BSC GUDIVADA580290.03.00905354.7513.80116
371456009-08-2021SB14-912VEERAIAH PARA BOILED RICE MILL GUDIVADAGUDIVADA479317-07-2021479317-07-2021BSC GUDIVADA580289.58.00903950.0313.90117
381456109-08-2021SB14-912VEERAIAH PARA BOILED RICE MILL GUDIVADAGUDIVADA480319-07-2021480319-07-2021BSC GUDIVADA580289.23.00902857.4814.30118
391456209-08-2021SB14-912VEERAIAH PARA BOILED RICE MILL GUDIVADAGUDIVADA480419-07-2021480419-07-2021BSC GUDIVADA580289.28.00903013.5613.80119
401456309-08-2021SB14-912VEERAIAH PARA BOILED RICE MILL GUDIVADAGUDIVADA480620-07-2021480620-07-2021BSC GUDIVADA580289.43.00903481.7914.00120
411456409-08-2021SB14-912VEERAIAH PARA BOILED RICE MILL GUDIVADAGUDIVADA480920-07-2021480920-07-2021BSC GUDIVADA580289.43.00903481.7914.00121
421456509-08-2021SB14-912VEERAIAH PARA BOILED RICE MILL GUDIVADAGUDIVADA481220-07-2021481220-07-2021BSC GUDIVADA580289.63.00904106.1114.00122
431456609-08-2021SB14-912VEERAIAH PARA BOILED RICE MILL GUDIVADAGUDIVADA481520-07-2021481520-07-2021BSC GUDIVADA580289.43.00903481.7913.90123
441456709-08-2021SB14-912VEERAIAH PARA BOILED RICE MILL GUDIVADAGUDIVADA481822-07-2021481822-07-2021BSC GUDIVADA580289.73.00904418.2714.00124
451456809-08-2021SB14-912VEERAIAH PARA BOILED RICE MILL GUDIVADAGUDIVADA482023-07-2021482023-07-2021BSC GUDIVADA580289.80.00904636.7814.00125
461456909-08-2021SB14-912VEERAIAH PARA BOILED RICE MILL GUDIVADAGUDIVADA481922-07-2021481922-07-2021BSC GUDIVADA580289.83.00904730.4314.00126
471457009-08-2021SB14-912VEERAIAH PARA BOILED RICE MILL GUDIVADAGUDIVADA482623-07-2021482623-07-2021BSC GUDIVADA580290.03.00905354.7513.90127
481457109-08-2021SB14-912VEERAIAH PARA BOILED RICE MILL GUDIVADAGUDIVADA482523-07-2021482523-07-2021BSC GUDIVADA580290.03.00905354.7513.70128
491457209-08-2021SB14-691SRI VIJAYALAKSHMI MODERN RICE MILL POLUKONDAPOLUKONDA483924-07-2021483924-07-2021BSC GUDIVADA580289.73.00904418.2714.6057
501457309-08-2021SB14-691SRI VIJAYALAKSHMI MODERN RICE MILL POLUKONDAPOLUKONDA484527-07-2021484527-07-2021BSC GUDIVADA580289.73.00904418.2714.0065
511457409-08-2021SB14-691SRI VIJAYALAKSHMI MODERN RICE MILL POLUKONDAPOLUKONDA485427-07-2021485427-07-2021BSC GUDIVADA580289.53.00903793.9514.2066
521457509-08-2021SB14-691SRI VIJAYALAKSHMI MODERN RICE MILL POLUKONDAPOLUKONDA484427-07-2021484427-07-2021BSC GUDIVADA580289.53.00903793.9514.1067
531457609-08-2021SB14-691SRI VIJAYALAKSHMI MODERN RICE MILL POLUKONDAPOLUKONDA485728-07-2021485728-07-2021BSC GUDIVADA580289.73.00904418.2714.5070
541457709-08-2021SB14-691SRI VIJAYALAKSHMI MODERN RICE MILL POLUKONDAPOLUKONDA487331-07-2021487331-07-2021BSC GUDIVADA580289.63.00904106.1114.3077
551457809-08-2021SB14-691SRI VIJAYALAKSHMI MODERN RICE MILL POLUKONDAPOLUKONDA477716-07-2021477715-07-2021BSC GUDIVADA580289.80.00904636.7814.9036
561457909-08-2021SB14-691SRI VIJAYALAKSHMI MODERN RICE MILL POLUKONDAPOLUKONDA481420-07-2021481420-07-2021BSC GUDIVADA580289.48.00903637.8713.8052
571458009-08-2021SB14-691SRI VIJAYALAKSHMI MODERN RICE MILL POLUKONDAPOLUKONDA482323-07-2021482323-07-2021BSC GUDIVADA580289.43.00903481.7914.6055
581458109-08-2021SB14-691SRI VIJAYALAKSHMI MODERN RICE MILL POLUKONDAPOLUKONDA480820-07-2021480820-07-2021BSC GUDIVADA580289.53.00903793.9514.0050
591458209-08-2021SB14-691SRI VIJAYALAKSHMI MODERN RICE MILL POLUKONDAPOLUKONDA482923-07-2021482923-07-2021BSC GUDIVADA580289.53.00903793.9514.5056
601458309-08-2021SB14-691SRI VIJAYALAKSHMI MODERN RICE MILL POLUKONDAPOLUKONDA483324-07-2021483324-07-2021BSC GUDIVADA580289.43.00903481.7914.6058
613132015644.88.000000.00.0048836900.96
62
63
14530 to 14583-RMS-TI-880
Cell Formulas
RangeFormula
W12W12=IF(K3="FORTIFIED RAW RICE:GR A", "FORTIFIED RAW RICE:GR A","RAW RICE:GR A")
W13W13=IF(K3="FORTIFIED RAW RICE:COMMON", "FORTIFIED RAW RICE:COMMON","RAW RICE:COMMON")
AC12AC12=IF(K3="FORTIFIED RAW RICE:GR A",(3179.9+73),3179.9)
AC13AC13=IF(K3="FORTIFIED RAW RICE:COMMON",(3147.15+73),3147.15)
AA16AA16=AA12+AA13+AA14+AA15
AB16AB16=AB15+AB14+AB13+AB12
S7:S60S7=((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.
 

Forum statistics

Threads
1,147,635
Messages
5,742,245
Members
423,717
Latest member
rubthenut

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top