Data needs transferring to the relevant tab

[dpaton05]

I have a spreadsheet that calculates costings. I want a start page or tab that allows you to enter costings for various dates, press a button and for it to filter the entries into the relevant sheet for each month depending on the date of the costing. I already have developed a spreadsheet that correctly produces the costing and this can be copied for other months but how would I create the tab that allows you to enter the info then sort it into the correct month tab?

 

[dpaton05]

I have a button on my tab where the information is entered. I am just trying to get it to first copy them to another tab.

What is wrong with this code I have as it won't work?

Private Sub cmdCopy_Click()

Worksheets("Home").Range("a5:c5").Copy _
Destination:=Worksheets("July2018").Range("A4:C4")

End Sub

 

[dpaton05]

Dunno why this wouldn't work. This is my code now:

Worksheets("Home").Range("a5:c5").Copy _
Destination:=Worksheets("July2018").Range("A4:C4")

This copies the cells A5:C5 to the july2018 worksheet. Only problem, it overwrites the previous values in the cells.

I need it to paste it below the cells if the cell range already has information entered in it. As new information gets entered into the home sheet it will get moved to the bottom of the information already entered.

 

[dpaton05]

I also need it to only paste the values and formulas, nothing else. On the home sheet, there is formatting that is copied across and I don't want it copied across. Here is my attempt:

Worksheets("Home").Range("a5:c5").Copy _
Destination:=Worksheets("July2018").Range("A9").PasteSpecial _
Paste:=xlPasteValues,xlformulas

What do I need to change to get it to only copy formulas and values?

 

[My Aswer Is This]

Try this:

Sub Copy_Me()
Application.ScreenUpdating = False
'Modified  10/25/2018  10:15:34 PM  EDT
Dim Lastrow As Long
Lastrow = Sheets("July2018").Cells(Rows.Count, "A").End(xlUp).Row + 1
Worksheets("Home").Range("a5:c5").Copy
Worksheets("July2018").Cells(Lastrow, 1).PasteSpecial Paste:=xlPasteValues
Worksheets("July2018").Cells(Lastrow, 1).PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[dpaton05]

Try this:

Sub Copy_Me()
Application.ScreenUpdating = False
'Modified  10/25/2018  10:15:34 PM  EDT
Dim Lastrow As Long
Lastrow = Sheets("July2018").Cells(Rows.Count, "A").End(xlUp).Row + 1
Worksheets("Home").Range("a5:c5").Copy
Worksheets("July2018").Cells(Lastrow, 1).PasteSpecial Paste:=xlPasteValues
Worksheets("July2018").Cells(Lastrow, 1).PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Thanks, that's awesome!

How would I now get a total field that is in cell j5 of the above row to be also copied to cell D5 on the new sheet, so next to the other information?

 

[dpaton05]

J5 contains a formula so if i copy and paste it normally, it copies the formula and it won't work as it doesn't have all the required information on the new sheet so it just needs to copy the value.

 

[dpaton05]

I worked it out, here is my code:

Sub cmdCopy_Click()

Application.ScreenUpdating = False
'Modified  10/25/2018  10:15:34 PM  EDT
Dim Lastrow As Long
Lastrow = Sheets("July2018").Cells(Rows.Count, "B").End(xlUp).Row + 1
Worksheets("Home").Range("a5:c5").copy
Worksheets("July2018").Cells(Lastrow, 1).PasteSpecial Paste:=xlPasteValues
Lastrow = Sheets("July2018").Cells(Rows.Count, "B").End(xlUp).Row
Worksheets("Home").Range("j5").copy
Worksheets("July2018").Cells(Lastrow, 4).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
[LEFT][COLOR=#222222][FONT=Verdana]

Now that I have that working, the next bit is a little harder for me to work out. I have worksheets in my workbook for all the months that the quotes will be generated for, such as July2018, August2018 etc and when the costing is entered in and the button pressed to copy it across to the july2018 sheet, I need it to find the correct sheet to put it in depending on the date of the costing.

I am trying to learn vba so would you be able to comment the code so I can understand what each line of code does please?


[/FONT][/COLOR][/LEFT]<strike></strike>

<strike></strike>

 

[My Aswer Is This]

Maybe someone else here will be able to help you.
And explain in detail what each line of code does.

 

[dpaton05]

Maybe someone else here will be able to help you.
And explain in detail what each line of code does.

So you are not sure how to do this?