Is it possible to copy cells from one sheet to another in excel?

Question

excel

Wholesalers need a price list in Excel, through which they can place an order. On one sheet there is a large catalog of products, with characteristics, prices and availability, where they put down the order number - how many pieces they need.
I want that if there is a number greater than 0 within the range, transfer the contents of some cells to the second Excel sheet, relative to the one where the number is greater than 0. Namely, size, color, cost, model, and so on.
As I understand it, you need to somehow transfer the coordinates of the filled cell to the second sheet and, relative to these coordinates, the coordinates of other cells associated with it.
Is it possible to do this in Excel (it is generated via PHPexcel) and if so, how?

Answer 1

With the following macro:

Sub GetProduct()
    ' Первая снизу непустая ячейка
    Dim LastRow As Integer
    
    ' Номер столбца с количеством > 0 (A = 1, B = 2 и т.д.)
    Dim PriceCol As Integer
    PriceCol = 3
    
    ' Название листа с каталогом товаров
    Dim PriceList As Worksheet
    Set PriceList = Excel.Workbooks(1).Worksheets.Item("Лист2")
    
    ' Название листа, на который копируются нужные ячейки
    Dim TotalList As Worksheet
    Set TotalList = Excel.Workbooks(1).Worksheets.Item("Лист1")
    
    ' Значение ячейки с количеством товара
    Dim Count As Object
    
    ' Массив номеров столбцов для копирования
    Dim Cols
    Cols = Array(1, 2)
    
    ' Счетчик строк для копирования
    Dim CountRow As Integer
    CountRow = 1
    
    ' Счетчик столбцов для копирования
    Dim CountCol As Integer
    
    LastRow = PriceList.Cells(Rows.Count, PriceCol).End(xlUp).Row
    
    
    For i = 1 To LastRow
        Set Count = PriceList.Cells(i, PriceCol)
        If (IsNumeric(Count)) And (Count > 0) Then
            CountCol = 1
            For Each copycell In Cols
                TotalList.Cells(CountRow, CountCol) = PriceList.Cells(i, copycell)
                CountCol = CountCol + 1
            Next
            CountRow = CountRow + 1
        End If
    Next i
End Sub

The logic is this:

• Find the most recent non-empty cell with the quantity of goods
  
• We run through all the lines to it, starting from the first
  
• Copy all cells of the row where the quantity of goods > 0
  

Cols = Array(1, 2)- here, separated by commas, the numbers of the columns are entered, from where it will be necessary to copy the values.
Given:
As a result: