Copy data from one sheet to multiple sheets based on sheet name in a column using vba

[Muhammad Hussaan]

I want to copy data from one sheet name "summary" to multiple sheets name 1,2,3 upto 30.
For example in summary sheet column 1 i have sheet names and daily sale in column 2
Column 1 Column 2
Sheet name daily sale
1 100
2 200
3 300
4 400

From summary sheet i want to move/copy data to the respective name sheets cell A5. Like 100 to sheet 1 in cell A5, 200 to sheet 2 in cell A5 and so on..
Kindly help me for this. Thank you in anticipation.

 

[footoo]

Assuming the sheets listed on the Summary sheet actually exist :

Dim rng As Range, cel As Range
Sheets("Summary").Activate
Set rng = Range([A2], Cells(Rows.Count, "A").End(xlUp))
For Each cel In rng
    Sheets(Format(cel, "@")).[A5].Value = cel(1, 2)
Next

 

[Muhammad Hussaan]

Thank you. It works fine

 

[Muhammad Hussaan]

Assuming the sheets listed on the Summary sheet actually exist :

Dim rng As Range, cel As Range
Sheets("Summary").Activate
Set rng = Range([A2], Cells(Rows.Count, "A").End(xlUp))
For Each cel In rng
    Sheets(Format(cel, "@")).[A5].Value = cel(1, 2)
Next

If i have more than one entry for each sheet
For example
ColumnA Column B Column C Column D
Sheet name employee code unit sold target
1 200 100 125
1 202 90 125
Similarly multiple entries for sheet name 2,3 upto 30.
I want to transfer the data to the sheets according to the sheet names in column A. The data should start in respective sheet name from cell B90 and continues to the next rows depending on the no of enteries of that sheet name in the summary sheet and arrange in ascending order with respect to employee code.
Kindly help me for this.

 

[footoo]

and arrange in ascending order

The following code does not do any sorting.
I have assumed you can sort the Summary sheet before running the code

Dim rng As Range, cel As Range
Sheets("Summary").Activate
Set rng = Range([A2], Cells(Rows.Count, "A").End(xlUp))
For Each cel In rng
    With Sheets(Format(cel, "@"))
        If .[B90] = "" Then
            .[B90:D90].Value = cel(1, 2).Resize(, 3).Value
        Else
            .Cells(Rows.Count, "B").End(xlUp)(2).Resize(, 3) _
                .Value = cel(1, 2).Resize(, 3).Value
        End If
    End With
Next

 

[Muhammad Hussaan]

Thank you very much for the help.
It is working as i need.

 

[Muhammad Hussaan]

For vice versa
If i want to copy from multiple sheets to a sheet name summary. The multiple sheets has same format. In each sheet i have a range B6:G18 and B20:G30 and i want to copy from the sheets name 1,2,3,4 upto 30 to summary sheet starting cell A40. Also the range B6:G:18 in evey sheet the macro must only pick the rows with filled cells in column B if empty leave it and keep in accending order base on sheet names 1,2,3,4 to 30.
Kindly help.

 

[Muhammad Hussaan]

The following code does not do any sorting.
I have assumed you can sort the Summary sheet before running the code

Dim rng As Range, cel As Range
Sheets("Summary").Activate
Set rng = Range([A2], Cells(Rows.Count, "A").End(xlUp))
For Each cel In rng
    With Sheets(Format(cel, "@"))
        If .[B90] = "" Then
            .[B90:D90].Value = cel(1, 2).Resize(, 3).Value
        Else
            .Cells(Rows.Count, "B").End(xlUp)(2).Resize(, 3) _
                .Value = cel(1, 2).Resize(, 3).Value
        End If
    End With
Next

The above code now i use in a another file having multiple sheets but for that workbook it only copy single entry for each sheet although the summary sheet has multiple entries for each sheet name.
What i am missing? Kindly help.