Updating data on sheet keeping all historical data

Posted by Meryn on August 08, 2001 6:09 AM

I have one workbook with 3 sheets (a,b & c)
Data input on 'a' creates a form on 'c' which then gets printed - no problem.
I would then like the data input on 'a' to be placed on 'b'.
Each time the data gets put onto 'b' from 'a' - I would like it placed on the next row down.
I have the code for finding the next empty cell in a column - so that my cursor is always in the right position for the next row of input - but do not know the syntax for getting the input placed to the right of this position (always column A).
eg: next available row is r28 - i start my macro and my cursor ends up at a28...
I have twelve fields on 'a' that need to be placed in 'b' at a28, then b28, c28 and so on...
next time the macro is run the cursor gets placed at a29 etc etc ...

apologies for spelling this out - but am just a novice at this (obviously) so need to get it straight in my own mind so that i can give you the correct info.

hope this makes sense...

if i can get this sorted then i have the rest of my speadsheets completed with other simple coding that i have done... any help would be greatly appreciated. thks.

Posted by Cory on August 08, 2001 6:48 AM

I think from what you're saying, the data on 'a' runs down a column and you want it copied to 'b' along a row (as a record would in a database). In the following example my data was on 'a' in cells A1:A5. I made this macro to copy that data to 'b' in the next available row below old data that was already there:

Sub Macro1()
'
' Macro1 Macro
' Macro written 8/8/2001 by Cory
'
' Keyboard Shortcut: Ctrl+q
'
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheet2.Activate
Range("A1").Activate
Selection.End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Range("A1").Select
End Sub

If that's not quite what you're looking for, just clarify:-)

Cory

Posted by Meryn on August 08, 2001 7:51 AM

Thanks I will try that but does this make a difference.....?
The data on 'a' is located in many different cells - a lot of them not located together ( for example - cell refs on 'a' are: b5,6,& 7 then e5 & 6, then d17 etc - they are dotted around 'a' - this is because i have created 'a' as the front end for the user - but you are correct - the data in 'b' runs along a row (as a record would in a database).
Does this affect the code you have given me? :-(

Posted by Cory on August 08, 2001 12:33 PM

Yes, that would deffinetly affect the code, but not too much. There's a "simple" fix that would make the code work:

Add a sheet called "data". In column A make references to all of the cells on your 'a' sheet that you want to transfer to 'b' (to bring them together in one column). Then the following code should work:

Sub Macro6()
Sheets("data").Select
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("b").activate
range("A1").activate
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("A1").Select
End Sub

Did that make sense?

Cory

Posted by Meryn on August 13, 2001 3:00 AM

perfect sense- and works fine - thanks very much once again for your help - the rest i have managed to sort myself.