Command Button Paste To Selected Worksheet By Cell Reference

[Shortbred]

Hi Guys,

I am just wondering if there is anyway to select the paste sheet as a cell reference. I am very new to all this have the code below to copy from sheet1 and paste to sheet2 in the next available free row. Is there anyway to be able to distinguish the paste sheet by cell refence? For instance if I put "Sheet2" in Sheet1 Cell H2 it would go to the worksheet called Sheet2 but if I changed it to "Sheet3" it would go to Sheet3 etc? I could make a ton of buttons which would do the same job but I am sure there must be an easier way? Any help greatly appreciated! Current code below.

Regards,
Jon

Please Login or Register  to view this content.

[Mumps1]

By what you describe, it sounds like you have many sheets and you want to specify which sheet will be the destination sheet by putting the sheet name in cell H2. Is this correct? Will the range to be copied always be A2:A10?

[Shortbred]

Hi Mumps,

Yes this is correct. I want to use one button and use H2 to specify the destination sheet. At the moment I would have to create say 10 different command buttons to manually code each destination sheet.

Regards,
Jon

[Mumps1]

I think the easiest way to this would be to insert a drop down list of all the sheets except for Sheet1 in a drop down list in H2. All you would have to do is make a selection in the drop down list and the range A2:A10 in Sheet1 would be copied to the selected sheet. Would this work for you?

[Shortbred]

That would be fine as long as I could press something to confirm it should be sent. What I am effectively trying to do in my code is set the paste sheet to Sheet1 Cell H2. If you think it would be more efficient by using a drop down list I am more than happy to take your advice if you could point me in the right direction. The only time I have really used drop downs is in data validation in dashboard for formula reference.

Set pasteSheet = Worksheets("Sheet1CellH2")

Regards,
Jon

[Mumps1]

confirm it should be sent

By this I assume that you mean if the range should be copied/pasted. Is this correct?

[Shortbred]

Yes. So almost like selecting the sheet and then pressing go.

[nankw83]

Assuming your range is fixed from A2:A10 & destination sheet name in cell H2, try the below code

Please Login or Register  to view this content.

[Shortbred]

Hi,

Thanks for the input. My range is actually from F7:K21 & Sheet1 (Where the button will be is currently named "Front" to be pasted in columns A-F so I amended the code to that below and it didn't like it.. Unsure if it is because I haven't edited correctly.

Private Sub CommandButton1_Click()

Sheets(Sheets("Front").[H2].Value).Range("A:F" & Rows.Count).End(3).Offset(1).Resize(9) = Sheets("Front").[F7:K21].Value

End Sub

Regards,
Jon

[Mumps1]

Copy and paste these macros into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Click on H2 and make a selection.

Please Login or Register  to view this content.

This assumes your "Front" sheet is the first sheet.

[nankw83]

Unsure if it is because I haven't edited correctly

Try this ...

Please Login or Register  to view this content.

[Shortbred]

Try this ...

Please Login or Register  to view this content.

Still not liking this one either.

[nankw83]

What happens ? Are you getting an error ? Can you post a small sample workbook ?

[Mumps1]

Have you tried the code I suggested in Post #10?

[Shortbred]

Have you tried the code I suggested in Post #10?

This one works. Thank you so much! Is there a way to ignore the first 4 tabs instead of just the first one?

[Shortbred]

What happens ? Are you getting an error ? Can you post a small sample workbook ?

This one is also working now. Brilliant. I just had to change the Range to A that's all. Thanks for your help See code below:

Private Sub CommandButton1_Click()

Sheets(Sheets("Front").[H2].Value).Range("A" & Rows.Count).End(3).Offset(1).Resize(15, 6) = Sheets("Front").[F7:K21].Value

End Sub

[nankw83]

I was just re-reading your post #9 & was going to suggest the same ... Glad you got it figured out. You have 2 options now

If that takes care of your original question, please mark this thread as [SOLVED] from the Thread Tools above your first post & consider Add Reputation to those who have helped by clicking the star icon under their usernames

[Mumps1]

Replace this line of code:

Please Login or Register  to view this content.

with this one:

Please Login or Register  to view this content.

[Shortbred]

Top work guys. Thank you very much.

[nankw83]

Glad to help .. Thank you for your feedback & for the add Rep.