0

I have a workbook that has a receive tracker and a production tracker that will be updated daily and needs to be saved to a master DATA sheet that holds all the info. For ease of use to my coworkers that are not computer savvy I want them to be able to hit a button to save the info they put in that copies and saves the data and clears the form. also i can not get it through my head how to use getLastRow

I have created a macro and a button for both sheets that copy and paste the data but I believe my range is wrong. If I click the button on receive tracker everything is fine, with the exception of not being able to do get last row correctly. When there is already data in the DATA worksheet from receive traker and I click on the button for Production Tracker it will paste it both where I want it and also over cells of receive tracker. Also I need to keep the formula on the production tracker it gets delete when I do Delete or clearcontets

Macro for Receive tracker is 

'''    Sub Macro3()
'
' Macro3 Macro
'

'
    Sheets("RecieveTracker").Select
    Range("A7:J105").Select
    Selection.copy
    Sheets("DATA").Select
    Range("A7").Select
    ActiveSheet.Paste
    Sheets("RecieveTracker").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
End Sub
'''

for Production tracker is ''' Sub Macro5() ' ' Macro5 Macro '

'
    Sheets("DailyProduction").Select
    Range("A2:I150").Select
    Selection.copy
    Sheets("DATA").Select
    Range("O7").Select
    ActiveSheet.Paste
    Sheets("DailyProduction").Select
    Application.CutCopyMode = False
    Selection.ClearContents
End Sub
'''

I do not get an error message when I run it but i loose the formulas on my production tracker and all the other data just gets copied over for reference on the DATA sheet the Recieve tracker should range from A7-J7 and down and the data from Production tracker should be o7-w7 and down

Mikku
  • 6,538
  • 3
  • 15
  • 38
Scott
  • 15
  • 5
  • [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/62576) is a must-read, especially when you're using it 10 times in 20 lines of code. – Ken White Aug 14 '19 at 22:07

2 Answers2

0

I'm gonna share with you one of my best friends:

last_cell_data = Sheets("DATA").Cells(Rows.Count, "A").End(xlUp).Row

It gives you the last line in column "A",with a value, so gives you an integer. If you want the column "O", just replace "A" for "O".

For your issue use like this:

Range("O" & last_cell_Data + 1 ).Select

Now you won't overwrite anymore.

TiagoSantos
  • 127
  • 8
0

For my issue with clearing the data and keeping the formula I used...

Range("A2:I150").SpecialCells(xlCellTypeConstants).ClearContents
Scott
  • 15
  • 5