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

E

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:

[TABLE="width: 250"]
<tbody>[TR]
[TD][TABLE="width: 192"]
<tbody>[TR]
[TD][/TD]
[TD]new[/TD]
[TD]new[/TD]
[/TR]
[TR]
[TD]Fruits[/TD]
[TD]Amount[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Blackberry[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dragonfruit[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jackfruit[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kiwi[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lemon[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lychee[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Melon[/TD]
[TD="align: right"]17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Peach[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pineapple[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Strawberry[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Strawberry[/TD]
[TD="align: right"]63[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Below is the new sheet:
[TABLE="width: 250"]
<tbody>[TR]
[TD][TABLE="width: 138"]
<tbody>[TR]
[TD]Fruits[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Blackberry[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Blueberry[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Jackfruit[/TD]
[TD="align: right"]55[/TD]
[/TR]
[TR]
[TD]Kiwi[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Lemon[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]Lychee[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Melon[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD]Peach[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Pineapple[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]Strawberry[/TD]
[TD="align: right"]63[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Desired Output
[TABLE="width: 250"]
<tbody>[TR]
[TD][TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]new[/TD]
[TD="width: 64"]new[/TD]
[/TR]
[TR]
[TD]Fruits[/TD]
[TD]Amount[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Blackberry[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Blueberry[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Dragonfruit[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Jackfruit[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]55[/TD]
[/TR]
[TR]
[TD]Kiwi[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Lemon[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]Lychee[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Melon[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD]Peach[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Pineapple[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]Strawberry[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]63[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]251[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
You must log in or register to reply here.

Similar threads

N
  • Question Question
Finding the differences then copy and paste code to a new sheet
Replies
1
Views
582
myall_blues
myall_blues
Q
  • Question Question
Macro needed to insert 4 rows in table, copy data from above, copy data from another sheet and paste special (transpose values) to those new rows...re
Replies
1
Views
229
sijpie
sijpie
S
  • Question Question
Copying rows to new sheet referencing master
Replies
2
Views
532
SPABS
S
S
  • Question Question
Copy and paste one row from filtered table as new a new row, always in row 3, to protected worksheet
Replies
0
Views
545
SonntagC
S
C
  • Question Question
Excel VBA macro to copy rows from multiple sheets upon entry
Replies
1
Views
488
Anbuselvam
A

Forum statistics

Threads
1,223,243
Messages
6,170,971
Members
452,371
Latest member
Frana

Share this page

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