Copy entries from a new sheet and paste them to the respective row in Master sheet

ExcelNewbiee

New Member
Joined
May 9, 2017
Messages
1
I got a "new" sheet that will be generated few times a day, and it will be deleted after this sub.
I want to copy data from "new" sheet and paste it into existing "records" sheet

  1. paste data into its respective row (different fruits)
  2. add new fruit row if fruit doesn't exist in "records" sheet
  3. sort from (a-z), the entire used range in "records" sheet by first column of fruit name
  4. fill zero for found blank cell in used range
  5. add a total row to the last used range row + 1 (so it will be different whenever this sheet run for new stall order)
I have no idea what went wrong in the code as the banana, blueberry should be added as new fruits in "Records", but now I get an extra Strawberry in the list, think it should be smtg about the else condition, but I don't know how to fix it.
For now I only get to the 2nd part of my requirements, as I am not too familiar with used range yet, I'm still having a little struggle in coding it.

my current "Records" sheet:


newnew
FruitsAmountAmount
Apple33
Blackberry4
Dragonfruit6
Jackfruit9
Kiwi2
Lemon7
Lychee10
Melon17
Orange20
Peach8
Pear1
Pineapple3
Strawberry5
Strawberry63

<tbody>
</tbody>

<tbody>
</tbody>

Below is the new sheet:
FruitsAmount
Apple3
Banana10
Blackberry5
Blueberry7
Jackfruit55
Kiwi2
Lemon23
Lychee6
Melon17
Orange33
Peach8
Pear1
Pineapple18
Strawberry63

<tbody>
</tbody>

<tbody>
</tbody>

Desired Output
newnew
FruitsAmountAmount
Apple33
Banana010
Blackberry45
Blueberry07
Dragonfruit60
Jackfruit955
Kiwi22
Lemon723
Lychee106
Melon1717
Orange2033
Peach88
Pear11
Pineapple318
Strawberry563
Total95251

<tbody>
</tbody>

<tbody>
</tbody>

In this case, you may take note on the Banana, Blueberry and Dragonfruit

my code so far

Code:
Sub fruits()
'find first blank column
blankCol = ThisWorkbook.Worksheets("Records").Cells(1, Columns.Count).End(xlToLeft).Column
If blankCol > 1 Then
blankCol = blankCol + 1
End If


'paste the list into existing "Records" sheet
Set rsht = ThisWorkbook.Worksheets("Records")
Set r = rsht.Range("A1") 'record sheet


'set up header for new entry in "Records"
r.Offset(0, blankCol - 1).Value = "new" 'new sheet name from different stalls
r.Offset(1, blankCol - 1).Value = "Amount"


'new sheet with new data
Set temp = ThisWorkbook.Worksheets("new")
Set n = temp.Range("A1") 'temp : new


Dim irow As Long
Dim jrow As Long
irow = temp.Cells(Rows.Count, "A").End(xlUp).Row 'last row in new temp sheet
jrow = rsht.Cells(Rows.Count, "A").End(xlUp).Row 'last row in record sheet


Dim nextrow As Long
nextrow = rsht.Cells(Rows.Count, "A").End(xlUp).Row + 1 'find next empty row in "Records"


    j = 2 '"Records" sheet


    For i = 1 To irow 'refer new


        If n.Offset(i, 0).Value = r.Offset(j, 0).Value Then 'if name matched then


            r.Offset(j, blankCol - 1).Value = n.Offset(i, 1).Value 'copy respective value


        Else 'if name doesn't match


            'paste new name into next available row
            r.Offset(nextrow - 1, 0).Value = n.Offset(i, 0).Value 'name
            r.Offset(nextrow - 1, blankCol - 1).Value = n.Offset(i, 1).Value 'value


        End If


    j = j + 1
    Next i

End Sub


Appreciate for any advice. Thank you. I'm stuck for quite sometime now.
Also apologize for not able to put the three tables horizontally for easier view. Idk how to do that
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Forum statistics

Threads
1,215,007
Messages
6,122,670
Members
449,091
Latest member
peppernaut

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