problem transfer data from sheet to another

[abdelfattah]

hello

i have this code to transfer data from sheet to another the problem when i fill data more than brand in my invoice it only transfers one row while i have more than two rows it supposes transferring theses rows to another sheet i no know where is the problem

Sub REDA()
 Application.ScreenUpdating = False
    
    Dim iRow As Long
    
    
    iRow = Sheets("sls").Range("C1048576").End(xlUp).Row + 1
    
    
        With ThisWorkbook.Sheets("SLS")
        
           .Range("B" & iRow).Value = iRow - 1
           .Range("C" & iRow).Value = Sheets("INV").Range("D7").Value
           .Range("D" & iRow).Value = Sheets("INV").Range("D8").Value
           .Range("E" & iRow).Value = Sheets("INV").Range("C14").Value
           .Range("F" & iRow).Value = Sheets("INV").Range("D14").Value
           .Range("G" & iRow).Value = Sheets("INV").Range("E14").Value
           .Range("H" & iRow).Value = Sheets("INV").Range("F14").Value
           .Range("I" & iRow).Value = Sheets("INV").Range("G14").Value
           .Range("J" & iRow).Value = Sheets("INV").Range("G24").Value
           .Range("K" & iRow).Value = Sheets("INV").Range("G25").Value
           .Range("L" & iRow).Value = Sheets("INV").Range("G26").Value
           .Range("M" & iRow).Value = Sheets("INV").Range("G27").Value
        
          
          
        End With
        end sub

 

[abdelfattah]

the problem still continues it gives me the same thing above image2 this attached image based on highlight by yellow into above image1, output supposing be this

 

[Joe4]

I really do not understand what you are asking.

Can you do me a favor? Can you post the following 4 images:
1. What the "inv" sheet looks like before the update
2. What the "sls" sheet looks like before the update
3. What the "inv" sheet looks like after the update
4. What the "sls" sheet looks like after the update

If I can see what you are starting with, and what you want the expected results to look like, I think that may clarify things.

 

[abdelfattah]

the image 3 when i fill the data in sheet("inv") and image 4 is output you can see the sheet("sls") is archive the invoices

 

[Joe4]

OK, I think I see now. I was focusing on the body of the invoice, but it looks like you also want stuff from the header section and total section.
This should do that:

Sub REDA2()

    Dim lr As Long
    Dim nr As Long
    Dim er As Long
    
    Application.ScreenUpdating = False
    
'   Find next available row on "sls" sheet
    Sheets("sls").Activate
    nr = Sheets("sls").Cells(Rows.Count, "B").End(xlUp).Row + 1
    
'   Find last row with data in column B on "inv" sheet
    Sheets("inv").Activate
    lr = Sheets("inv").Range("B13").End(xlDown).Row
    
'   Calculate ending row on "sls" sheet
    er = nr + lr - 14
    
'   Copy data to "sls" sheet
    Sheets("inv").Range(Cells(14, 2), Cells(lr, 2)).Copy Sheets("sls").Cells(nr, 2)
    Sheets("inv").Range(Cells(14, 3), Cells(lr, 7)).Copy Sheets("sls").Cells(nr, 5)
    
    Sheets("sls").Activate
    Sheets("sls").Range(Cells(nr, 3), Cells(er, 3)).Value = Sheets("inv").Range("D7").Value
    Sheets("sls").Range(Cells(nr, 4), Cells(nr + lr - 14, 4)).Value = Sheets("inv").Range("D8").Value
    Sheets("sls").Range(Cells(nr, 10), Cells(nr + lr - 14, 10)).Value = Sheets("inv").Range("G24").Value
    Sheets("sls").Range(Cells(nr, 11), Cells(nr + lr - 14, 11)).Value = Sheets("inv").Range("G25").Value
    Sheets("sls").Range(Cells(nr, 12), Cells(nr + lr - 14, 12)).Value = Sheets("inv").Range("G26").Value
    Sheets("sls").Range(Cells(nr, 13), Cells(nr + lr - 14, 13)).Value = Sheets("inv").Range("G27").Value

    Application.ScreenUpdating = True
    
End Sub

 

[abdelfattah]

hi, joe it doesn't show anything in sheet("sls") !

 

[Joe4]

I tested it out, and it works perfectly for me. It matches exactly what you posted in your 4 images.

One question though, on your "inv" sheet that could be causing issues.
On rows 7-11, are you merging columns D-G?
If so, get rid of those merged cells. Merged cells are very bad and cause lots of issues for VBA!

You can accomplish the exact same visual effect *** merged cells without all the issues by doing the following:
1. Selecting the range D7:G11
2. Right-click
3. Select Format Cells
4. Go to the Alignment Tab
5. Under the "Horizontal" setting, select "Center Across Selection"

 

[abdelfattah]

i cancelled merged cell as you said , but unfortunately it doesn't show any thing in sheet "sls" this is my file if you have time you can check it
1.xlsm

 

[Joe4]

The macro is not the problem, your "SLS" sheet is. You have data way down on lives 1633-1639.
Try deleting all the rows from row 6-1639. Then re-save your file and try again.

 

[abdelfattah]

yes, you're right but i have a problem with the format not same thing in some columns and there is error in column f

 

[Joe4]

That is because it looks like that data in column D on your "inv" sheet is not being manually entered, but rather it is a formula, and in your formula, you are unnecessarily locking down column C (which prevents the formula from shifting properly when you are copying to a different column on the "sls" sheet (column F).

Change the formula in cell D14 on your "inv" sheet from:

=IF($C14="","",INDEX(codes!$C:$C,MATCH($C14,codes!$B:$B,0)))

to this:

=IF(C14="","",INDEX(codes!$C:$C,MATCH(C14,codes!$B:$B,0)))

and copy it all the way down to cell D23, and then the values will be correct when copied to the "sls" sheet.