VBA Saving Data

[Dannybert]

I have a table to keep track of amounts and prizes. I want to use a VBA code to be able to make a button that can save the data from this week and start a new week in the row below.

This is what the table looks like now:

So after running the macro, I would like to get it to start a new row and change some stuff on the current (lowest) row. It would look like this:

So after running the macro (VBA code), it should start a new row and display the currentDate (which should stay the same after pasting it). Then it should copy the data from the first row like I displayed with the asterixes.
After completing the date for the new row, it should change the 'End Amount' and 'End Value' to only be left with the number/currency instead of the formula, because the formula is to display real-time changes. So it should be fixed after starting a new row.

The last 4 columns can be copied over directly since those calculations and formulas are relative.

Thanks already!

 

[Logit]

Please provide the various formulas for each of the cells. Be certain to indicate which cell the formula belongs in.

 

[Dannybert]

The first cell should display today's date and don't change afterwards. Cells B3 and C3 should display the raw data from cells D2 and C2 in this case. These cells contain formulas so it should be turned to raw data when pasting it on the new row.

Then cells E3 till I3 should have the formulas copied over from E2 till I2. So all the formulas can simply be copied.

The last step is to change the D2 and E2 to raw data to get rid of the formulas in there.

The formulas are:

1. D2 = Referring to a cell on Sheet1 (i.e. = Sheet1!$G$2)
2. E2 = Referring to another cell on Sheet1 (i.e. =Sheet1!$H$2)
3. F2 = Calculating the percentage from D2 and B2. (i.e. =D2/B2-100%)
4. G2 = E2 - C2
5. H2 = Calculating the percentage from C2 and E2. (i.e. =E2/C2-100%)
6. I2 = Dividing Profit by 7 (i.e. =G2/7)

 

[Logit]

There are several methods to accomplish your goal ... this is only one :

Option Explicit
            
Sub NextDayEntries()

Dim LastCell As Range
Dim LastCellColRef As Long

LastCellColRef = 1  'column number to look in when finding last cell

    Set LastCell = Sheets("Sheet2").Cells(Rows.Count, LastCellColRef).End(xlUp).Offset(1, 0)

    LastCell.Select
    LastCell.Value = Date
    
    LastCell.Offset(0, 3).Formula = "=Sheet1!$G$2"
    LastCell.Offset(0, 4).Formula = "=Sheet1!$H$2"

    LastCell.Offset(0, 1).Value = LastCell.Offset(-1, 3).Value
    LastCell.Offset(0, 2).Value = LastCell.Offset(-1, 4).Value
    
    LastCell.Offset(-1, 3).Value = LastCell.Offset(-1, 3).Value
    LastCell.Offset(-1, 4).Value = LastCell.Offset(-1, 4).Value
    
    LastCell.Offset(-1, 5).Resize(, 8).Copy
    LastCell.Offset(0, 5).PasteSpecial Paste:=xlPasteFormulas
    
Application.CutCopyMode = False

Set LastCell = Nothing

End Sub

If you are beginning with a brand new - unused worksheet .... you will need to "seed" the second line with numbers from the previous day.

Download example workbook : Formulas Copy Paste.xlsm

 

[johnnyL]

This is the version I came up with:

Sub StartNewRow()
'
    Dim LastRowMaster   As Long
    Dim wsMaster        As Worksheet
'
    Set wsMaster = Sheets("Master")                                 ' <--- Set this to the desired sheet name
'
    LastRowMaster = wsMasterRange("A" & Rows.Count).End(xlUp).Row                                       ' Determine last used row to in the sheet
'
    wsMaster.Range("A" & LastRowMaster + 1) = Date                                                      ' Set the date for new row
    wsMaster.Range("B" & LastRowMaster + 1) = wsMaster.Range("B" & LastRowMaster + 1).Offset(-1, 2)     ' Get value from previous row + 2 columns
    wsMaster.Range("C" & LastRowMaster + 1) = wsMaster.Range("C" & LastRowMaster + 1).Offset(-1, 2)     ' Get value from previous row + 2 columns
