Want to copy S.No (A1,A2,A3,A4.......) one by one from sheet "filter_data" to sheet "cheque print" , into Cell (Y22) using VBA

Question

I have two Excel sheets with the name Filter data and Cheque Print on the same workbook, In the Filter Data, I have Cheque Serial Number in Column A2, A3, A4, A5...... and in the Cheque Print, Cheque Print format using VLOOKUP.

I want to copy Cheque S.No (A2, A3, A4, A5......) to sheet Cheque Print into Cell Y22 one by one using VBA. Copy Cell A2 data to Y22 and print the Cheque, again Copy Cell A3 data to Y22 and print the Cheque, at last, to find the empty cell.

Answer 1

If I understand correctly, you have a workbook with two (and perhaps more) worksheets. One is named "Filter Data" and one is named "Cheque Print". The Filter Data sheet has data about which checks need to be printed and the Cheque Print sheet is formatted so that when it is printed, it prints a cheque. By placing a Cheque Serial Number from column A on the Filter Data sheet into cell Y22 on Check Print, the Cheque Print sheet will use VLOOKUP to bring in all the necessary data to print a single check.

You want to iterate over the values in column A of Filter Data to place the Serial Numbers, one by one, into Y22 on Cheque Print and print the check.

Because your sheet with the data for cheques is called "Filter Data" I am assuming that you use a filter to hide the rows that you do not want printed.

The following code will take each value in column A on a visible row on the sheet "Filter Data" and place it in Y22 of "Cheque Print", printing all data on Cheque Print before moving to the next serial number.

Sub print_cheques()
        
    Dim row As Long
    Dim filter_data As Worksheet
    Dim cheque_print As Worksheet
    
    Set filter_data = ThisWorkbook.Worksheets("Filter Data")
    Set cheque_print = ThisWorkbook.Worksheets("Cheque Print")
    
    For row = 2 To filter_data.Cells(filter_data.Rows.Count, 1).End(xlUp).row
        If Not filter_data.Rows(row).Hidden And filter_data.Cells(row, 1).Value > "" Then ' only print the check if the row is visible and there is a serial number
            Debug.Print "Now printing cheque # " & filter_data.Cells(row, 1).Value ' display progress in the immeidate window.
            cheque_print.Range("y22").Value = filter_data.Cells(row, 1).Value ' put the serial number on cheque print sheet
            cheque_print.UsedRange.PrintOut ' print the cheque
        End If
        'row = row + 1
        DoEvents ' makes it possible to stop code by pressing ctrl+break
    Next

End Sub