Copy multiple cells in one row based on multiple criteria

Question

Reference Information. I have an Excel file used to track payables. There are 18 data columns (A to R). Of these 18 columns, I want to use a macro to filter for a specific operator date, and then for a specific company code.

Each corporate code will be assigned a new worksheet. In each of these worksheets, I want to use specific cells from the main worksheet based on the criteria. For example, a macro must first sort the transaction date (7/31/2012) and then the company code (ABC). Then I need to run a loop to get the details. For example, on the main sheet, the GL code in column P should be copied to the sheet "ABC" in column H.

Here is a summary of what should happen:
1. Clear any filters in the filter range (A2: R2)
2. Filter the date in cell A1 on the Master sheet, starting from cell A3 (date column)
3. Filter for company code (ABC) in column O

This should provide a data set for the specific activities of the company. Here's what should happen next:
4. Copy the values ​​of the cell column P in the "main" desktop in column C on the sheet "ABC"
5. Copy the values ​​of the column N of the cell in the "main" desktop to column D on the sheet "ABC"
6. Copy the values ​​of the cell R of the column in the "main" desktop to column H on the sheet "ABC"
7. Copy the values ​​of the cell F of the column in the "main" desktop to column G on the sheet "ABC", but not more than 30 characters
8. If the value of column G in the sheet "master" is> = 0, then copy this value to column E on sheet "ABC" (otherwise it should be zero)
9. If the value of column G in the sheet "master" is <0, then copy this value to column F on sheet "ABC" (otherwise it should be zero)

Is it possible?

Answer 1

Here is the sub-item that should launch you. I have not completed all your steps, but I believe that this is enough to take and finish on my own. If you find this answer is useful for you when you need to go, accept this answer. If you have problems with anything here, add a comment to this answer asking for clarification.

I tested only dummy data, but what I worked with was successful.

Option Explicit Sub TransferData() Dim Master As Worksheet Dim NewSheet As Worksheet Dim CompanyList As Object Dim lRow As Long, lMaxRow As Long, lNewRow As Long Dim vDictItem As Variant Set CompanyList = CreateObject("Scripting.Dictionary") Set Master = ThisWorkbook.Sheets("Master") If Master.FilterMode Then Master.ShowAllData End If Master.Range("A:R").Sort Master.Range("A2"), xlAscending, Master.Range("O2"), , xlAscending, , , xlYes lMaxRow = Master.Range("A" & Master.Rows.Count).End(xlUp).Row For lRow = 3 To lMaxRow If Not CompanyList.Exists(Master.Range("A" & lRow).Value) Then CompanyList.Add Master.Range("A" & lRow).Value, Master.Range("A" & lRow).Value End If Next lRow For Each vDictItem In CompanyList.Keys Master.Range("A3:R" & lMaxRow).AutoFilter 1, vDictItem If Master.Cells.SpecialCells(xlCellTypeVisible).Count > 0 Then Set NewSheet = ThisWorkbook.Worksheets.Add NewSheet.Name = vDictItem lNewRow = 1 For lRow = 3 To lMaxRow If Master.Rows(lRow).Hidden = False Then lNewRow = lNewRow + 1 NewSheet.Range("C1").Value = Master.Range("P1").Value NewSheet.Range("C" & lNewRow).Value = Master.Range("P" & lRow).Value NewSheet.Range("G1").Value = Master.Range("F1").Value NewSheet.Range("G" & lNewRow).Value = Left(Master.Range("F" & lRow).Value, 30) NewSheet.Range("E1").Value = Master.Range("G1").Value & " (POS)" NewSheet.Range("F1").Value = Master.Range("G1").Value & " (NEG)" If Master.Range("G" & lRow).Value >= 0 Then NewSheet.Range("E" & lNewRow).Value = Left(Master.Range("G" & lRow).Value, 30) Else NewSheet.Range("F" & lNewRow).Value = Left(Master.Range("G" & lRow).Value, 30) End If End If Next lRow End If Next vDictItem End Sub