Need a macro for excel 2007 to save data

February 19, 2011 at 10:32:00
Specs: Windows XP
I need some help with a macro. I need to save data from certain cells in 1 worksheet and to another worksheet. Details below
I want to grab the data in worksheet "survey" from cells A3, A4 and A6 and save them to worksheet "data" in cells A2, B2 and C2. After the save is done I then want to clear the data from cells A3, A6. I created an object button in my workbook that is clickable. I just need to assign a macro to it. I have been working on this for over a week and I am new to macros so it is a struggle. I have even went as far as purchasing a program call teachmemirosoftoffice and still can't figure out the whole thing. I get portions of it to work but not all of it. I would greatly appreciate anyone who can help me write this macro. I would forever be indebt to you my friend. Thanks.

See More: Need a macro for excel 2007 to save data

Report •


#1
February 19, 2011 at 11:08:48
Since you do not appear to have a contiguous range of cells in your source document, you have to do each cell individually. A contiguous range could have been copied and pasted Transposed into your destination, but since you skipped A5, you can't use that method.

Try this:

Sub CopyThenClear()
Sheets("data").Range("A2") = Sheets("survey").Range("A3")
Sheets("data").Range("B2") = Sheets("survey").Range("A4")
Sheets("data").Range("C2") = Sheets("survey").Range("A6")
Sheets("survey").Range("A3, A6").ClearContents
End Sub

re: "I would forever be in debt to you "

Forever is a long time to be indebted for a few lines of VBA code. A simple thanks and a promise to pass it forward will suffice.

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


Report •

#2
February 19, 2011 at 16:41:26
Thank you sir, as i read your code I realized i let a piece of information out and I apologize. Everytime I click the save button and it saves the data to the other sheet it needs to go to the next empty row. Can you add a line for that? Thank you very much.

Report •

#3
February 19, 2011 at 17:21:31
Sub CopyThenClear()
'Determine next empty row in data sheet
  nxtRw = Sheets("data").Range("B" & Rows.Count).End(xlUp).Row + 1
'Copy cells
  Sheets("data").Range("A" & nxtRw) = Sheets("survey").Range("A3")
  Sheets("data").Range("B" & nxtRw) = Sheets("survey").Range("A4")
  Sheets("data").Range("C" & nxtRw) = Sheets("survey").Range("A6")
'Clear A3 & A6
  Sheets("survey").Range("A3, A6").ClearContents
End Sub

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


Report •

Related Solutions

#4
February 20, 2011 at 06:43:42
Thank you very much, it works great and now I see what I was doing wrong. You heped me and taught me, again thank you.

Report •

#5
February 23, 2011 at 05:56:08
Derby, I greatly appreciate your help. My macro has been running now for awhile and everything is working great! An issue came up though as they always do. Is it possible to add some lines of code into that macro or another module that based off of time it runs the macro. So basically what I have found is my employees are forgetting to click the save data button that runs the macro and captures all that data and save it. I am losing all my data because of that so the shift data we had is gone. So is it possible to write another macro that at specific times it runs the other macro you wrote. In other words at 6pm it runs the macro you wrote that saves my data and clears the cells and makes it ready for the next shift. Then at 6am the macro runs again saving the data and clearing the fields for that shift and then back and forth, every 12 hrs it runs. I hope sir I am not asking for to much and I greatly appreciate your help.

Report •

Ask Question