VBA for checking rows and returning cells that match criteria to another sheet

Question

I’m struggling trying to develop an automated solution for the following challenge (ie apart from a button press, no user intervention): I have a master ‘sheet that contains Accrual figures in monthly columns and an associated Receipt Number for this in a column immediately to the right. The Receipt columns have the month in the form: ‘Feb’, ‘Jun’, ‘Dec’ at the top.

I need to return a variety of cell data to another summary ‘sheet in the workbook from each row that matches the following criteria:

1. Identify the Receipt column based on a match of the month with an entered month in cell ‘x’ in the summary ‘sheet, eg if I type ‘Mar’, find the column headed ‘Mar’ in the master ’sheet.
2. Ignoring blank rows (ie there are data in Column A), if the cell in the first row of the Receipt column is blank, identify the row, select 6 different cells and return the contents to 6 specific cells in the first blank row (from a given row number) in the summary ‘sheet – then move onto the next row in the column and continue this process until the end (or a row limit). NB: the 'blank' cell will have a fill colour (conditionally formatted).

If the cell contains data, move to the next row down and continue the process. A VBA routine I can attach to an onscreen button to update the results each time would do the job nicely. Although I've picked up bits of really useful code here, eg for checking each row for given conditions, I can’t get my head around a working solution to include returning the relevant cells to the summary 'sheet...

Any and all feedback greatly appreciated.

(Sorry - can't post images / screenshots yet...)

---

Update Thanks for the quick response, and apols I couldn't append/paste-in a sample of the spreadsheet - apparently I need 10 points... :)

Slightly Different summary sheet (Sheet1). The columns are: Month (Col A), then 4 cells (TBD) to be returned to cols B-E, then Receipt No (Col F). The data come from each identified row in the master sheet (Sheet4) and are placed in the next available blank row in Sheet1. I can mod any examples given to match the actual positions.

I have the criteria for selecting the row (this is from a loop I've got working to identify the row, but using a fixed column reference [26 - Col Z] for a specific month):

If Not Cells(TheRow, 3).Value = "" And Cells(TheRow, 26).Value = "" And Cells(TheRow, 6).Value < Cells(TheRow, 25).Value Then... (do the other bit I'm stuck on)

Hope this is a bit clearer. Appreciate your help.

---

Can't add a comment, or chat (!) so further update:

Thanks Tony The month column in the summary is to confirm the month reported on, as this will change when the user overwrites the source cell for the month they wish to parse the master sheet with. The person this data goes to will get these 7 columns and nothing else. The mechanism has to find the Receipt col (month), then identify that row entry is blank AND col 3 on that row is not blank AND that the Accrual value [Rng.Column - 1] is greater than the PO value (Col 4) on that row. Once these criteria are established, the Receipt, Accrual, PO and 3 other data are selected, copied and pasted into the summary sheet on the next available blank row.

The master sheet has Accrual and Receipt for each month, so there is only one column to identify.

Hope this helps...

Answer 1

I am struggling to visualise you worksheets. I see the master sheet as something like:

 ... |Accrual|  Jan|Accrual|  Feb|Accrual|  Mar|Accrual|  Apr| ...
 ... |   1.23|A12  |       |     |  23.67|A14  |       |     |
 ... |       |     |  56.78|C34  | 178.12|C56  |       |     |
 ... |   2.34|B678 |       |     |       |     | 123.82|B821 |
 ... |       |     |       |     |  96.52|D56  |       |     |

Somewhere within the Summary sheet there is a list of months of interest.

I need a variable:

Dim MonOfInt As String

and a loop within which a month of interest will be loaded from the worksheet into this variable:

With Sheets("Summary")
  MonOfInt = .Cells(R, C).Value
End With

The following is a possible skeleton for the code to move values for the month of interest:

Dim ColCrnt As Long
Dim Rng As Range
Dim RowCrnt As Long
Dim RowLast As Long
  :

With Sheets("Master")
  ' Look for month of interest in row 1
  Set Rng = .Rows(1).Find(MonOfInt)
End With

If Rng Is Nothing Then
  ' No columns for this month in Master

Else
  ' Accrual and Receipt columns for this month found
  ColCrnt = Rng.Column - 1        ' Accrual column for month
  ' Find last used row in Accrual column
  RowLast = .Cells(Rows.Count, ColCrnt).End(xlUp).Row
  For RowCrnt = 2 To RowLast

    ' Code to extract values from Master and move to Summary here

  Next

  End If

End With

If you can add information to your question to confirm my visualisation and to give more information about (1) how to detect is a row is blank (other than testing the whole row which is an option) and (2) the source and destination of the six cells then I will try to pad out this answer.