Add, edit and clear macro buttons between sheets

[chris1089]

Hi Guys

Conditions
- I want to select a name from the data validation list in sheet 1. Once the name is selected, I want to paste data into sheet 1’s range and “add” (paste) it using a macro button to the name’s respective sheet, month and destination.
- I also want to be able to “edit” the data from the output sheets in the input sheet and “update” them.
- I also want to be able to clear the selected range in the input sheet.
Hope this is clear

Summary:
I have two excel sheets. Sheet 1 is acts as an input sheet (pasting raw data). Sheet 2,3 and 4 are for collecting raw data from Sheet 1.

Sheet 1 input
This sheet includes a validation list which enables me to select a specific person so as to enter the miles they travelled in each destination during specific months. All of the people (names) share the name destinations.
Validation list Cell: assume its “A4”
Pasting range: assume B9:M13 this is the selected range as shown below

Sheet 2 (Mike’s output sheet)
This would be Mike’s input sheet
The other people will have their own unique sheets for collecting data pasted from sheet 1.
“In this case B9, B10 and B11 Data in sheet 1 will be pasted into B3, C3 and D4 in Mike’s output sheet using the add button since Mike’s name is selected. “

sample.xlsx

[sourabhg98]

Hello
I am working on your question...but this is quite long so please check the answer tomorrow if I am not able to complete today
Regards
Sourabh

[chris1089]

Thanks you very much Sourabhg98. I am looking forward to your response

[sourabhg98]

Hello
According to what I understood from your problem I have made the changes...
So what I have made is two buttons....
One is the Go button>>
Go button copies the values from the user you have selected in the input sheet. If you select Paul and press Go button then it will bring the data from Paul's Sheet to the main Input sheet.....Note: The sheet name must be exactly like this..... "User's Name" Space "Output" like Mike Output is correct but MikeOutput is incorrect.

Now the second button is update button
It takes the values from the main input sheet to the sheet of the user you have specifies. Like if you select Mike and press Go ....data will come in Input Sheet....now you can make any changes whether it is delete or add values.......after that just click on update button to update the values in the User's sheet....

Hope it helps....
Regards
Sourabh

If you are satisfied then mark the thread as solved and you can just click on ADD REPUTATION below my post to say thanks...by doing this you add to my reputation…...

[sourabhg98]

Lol forgot to attach the sheet....
Here's it.....
One more thing....I have used the cell P1 in the input sheet to create a cell reference for the macros

[chris1089]

Thanks sourabhg98!! I have a few questions to ask:

- How do I expand the name selection and country list so as to include in the macro? (I am familiar with data validation however i am not familiar with coding)
- Could you walk me through how you created the macro code? I am specifically interested in and impressed by how you specifically selected particular names using the drop down list. The reason why i am asking is because i am going to try to imitate the same thing you did in the same workbook.

I cant thank you enough for the learning lesson. I am looking forward to your response

[sourabhg98]

Ohk so let me explain
This is the macro for the Go button

Sub Bevel1_Click()
'
' Bevel1_Click Macro
'

'
Range("B9:M13").Select
Selection.Copy
<<this copies the range B9 to M13>>
Sheets(Range("P1").Value).Select
<<this selects the sheet name which is there in the cell P1>>
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
<<pastes the data copied from input to the user's sheet selected>>
Application.CutCopyMode = False
Sheets("sheet 1 input").Select
<<finishes pasting and comes back to input sheet>>

End Sub

Now the Update Button>>>

Sub Bevel3_Click()

Sheets(Range("P1").Value).Select
Range("B3:M7").Select
Selection.Copy
<<selects the sheet in P1 and copies B3:M7>>

Sheets("sheet 1 input").Select
Range("B9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
<<Pastes the copied value in the input sheet>>
End Sub

If you are satisfied then mark the thread as solved and you can just click on ADD REPUTATION below my post to say thanks...by doing this you add to my reputation…...

[sourabhg98]

So if you want to increase the countries you just need to change the copy range in the macros....
You can add any number of User's then there's no problem.....it will work for any number of users provided that we have the sheet name of the user as ..>>"User's Name" Space "Output"

[chris1089]

The post was has been solved