Transfer Data from a sheet to another sheet dependent on sheet name

[max8719]

Hi Guys,

I need to transfer a row of data from "sheet1" for example B11:P11 to another sheet in the work book. The data in column B11:B170 will define sheet names. So the macro can use this to reference which sheet the row of data needs transferring to. The row of data also need to go into the next available blank row on the specific work sheet and into the same row range eg B11:P11, B12:P12 etc etc.......

This will be assigned to a Active X command button.

Thanks in advance for your feedback.

 

[max8719]

Column B has a drop box function which you can select sheet names would the drop box / data validation conflict with the script?

Also just to get some clarification , will the script avoid making duplicates in the destination sheet.

And is it possible to apply this to a command button click function?

Thanks for your help.

 

[My Aswer Is This]

I would test this script on a small set of data.
Create a sheet named "George" and a sheet named "Bob"
Then put the value "George" in column "B" of row one and put "Bob" in column "B" of row two.
Then change the script to say:
For I= 1 to 2

This will test the script. It works for me.
Data validation should not cause a problem.
If you get this working we will later talk about duplicates.
I like solving one problem at a time.
This script will run from a shape or button or whatever.
For now try running it from the ribbon "View" Macro" section of the ribbon.

 

[max8719]

Hi I have tried this on a new blank excel work book and the error comes up that the script is out of range.

All sheets names and names in column B match. I really hope we can get this working, like yours is.

 

[My Aswer Is This]

And your master data sheet is sheet (1) which is the sheet to the far left on your tab menu is that correct? In your original post you said sheet(1).