Solved I need a macro to copy data to an archive sheet

December 10, 2012 at 08:49:37
Specs: Windows 7
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!!!!????


See More: I need a macro to copy data to an archive sheet

Report •


#1
December 10, 2012 at 16:03:20
First, when posting in a Help forum such as this, please try to use a Subject Line that tells us something about your question. If everyone used a generic Subject Line such as "I Need Help With A Macro" we wouldn't be able to tell one question from another and the Archives would essentially be useless.

I have edited the subject line of your post to show you what I mean. Scan the list of questions in this forum to see the types of Subject Lines that others have used.

re: CAN ANYONE HELP!!!!????

Second, please do not use all uppercase when posting. All uppercase is the internet equivalent of yelling and nobody likes to be yelled at. In addition, !!!!???? isn't going to get you an answer any quicker than a using the correct punctuation, a single question mark.

As for your question, we would need more detailed information than you have provided before we could offer any suggestions. Since VBA is very specific in its instructions, we need to where the source data is located, what sheets you are working with, etc. Perhaps a sample of your data would help.

If you are going to post example data, please click on the following line and read the instructions found via that link. Thanks.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 11, 2012 at 05:35:40
Sorry about that this my first post: Thanks for the quick reply,but this is something for work i cant officially post it. Sorry, but i can do more detail. I have a calculator i made that you input data into then it outputs the answer you need for the application. Cells C10,E10,J10,D14,D18 are the input data H27,D27 are the output. I need to copy these inputs and outputs to an archive sheet that will keep records in a certain order. That order is B3,B2,D2,E2,A2 input, then F2,G2 output respectivley. After it copies to these lines I need the macro to drop to next blank row, then zero the input cell and repeat on the next line everytime the button is press. If this helps any let me no i have it to where it copies to all the column 2nd row and move to next blank row but cant get it to repeat on that row and continue when i press the button.


Heres the sample:
Sub Macro9()
'
' Macro9 Macro
'

'
Sheets("Archive").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "=Calculator!R[16]C[3]"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=Calculator!R[8]C[1]"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=Calculator!R[8]C[2]"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=Calculator!R[8]C[6]"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=Calculator!R[12]C[-1]"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=Calculator!R[25]C[-2]"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=Calculator!R[25]C[1]"
Dim BlankRow As Long
BlankRow = Range("A65536").End(xlUp).Row + 1
Cells(BlankRow, 1).Select


End Sub


Report •

#3
December 11, 2012 at 12:02:53
✔ 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



Report •

Related Solutions

#4
December 11, 2012 at 12:30:43
AlteK,
This works absolutely perfect!!! That was awesome!!!. ..Hey, i do want to pick your brain one more time though.Im wondering how i can add a autofilter to Column A on the archive sheet while maintaining everything else.Also yes i did mean a Form button, and i did have a error i notice right after posting..!

Thanks alot
BJELLS


Report •

#5
December 11, 2012 at 12:41:48
Hi Bjells

No worries. I did add something to the macro probably after you tried it out. I've added a date stamp for when the data was archived entered into column H of the Archive sheet. If that's of interest just recopy the macro or just the new line.

Not sure what you mean by "maintaining everything else". If that means you want to see only specific rows of Col A and all rows for all other column - that would be physically impossible.

Have you tried the Filter option in the Data ribbon? does that not do what you need?

Can you clarify what you want a bit more?


Report •

#6
December 11, 2012 at 12:52:47
Sure let me clarify. Many different combos of number can be entered into the calculator in the input cells. But only one cell which is D18 is specfic to your reciepe of numbers that you entered in the input to get your output. Basically a ID # all similar in digits.
I can use the auto filter button but this would have to be done after every "button" push when data is updated into the archive sheet. I was hoping they would be a macro function i could just add in addition to whats already done. "maintaing everything else" i was meaning do everything that the macro does now but when it enters the data into the archive sheet it autofilter from lowest # to highest # for instance..

And i think the date and time would be a great addition also. Look forward to your response


Report •

#7
December 11, 2012 at 13:12:40
Just tossing in some coding options....

All of the cells you are clearing can be done in one line:

Range("C10,E10,J10,D14,D18").ClearContents

Those cells could also be set as a Named Range and cleared as follows:

Range("MyCalc").ClearContents

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
December 11, 2012 at 13:29:28
Hey thanks for that>DerbyDad03, Do you have any ideas on how to add the auto filter function to column A after the updated data from the calculator is added to the archive sheet

Report •

#9
December 11, 2012 at 15:20:06
Hi BJELLS

Sounds like you just want to sort the Archive
data based on Column A which contains the
ID data entered in D18 data. Is this correct?

Here is an updated macro that will sort as
well. This sort assumes that row 1 contains Column headers.

(all I did was do a "record macro" and altered
some of the result to tailor it to this application)

The macro also contains the date/time stamp and .....

the improvement on the ClearContents that DerbyDad03 provided (Thanks for that)

Let me know if this isn't what you were going for.

Sub Archive()

Application.ScreenUpdating = False


'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,E10,J10,D14,D18").ClearContents
   
'Sort on Column A

    ActiveWorkbook.Worksheets("Archive").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Archive").Sort.SortFields.Add Key:=Range("A2:A" & LastRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Archive").Sort
        .SetRange Range("A1:H" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Sheets("Archive").Activate
Range("A1").Select

End Sub


Report •

#10
December 12, 2012 at 04:57:53
AlteK,
That works great, you are the best!! Thanks a lot Im overjoyed. This was my first time trying this site and youll see more from me..
Thanks
BJELLS

Report •

#11
December 12, 2012 at 13:49:06
Hi BJELLS

Glad it helped. I'm pretty new here myself but I'll presume to say that you are welcome back any time. Hope to see you soon.


Report •


Ask Question