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.

 

[Smitty]

From a reporting standpoint, it's a whole lot more difficult to summarize if you've split up your data, especially when you can just use a Pivot Table(s) for reporting.

Unless you have a compelling reason, it's generally better to keep your data consolidated.

 

[My Aswer Is This]

Can we just copy over the entire row? Or must it be only columns B to P?
And if only B to P where on the sheet do you want it pasted? To columns 1 to 16 or columns B to P
Do you want just the values or all the formatting and the formulas?
And we can do this all the way down to the last value in column "B"?
Be sure and answer all the questions please

 

[max8719]

Values only please. There are no formulas in these sheets.


So lets say the sheet1 is the master data base and the data starts on row 11 from column B to P and at the end there is a command button that once clicked the data will be copied into the sheet named in B11, and into the next available clear row below row10. The destination sheet has the same design so copying the whole row should not be an issue.


For example on sheet1 the master data base row 12 will be the same and row 13 etc also row data will be populated gradually and copy/transferred gradually. If this could be run on the whole range in the master data base B11:P171 without creating duplicates in the destination sheets after running the code more than once, this would be super as there will only be one button instead of 171 buttons.

 

[max8719]

Just to clarify row 12 will have the same code not data

 

[My Aswer Is This]

Try this script:

Sub Copy_To_Sheets()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Sheets(1).Activate
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
    For i = 11 To 170
        Lastrowa = Sheets(Cells(i, 2).Value).Cells(Rows.Count, "B").End(xlUp).Row + 1
        Range("B" & i & ":P" & i).Copy Destination:=Sheets(Cells(i, 2).Value).Cells(Lastrowa, 2)
    Next
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

I posted my code before seeing your last post. Tell me if this works for you. Now if you want to run the same code later again it will copy all the data over a second time. If you don't want that I will have to modify the script.

 

[My Aswer Is This]

Also normally we would write scripts to go from like row 11 to Lastrow in column "B" but you said "170" so I wrote it that way. I can change that if you want.

 

[max8719]

Hi,

I have tried to run the code but it says the script is out of range. Also I am looking to increase the range of rows, lets say to 300 so that there is no need for the user to add more rows on their particular sheet.

 

[My Aswer Is This]

The script looks down column "B" from row 11 to 170 for sheet names.
If any of the values in column "B" are not correct sheet names then you may get an Error.
If you want to change the rows it searches change:
For i = 11 To 170
To whatever you like.
But you must ensure the value in column "B" must be a proper sheet name spelled exactly correctly.