'
'   Copies Formulas from Columns D thru I Down 1 row
    wsMaster.Range("D" & LastRowMaster & ":I" & LastRowMaster).AutoFill Destination:=wsMaster.Range("D" & LastRowMaster & ":I" & LastRowMaster + 1), Type:=xlFillDefault
'
'   Removes a couple formulas from previous row, keeps the Values
    wsMaster.Range("D" & LastRowMaster & ":E" & LastRowMaster).Value = wsMaster.Range("D" & LastRowMaster & ":E" & LastRowMaster).Value
End Sub

@Dannybert, are you sure about the formulas in D2 & E2? Should those formulas always be the same or should they change to reflect the next row down when a row is added like the other formulas?

 

[Dannybert]

This is the version I came up with:

Sub StartNewRow()
'
    Dim LastRowMaster   As Long
    Dim wsMaster        As Worksheet
'
    Set wsMaster = Sheets("Master")                                 ' <--- Set this to the desired sheet name
'
    LastRowMaster = wsMasterRange("A" & Rows.Count).End(xlUp).Row                                       ' Determine last used row to in the sheet
'
    wsMaster.Range("A" & LastRowMaster + 1) = Date                                                      ' Set the date for new row
    wsMaster.Range("B" & LastRowMaster + 1) = wsMaster.Range("B" & LastRowMaster + 1).Offset(-1, 2)     ' Get value from previous row + 2 columns
    wsMaster.Range("C" & LastRowMaster + 1) = wsMaster.Range("C" & LastRowMaster + 1).Offset(-1, 2)     ' Get value from previous row + 2 columns
'
'   Copies Formulas from Columns D thru I Down 1 row
    wsMaster.Range("D" & LastRowMaster & ":I" & LastRowMaster).AutoFill Destination:=wsMaster.Range("D" & LastRowMaster & ":I" & LastRowMaster + 1), Type:=xlFillDefault
'
'   Removes a couple formulas from previous row, keeps the Values
    wsMaster.Range("D" & LastRowMaster & ":E" & LastRowMaster).Value = wsMaster.Range("D" & LastRowMaster & ":E" & LastRowMaster).Value
End Sub

@Dannybert, are you sure about the formulas in D2 & E2? Should those formulas always be the same or should they change to reflect the next row down when a row is added like the other formulas?

Yes they should be absolute since the cells on Sheet1 always stay on the same place?

 

[Dannybert]

T

There are several methods to accomplish your goal ... this is only one :

Option Explicit
           
Sub NextDayEntries()

Dim LastCell As Range
Dim LastCellColRef As Long

LastCellColRef = 1  'column number to look in when finding last cell

    Set LastCell = Sheets("Sheet2").Cells(Rows.Count, LastCellColRef).End(xlUp).Offset(1, 0)

    LastCell.Select
    LastCell.Value = Date
   
    LastCell.Offset(0, 3).Formula = "=Sheet1!$G$2"
    LastCell.Offset(0, 4).Formula = "=Sheet1!$H$2"

    LastCell.Offset(0, 1).Value = LastCell.Offset(-1, 3).Value
    LastCell.Offset(0, 2).Value = LastCell.Offset(-1, 4).Value
   
    LastCell.Offset(-1, 3).Value = LastCell.Offset(-1, 3).Value
    LastCell.Offset(-1, 4).Value = LastCell.Offset(-1, 4).Value
   
    LastCell.Offset(-1, 5).Resize(, 8).Copy
    LastCell.Offset(0, 5).PasteSpecial Paste:=xlPasteFormulas
   
Application.CutCopyMode = False

Set LastCell = Nothing

End Sub

If you are beginning with a brand new - unused worksheet .... you will need to "seed" the second line with numbers from the previous day.

Download example workbook : Formulas Copy Paste.xlsm

Thanks a lot! I will try this later today! I will let you know if it worked?

 

