problem transfer data from sheet to another

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,429
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
VBA Code:
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
right now, it looks like you only have it set to do the last row.
If you want to loop through multiple rows, then you need to add a loop, telling where to start and end.

So if you wanted it to start at row 2 and go down to the last row, you would need to do something like:
VBA Code:
Sub REDA()
Application.ScreenUpdating = False
   
    Dim eRow as Long
    Dim iRow As Long
   
    eRow = Sheets("sls").Range("C1048576").End(xlUp).Row + 1
   
    With ThisWorkbook.Sheets("SLS")
        For iRow = 2 to eRow    
           .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
        Next iRow
     End With

End Sub
 
Upvote 0
thanks joe you seem misunderstanding this is my mistake it supposed capture picture to understand the image 1 when i fill data in sheet1 more rows i'm talking about from b14 in sheet1
1.xlsm
DEFG
8hh
INV


and image 2 it only transfer 1 row

1.xlsm
F
15
SLS
 
Last edited:
Upvote 0
thanks joe you seem misunderstanding this is my mistake it supposed capture picture to understand the image 1 when i fill data in sheet1 more rows i'm talking about from b14 in sheet1
I am sorry, but I do not understand what you are trying to say. I suspect we are having a language barrier here.
Can you explain the problem in detail in your native language, and use Google Translate to translate it to English?
 
Upvote 0
look i have data in sheet("inv") this is the home of invoice data and sheet("sls") output data from sheet("inv") the data transfer normally but the problem not all data the problem begins from row14 where i fill the brand ,quantity,price i attach img1 this is my filling data and highlight by yellow data don't transfer to sheet("sls")
aa.JPG





this image to output you see only data transfer from sheet("inv") row14 ther is no row15,16
aa1.JPG




i hope this help to understand my problem
 
Upvote 0
So, are you just wanting the macro to copy over all 3 records from you "inv" sheet to the "sls" sheet.
It doesn't look anything like what your original code waas doing.
 
Upvote 0
yes , joe with considering , it's not only 3 recorded ,it's changeable maybe four or five...etc this is invoice my code works but not completely as what i would the problem it doesn't move to next row as in the image from row14 the rest of data as what contains code is ok
 
Upvote 0
I think this should do it:
VBA Code:
Sub REDA2()

    Dim lr As Long
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column B on invoice sheet
    Sheets("inv").Activate
    lr = Sheets("inv").Range("B13").End(xlDown).Row
    
'   Copy data to "sls" sheet
    Sheets("inv").Range(Cells(14, 2), Cells(lr, 2)).Copy Sheets("sls").Range("B6")
    Sheets("inv").Range(Cells(14, 3), Cells(lr, 7)).Copy Sheets("sls").Range("E6")
    
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
actually i appreciate you effort but , i don't agree the way what works your code this code transfer to one row sheets("sls") every a new process i filled in sheets("inv" ) it exchange the old data , you can say delete old data and replace a new data more over indeed , the problem became more complicated
 
Upvote 0
actually i appreciate you effort but , i don't agree the way what works your code this code transfer to one row sheets("sls") every a new process i filled in sheets("inv" ) it exchange the old data , you can say delete old data and replace a new data more over indeed , the problem became more complicated
This is why it important for you to explain the full extent of the problem in detail. Remember, while you are very familiar with the problem, the only thing that we know about it is what you share with us here. Otherwise, we are left to guess and make assumptions about certain details.

Are you saying that there may already be data on the "sls" page that you do NOT want to overwrite, and you want to copy/paste the new data below the existing data on the "sls" page? If so, that is a rather easy fix.

Try this:
VBA Code:
Sub REDA2()

    Dim lr As Long
    Dim nr 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
   
'   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)
   
    Application.ScreenUpdating = True
   
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top