Hi, I am making a workbook to log all data based on coffee roast profiles sold.
Sales sheet has the following which I want to copy to log sheet.
B2 = sales ID
B4 = name (either company or person)
Then I can have data from D11 all the way to F20, and that's where I am finding difficulties, I can copy and paste D11:F11 but if I have more than one row then I will only have data on the first row on the log sheet.
log sheet is as follows:
should look as follows:
my VBA macro:
Sales sheet has the following which I want to copy to log sheet.
B2 = sales ID
B4 = name (either company or person)
Then I can have data from D11 all the way to F20, and that's where I am finding difficulties, I can copy and paste D11:F11 but if I have more than one row then I will only have data on the first row on the log sheet.
D11 | E11 | F11 |
Light | Whole | 1,250 g |
Medium | Whole | 2,500 g |
Dark | Whole | 2,500 g |
Medium | Ground | 750 g |
log sheet is as follows:
(A)Day | (B)Order Nr | (C)Client | (D)Seller | (E)Roast | (F)Prep | (G)Quantity |
16-06-20 | SBC 00001 | Baker | Dan | Light | Whole | 1250 |
Medium | Whole | 2500 | ||||
Dark | Whole | 2500 | ||||
Medium | Ground | 750 | ||||
16-06-20 | SBC 00002 | Lawyer | Tom | Light | Whole | 1250 |
Medium | Whole | 2500 | ||||
Dark | Whole | 2500 | ||||
Medium | Ground | 750 | ||||
16-06-20 | SBC 00003 | Dentist | Joe | Light | Whole | 1250 |
Medium | Whole | 2500 | ||||
Dark | Whole | 2500 | ||||
Medium | Ground | 750 | ||||
should look as follows:
(A)Day | (B)Order Nr | (C)Client | (D)Seller | (E)Roast | (F)Prep | (G)Quantity |
16-06-20 | SBC 00001 | Baker | Dan | Light | Whole | 1250 |
16-06-20 | SBC 00001 | Baker | Dan | Medium | Whole | 2500 |
16-06-20 | SBC 00001 | Baker | Dan | Dark | Whole | 2500 |
16-06-20 | SBC 00001 | Baker | Dan | Medium | Ground | 750 |
16-06-20 | SBC 00001 | Lawyer | Tom | Light | Whole | 1250 |
16-06-20 | SBC 00001 | Lawyer | Tom | Medium | Whole | 2500 |
16-06-20 | SBC 00001 | Lawyer | Tom | Dark | Whole | 2500 |
16-06-20 | SBC 00001 | Lawyer | Tom | Medium | Ground | 750 |
16-06-20 | SBC 00001 | Dentist | Joe | Light | Whole | 1250 |
16-06-20 | SBC 00001 | Dentist | Joe | Medium | Whole | 2500 |
16-06-20 | SBC 00001 | Dentist | Joe | Dark | Whole | 2500 |
16-06-20 | SBC 00001 | Dentist | Joe | Medium | Ground | 750 |
my VBA macro:
VBA Code:
Sub insert()
Dim ms As Worksheet, NR
Set ms = Sheets("log")
With Sheets("sales")
NR = ms.Cells.Find("*", , , , xlByRows, xlPrevious).Row + 1
ms.Range("A" & NR) = Date
.Range("B2").Resize(1).Copy 'sale nr
ms.Range("B" & NR).PasteSpecial xlValues
.Range("B4").Resize(1).Copy 'client
ms.Range("C" & NR).PasteSpecial xlValues
myValue = InputBox("Quem vendeu") 'broker
ms.Range("D" & NR).Value = myValue
'A B C and D should repeat the paste by the amount of rows between B11 and B20 with data
.Range("d11:f11").Resize(10).Copy 'order
ms.Range("E" & NR).PasteSpecial xlValues, Transpose:=False
End With
End Sub