Home/ Questions/Q 12817
Next
Answered
computing
  • 5
Asked: 2021-12-10T06:21:15+00:00 In:

Solved I Need a Macro To Copy Data To An Archive Sheet

  • 5

I need a macro that copies data from various cells in calculator then paste them in an archive sheet. the cells are not in any particular order but they will be in the archive sheet. then i need the macro to move down 1 row to the next blank row and zero out the calculations on the calulator sheet. and repeat every time this button is pressed.

CAN ANYONE HELP!!!!????

Share

    1 Answer

    1. Best Answer
      Hi Bjells

      See if this will do what you need. I’ve changed the output slightly because I believe you had an error in where the results are archived. At the top of the macro I’ve specified what the outputs cells are. The “button” you press is, I’m assuming, an activex or Form control button. If not please let me know what you mean by “button that you press”

      Here’s the Macro. Any problems let me know …

      Sub Archive()


'Calculator   Archive Col
'C10            C
'E10            B
'J10            D
'D14            E
'D18            A
'H27            F
'D27            G


Sheets("Calculator").Activate

LastRow = Sheets("Archive").Range("A" & Rows.Count).End(xlUp).Row + 1 'Sets the last row in the Archives

'Copies the Calculator data to the Archive
        Sheets("Archive").Range("C" & LastRow) = Range("C10")
        Sheets("Archive").Range("B" & LastRow) = Range("E10")
        Sheets("Archive").Range("D" & LastRow) = Range("J10")
        Sheets("Archive").Range("E" & LastRow) = Range("D14")
        Sheets("Archive").Range("A" & LastRow) = Range("D18")
        Sheets("Archive").Range("F" & LastRow) = Range("H27")
        Sheets("Archive").Range("G" & LastRow) = Range("D27")
 
'Date and time of the archiving
        Sheets("Archive").Range("H" & LastRow) = Now
        

'Clears out the input data in the calculator but does not touch the calculated cells in the calculator
        Range("C10").ClearContents
        Range("E10").ClearContents
        Range("J10").ClearContents
        Range("D14").ClearContents
        Range("D18").ClearContents

End Sub

      • 0