Assistance on VBA code to extract data

[Sway1978]

I have created a database and a userform . I have two sheets, Sheet 2 has a command button the opens up the userform and Sheet1 records that data that is entered into the form. I want to add a command button for the end users to press and it extracts the data entered onn the form and will place onto an existing workbook saved on my desk top. The command button to extract the data has been added to the form already and I have labled it "ReportButton". I need assistance. the code i attempted to use didnt function.

[Alf]

Uploading the file makes it easier to help you.

Alf

[Sway1978]

My apologies - here is a blank template I have been playing with.

blank tester.xlsm

[Alf]

Ouch! It seems I bite off more than I can chew but I’ll do my best and hopefully other forum members will join in.

Alf

[Sway1978]

No worries- everything functions perfectly - except the blank command button on the user form. Ideally the end user will click the blank button and it will extract all the data that has been entered onto Sheet 1 and either auto send to a new untitled workbook or send to outlook as an attachement, either way would work. Everything I have attempted to write hasnt functioned. So, any assistance I can get will be amazing.

[Alf]

Ok I'm going to give it a try so here we go.

Please Login or Register  to view this content.

The part I'm unsure about is the "Private Sub ReporttButton_Click" is this the right name?

The line "Offset(1)" removes the heading when copying from Sheet1. If you do wish to include heading when you copy remove the "Offset(1)" part.

You will also need to change the path and file name to your needs.

Alf

[Sway1978]

Sadly, I have altered and inserted the code and when I click the command button it does nothing.

[Alf]

Strange I got it to work.

Did you set it up like this?

Right click on tab sheet2.

Click on “View code”

Find “Forms” and below that there is a small square named “DinnerPlannerUserform”

Right click on this and click on “View code”

In the right hand window scroll down until you find “Private Sub ReporttButton1_Click()”

Insert this part of the code after changing "C:\Users\05n\Desktop\Book1.xlsm" between "Private Sub ReporttButton1_Click()”
and "End Sub"

Please Login or Register  to view this content.

To test click on the black square in the userform in Sheet2.

Alf

[Sway1978]

Alf it worked perfectly this time, how odd. Thank you so much for your assistance with this. I was pulling my hair out trying to get the code to work. of course I am bald now LOL thanks again

[Alf]

worked perfectly this time, how odd

Could it have been the name? I guessimated "Private Sub ReporttButton_Click()" but the real name was “Private Sub ReporttButton1_Click()”. Oh well no matter as it works now.

Since you problem is solved could you please mark you thread "Solved"

Alf

Ps

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save