[Dannybert]

There are several methods to accomplish your goal ... this is only one :

Option Explicit
           
Sub NextDayEntries()

Dim LastCell As Range
Dim LastCellColRef As Long

LastCellColRef = 1  'column number to look in when finding last cell

    Set LastCell = Sheets("Sheet2").Cells(Rows.Count, LastCellColRef).End(xlUp).Offset(1, 0)

    LastCell.Select
    LastCell.Value = Date
   
    LastCell.Offset(0, 3).Formula = "=Sheet1!$G$2"
    LastCell.Offset(0, 4).Formula = "=Sheet1!$H$2"

    LastCell.Offset(0, 1).Value = LastCell.Offset(-1, 3).Value
    LastCell.Offset(0, 2).Value = LastCell.Offset(-1, 4).Value
   
    LastCell.Offset(-1, 3).Value = LastCell.Offset(-1, 3).Value
    LastCell.Offset(-1, 4).Value = LastCell.Offset(-1, 4).Value
   
    LastCell.Offset(-1, 5).Resize(, 8).Copy
    LastCell.Offset(0, 5).PasteSpecial Paste:=xlPasteFormulas
   
Application.CutCopyMode = False

Set LastCell = Nothing

End Sub

If you are beginning with a brand new - unused worksheet .... you will need to "seed" the second line with numbers from the previous day.

Download example workbook : Formulas Copy Paste.xlsm

I tried the code and it worked, after I formatted the cells to the correct type of data and I changed the table to only be formatted as a table till the last row. So no empty rows in the table did the trick, since before it added the new row underneath the existing table 10 rows below. I also now changed the format for all the rows (outside the table) to make sure it does copy the new row perfectly.

All and all it works like a charm! I just need to have some things setup properly before running it, but I know what it is now

And if I would like to delete the 'last' row and put back the formula's (=Sheet1!$G$2 and =Sheet1!$H$2) and make a button for it, is that also easy to do? Or maybe easier?

Many thanks for this one man! Was struggling so much.

 

[Dannybert]

This is the version I came up with:

Sub StartNewRow()
'
    Dim LastRowMaster   As Long
    Dim wsMaster        As Worksheet
'
    Set wsMaster = Sheets("Master")                                 ' <--- Set this to the desired sheet name
'
    LastRowMaster = wsMasterRange("A" & Rows.Count).End(xlUp).Row                                       ' Determine last used row to in the sheet
'
    wsMaster.Range("A" & LastRowMaster + 1) = Date                                                      ' Set the date for new row
    wsMaster.Range("B" & LastRowMaster + 1) = wsMaster.Range("B" & LastRowMaster + 1).Offset(-1, 2)     ' Get value from previous row + 2 columns
    wsMaster.Range("C" & LastRowMaster + 1) = wsMaster.Range("C" & LastRowMaster + 1).Offset(-1, 2)     ' Get value from previous row + 2 columns
'
'   Copies Formulas from Columns D thru I Down 1 row
    wsMaster.Range("D" & LastRowMaster & ":I" & LastRowMaster).AutoFill Destination:=wsMaster.Range("D" & LastRowMaster & ":I" & LastRowMaster + 1), Type:=xlFillDefault
'
'   Removes a couple formulas from previous row, keeps the Values
    wsMaster.Range("D" & LastRowMaster & ":E" & LastRowMaster).Value = wsMaster.Range("D" & LastRowMaster & ":E" & LastRowMaster).Value
End Sub

@Dannybert, are you sure about the formulas in D2 & E2? Should those formulas always be the same or should they change to reflect the next row down when a row is added like the other formulas?

Hey thanks for helping me out, but this code is giving me an error on 'wsMasterRange'. So it doesn't run now and I tried to change it to 'wsMaster.Range' but that didn't help

I already got another solution so it's fine but thanks a lot for helping me out!

 

[Logit]

LastRowMaster = wsMaster.Range("A" & Rows.Count).End(xlUp).Row

You left out a PERIOD