Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Spreadsheet data entry sheet.


  • Please log in to reply

#1
Welshhobo

Welshhobo

    Member

  • Member
  • PipPip
  • 29 posts
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.

  • 0

Advertisements


#2
dm27

dm27

    Member

  • Member
  • PipPipPip
  • 272 posts
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
  • 0

#3
dm27

dm27

    Member

  • Member
  • PipPipPip
  • 272 posts
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
  • 0

#4
Welshhobo

Welshhobo

    Member

  • Topic Starter
  • Member
  • PipPip
  • 29 posts
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 :/
  • 0

#5
dm27

dm27

    Member

  • Member
  • PipPipPip
  • 272 posts
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,
  • 0

#6
dm27

dm27

    Member

  • Member
  • PipPipPip
  • 272 posts
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
  • 0

#7
Welshhobo

Welshhobo

    Member

  • Topic Starter
  • Member
  • PipPip
  • 29 posts
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?
  • 0

#8
dm27

dm27

    Member

  • Member
  • PipPipPip
  • 272 posts
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
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP