0

I've been working on what is basically a register for sales. My spreadsheet records sales, profits etc and from that prints a receipt however, I'd like to keep my own record of this data to use for graphs etc. At the moment my macro moves up from a specified column, beginning from row 100000, then when it reaches a row with data, moves down one to the empty cell. In it it places the cell reference for the new data it has found. I'm new to VBA so I'm not sure if I'm taking the right approach, this is my code:

Sub DataLog()
'
' DataLog Macro
' Record sales in the data log.
'

'
Sheets("Data").Select

Dim rngd As Range
Set rngd = Range("A100000").End(xlUp).Offset(1, 0)

Dim coke As Range
Set coke = Range("C100000").End(xlUp).Offset(1, 0)

Dim creamsoda As Range
Set creamsoda = Range("D100000").End(xlUp).Offset(1, 0)

Dim lemonade As Range
Set lemonade = Range("E100000").End(xlUp).Offset(1, 0)

Dim pasito As Range
Set pasito = Range("F100000").End(xlUp).Offset(1, 0)

Dim pepsi As Range
Set pepsi = Range("G100000").End(xlUp).Offset(1, 0)

Dim pepsim As Range
Set pepsim = Range("H100000").End(xlUp).Offset(1, 0)

Dim solo As Range
Set solo = Range("I100000").End(xlUp).Offset(1, 0)

Dim sprite As Range
Set sprite = Range("J100000").End(xlUp).Offset(1, 0)

Dim sunkist As Range
Set sunkist = Range("K100000").End(xlUp).Offset(1, 0)

Dim water As Range
Set water = Range("L100000").End(xlUp).Offset(1, 0)

rngd.Value = "=Form!D1"

coke.Value = "=Form!E7"

creamsoda.Value = "=Form!E8"

lemonade.Value = "=Form!E9"

pasito.Value = "=Form!E10"

pepsi.Value = "=Form!E11"

pepsim.Value = "=Form!E12"

solo.Value = "=Form!E13"

sprite.Value = "=Form!E14"

sunkist.Value = "=Form!E15"

water.Value = "=Form!E16"

When I clock the macro button, the first time it works, as does it the second time. Although, when it does record the second set of data in the row below, it updates all the previous data in each cell. How can I stop any previously entered data from being updated so I can keep a weekly record of information? Any help or insight is greatly appreciated, thank you :)

EDIT:

I should add, I have tried locking the cells once data has been added but this only protected them from manual alteration, the macro still updated the data.

user3683101
  • 3
  • 2
  • Hmm... it seems like your problem might be that the formulas remain in the cells after your script runs. Ideally, after the script you've got here, you would walk through the entire `Data` sheet and overwrite formulas with values – Dan Wagner May 28 '14 at 10:56

1 Answers1

0

To overwrite the all formulas in your Data sheet with values, add these two lines to the bottom of your script:

Sheets("Data").Cells.Copy
Sheets("Data").Cells.PasteSpecial (xlPasteValues)

If there are formulas you want to keep, you'll simply need to define the Range of soda values you do want to overwrite and follow the same pattern:

Dim SodaData As Range
'ex below sets SodaData range from A3 to C3
Set SodaData = Sheets("Data").Range(Sheets("Data").Cells(3, 1), Sheets("Data").Cells(3, 3))
SodaData.Copy
SodaData.PasteSpecial (xlPasteValues)
Dan Wagner
  • 2,693
  • 2
  • 13
  • 18
  • Thanks Dan, that's exactly what I wanted and it worked perfectly. Is there anyway I good make that occur in the background without selecting all the cells? Again, thank you so much for your help :) – user3683101 May 28 '14 at 11:35
  • It's not the prettiest solution, but everything seems to be pretty tightly contained within the `Data` worksheet so you can make sure the script ends WITHOUT all the cells selected by adding `Sheets("Data").Cells(1, 1).Select` to the end of your script, which will leave you with only cell A1 selected – Dan Wagner May 28 '14 at 11:53
  • Your help has been invaluable, thank you so much. As soon as I get 15 reputation I'll be voting up your answer ahah. Again, thanks. – user3683101 May 28 '14 at 12:12
  • Nah, there are many, MANY more helpful folks on here. This link has some great info on how to reliably find the last row (which is something you're doing quite a few times): http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba. I would definitely recommend checking it out. Thanks! – Dan Wagner May 28 '14 at 12:59