[Welshhobo]

For a school project i'm working on, i have to create a spreadsheet for holding customer details and financial details for some imaginary company. What i need to do is basically have a data entry form on one worksheet, and a button on that worksheet that'll cut+paste all the data thats been entered, into a list of past entries that have been made.

What i basically need is a code that'll take the contents of say cell C11 from Worksheet1, and place it into cell G4 in Worksheet2, but the next time run the code, it'll take the contents of cell C11 and place it into G5, then G6, then G7 etc...

Thanks in advance.

Edited by Welshhobo, 08 March 2010 - 04:23 PM.

[Advertisements]

Hello Welshhobo,

I have some VBA code that will do the copy paste to next blank line portion...not sure how it would apply within a UserForm and button though.

Once I locate it I will try to post it during my lunch break and perhaps it will give you a starting point.


Sincerely,

dm

[dm27]

Hello Welshhobo,

I have some VBA code that will do the copy paste to next blank line portion...not sure how it would apply within a UserForm and button though.

Once I locate it I will try to post it during my lunch break and perhaps it will give you a starting point.


Sincerely,

dm

[dm27]

This is the code I was referring to:

Sub Test()

'will copy selected range from sheet one and paste on the next emply line in sheet two

' change the range as required
Range("A2").Select
Selection.Copy

' change sheet name as required
Sheets("Sheet2").Select

' change destination cell (range) as required
Range("A2").Select

' this line ensures the data is copied to the next empty line
Range("A65000").End(xlUp).Offset(1).Select

Selection.PastSpecial Paste:=xlPasteAll, Operation:=xlNone, Skip Blanks:=False

End Sub

I'm sure there are better ways to do this, but I thought it might be helpful. Please note, it has not been tested from a UserForm.

Best regards,

dm

[Welshhobo]

I've tried putting the same code into both a command button and a normal button, but neither work :/

I keep getting a syntax error from the last line

Selection.PastSpecial Paste:=xlPasteAll, Operation:=xlNone, Skip Blanks:=False

I really can't understand why it would be giving me a syntax error :/

[dm27]

Hello Welshhobo,

That does sound odd, let's see...

A couple options:


1. Keep the line and remove this portion , Operation:=xlNone, Skip Blanks:=False and then try again.

2. Comment out the entire line, and then replace it with

ActiveSheet.Paste


Let me know if that is successful and if I have some time later I'll try to test it from a button.


Sincerely,

[dm27]

Greetings once again,

I found the reason for that syntax error (apparently I can't type or copy correctly today)

The original line below has two errors:
Selection.PastSpecial Paste:=xlPasteAll, Operation:=xlNone, Skip Blanks:=False

The correct line should be below (note differences in red):
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False

I tried with a Button and it seems to run fine...though additional fine tuning may still be needed.

dm

[Welshhobo]

Thanks very much dude, work perfectly

Was wondering why it wasn't working at first and it was because it was on a command button and not a regular button.

Out of curiosity, do you know the difference between the two? One is for macros obviously, but why are there two? and why do they influence how certain codes run?

[dm27]

Hello again,

You're welcome and I'm glad I could help you out...

At the moment I really haven't researched the differences between a button created from the Forms toolbar or theControl toolbar.

Good luck with your studies and enjoy the forums.

Best regards,

dm