Split Data from One Sheet to Different Sheets using VBA

[Haree]

Hello,
I have various sheets in excel approximately 30 to 35 sheets, basically one for each SKU i have a main sheet in which i will enter that days sales or purchase for all the SKU'S they should get posted in their respective sheets. I am attaching a sample sheet, Kindly guide me on how to do the same.
Thanks in advance

New Stock Try.xlsx

drive.google.com

 

[My Aswer Is This]

I like helping on questions like this but I never linking to excel sheets.
If you would provide specific details like in what column will we find the sheet name I may have a Vba script for you

 

[Haree]

Hello, Thank you for taking your time to help me i am attaching a screenshot for your reference. Column B in sheet 1 has the Sheet name details

 

[My Aswer Is This]

So in Sheet named Master
You have sheet names in column B
And the entire row will be copied to the proper sheet is that true.
I like using sheet names like Master
Sheet 1 may mean the first sheet or may mean a sheet named Sheet1
I think it best to use specific names
So on sheet Master we will start on row 2 or row 1
I have a hard time understand the image
do you want whole row copied over or just data in column A?

 

[Haree]

Okay so let the first sheet be named Master
in the sheet named master i have 5 columns
Col 1 Date
Col 2 Stock Name
Col 3 Purchase/Sales
Col 4 No of Pieces
Col 5 Weight

The column 2 will have the sheet names

The Second Sheet is "Stock 1"
in the sheet Stock 1 i have 8 columns
the columns from A to D are for Purchases
the columns from E to H are for Sales

lets take a example

Sheet named Master

Col 1: Date : 10/01/2020
Col 2: Stock Name: Stock 1
Col 3: Purchase
Col 4: No Of Pieces
Col 5: Weight

in the above example the second column is stock 1 so i want the data in the sheet stock 1
in the sheet stock 1
col A is Sl.No
Col B is Date: over here i want the date from Sheet"Master" col 1
Col c is No of pieces: over here i want the pieces from Sheet "Master" col 4
Col D is Weight : over here i want the weight from sheet "Master" col 5

Please Note i have used columns A to D in sheet "Stock 1" because col 3 in sheet "Master" was Purchase if the same was Sales i would have used columns E to H in the sheet "Stock 1"

Hope i was clear, sorry not really good in explaining
thanks

 

[Haree]

Help on the above situation will be very helpful, if i have not explained correctly. This can also be tried.

i will Have approximately 35 sheets

1. Master Sheet ( sheet where i will consolidate all the data )
2. Data sheet ( sheet where i will be updating details daily)
3 to 35. SKU'S namely SKU 1 SKU 2 SKU 3 and so on till SKU 33

Sheet"Data Sheet" will have 5 columns
Column 1 : Item Name (basically the sheet names like Eg SKU 2)
Column 2: Date
Column 3: Particulars ( a dropdown list with two options ie, Purchase or Sales)
Column 4: No of Pieces
Column 5: Weight


Now in Sheet "SKU 2" i will have 4 columns
Column 1 : Date
Column 2 : Particulars
Column 3 : No of Pieces
Column 4 : Weight

once when i run the macro all the data in the sheet " Data sheet" having item name as SKU 2 should be reflected in sheet "SKU 2" and respectively.
i will be updating the sheet daily and it should get updated with the existing data.

THanks a lot

 

[My Aswer Is This]

I'm back again.
You mentioned this:
1. Master Sheet ( sheet where i will consolidate all the data )
But I see where that has noting to do with the script.

So in a sheet named "Data Sheet"

You have sheet names in column A starting in row 2

So we need to have a script copy each row of data to the sheet named in column A

So if in Range("A2") of the sheet named "Data Sheet" you have "Alpha" this row of data will be copied to a sheet named Alpha

Is that correct?

And if in Rage("A3" of sheet named "Data Sheet" you have "Bravo" this row of data will be copied to a sheet named Bravo
Is this correct?

And we keep going down Column A till we have no more sheet names in column A
Is that correct?
And the sheet is named "Data Sheet" Not just "Data"
These specifics are important

 

[Haree]

Hello Thank you.
Ya everything you mentioned above is perfectly correct and yes the sheet itself is named as "Data Sheet"
I will be updating the sheet also daily, Which means i dont want the old data in sheet alpha and bravo to be deleted, i want it to just be added with the old data

 

[My Aswer Is This]

Try this:

Sub Copy_Rows()
'Modified 3/14/2020 4:54:08 AM EST
Application.ScreenUpdating = False
Sheets("Data Sheet").Activate
Dim i As Long
Dim Lastrow As Long
Lastrow = Sheets("Data Sheet").Cells(Rows.Count, "A").End(xlUp).Row
Dim Lastrowa As Long
For i = 2 To Lastrow
Lastrowa = Sheets(Cells(i, 1).Value).Cells(Rows.Count, "A").End(xlUp).Row + 1
Rows(i).Copy Sheets(Cells(i, 1).Value).Rows(Lastrowa)
Next
Application.ScreenUpdating = True
End Sub

 

[Haree]

Hello, Thank You so much it worked like a charm, A small help, column 1 from data sheet is also copying i would be very grateful if that doesn't copy. A small request is there a way a message pops up if i am copying the same data twice ??