VBA to copy data from a template in a sheet to Database Sheet

[dandelion]

I have a "Template" as in the MiniSheet below where I want to copy data to DataBase Sheet. (I present 02 sheets in 01 layout here for easier reference). What I want to do is I want to select and copy the non-blank cells in each Month column and paste in Database Sheet vertically with the name of the Month in each row. Is there any way to do it in VBA instead of copy manually?

Book3
	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	Template
3	Name	Category	Jan 2020	Feb 2020	Mar 2020	Apr 2020	May 2020	Jun 2020	Jul 2020	Aug 2020	Sep 2020	Oct 2020	Nov 2020	Dec 2020
4	Apple Juice	Sour	$ 3.00		$ 4.00		$ 5.00	$ 4.00	$ 5.00	$ 6.00				$ 3.00
5	Lemon Juice	Sour	$ 3.00			$ 2.00	$ 1.00			$ 4.00	$ 3.00	$ 3.00		$ 3.00
6	Oragne Juice	Sweet		$ 2.00			$ 1.00				$ 3.00		$ 5.00
7	Watermelon Juice	Sweet			$ 4.00		$ 1.00			$ 4.00		$ 5.00	$ 5.00
8
9
10	Database
11	Name	Category	Period	Value
12	Apple Juice	Sour	Jan 2020	$ 3.00
13	Lemon Juice	Sour	Jan 2020	$ 3.00
14	Oragne Juice	Sweet	Feb 2020	$ 2.00
15	Apple Juice	Sour	Mar 2020	$ 4.00
16	Watermelon Juice	Sweet	Mar 2020	$ 4.00
Sheet1

 

[portews]

Are you creating the Database from scratch, or just adding the current month? What the rows and columns are things located in the Database sheet?
To create the Database from scratch, assuming the sheet is already created and the Name field is in Cell A2, try this:

Sub CreateDatabase()

Dim Templ As Worksheet
Dim DB As Worksheet
Dim LastCol As Long
Dim i As Long, j As Long, k As Long

Set Templ = Sheets("Template")
Set DB = Sheets("Database")

'start populating with row 3 of DB
k = 3
'find the last used column of Template
LastCol = Templ.Cells(3, Templ.Columns.Count).End(xlToLeft).Column

With DB
    'for columns 5 thru last column
    For j = 5 To LastCol
        'check rows 3 thru 7
        For i = 3 To 7
            'if the cost is not empty
            If Templ.Cells(i, j) > "" Then
                'copy the price to the next row in DB from that cell
                .Cells(k, 4) = Templ.Cells(i, j) 'price
                'copy the Name and category
                Templ.Range(Templ.Cells(i, 3), Templ.Cells(i, 4)).Copy
                'paste it into columns A & B on DB
                .Range("A" & k).PasteSpecial 'name and category
                'copy the month to DB
                .Cells(k, 3) = Templ.Cells(2, j) 'month
                'increment k to set up for the next row on DB
                k = k + 1
            End If
        Next i
    Next j
End With
End Sub

 

[dandelion]

Brilliant! Thanks