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

Muhammad Hussaan

New Member
Joined
Dec 13, 2017
Messages
47
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,181
Office Version
  1. 2016
Platform
  1. Windows
Assuming the sheets listed on the Summary sheet actually exist :
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
    Sheets(Format(cel, "@")).[A5].Value = cel(1, 2)
Next
 

Muhammad Hussaan

New Member
Joined
Dec 13, 2017
Messages
47
Office Version
  1. 2013
Platform
  1. Windows
Assuming the sheets listed on the Summary sheet actually exist :
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
    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

Well-known Member
Joined
Sep 21, 2016
Messages
3,181
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

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

New Member
Joined
Dec 13, 2017
Messages
47
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Dec 13, 2017
Messages
47
Office Version
  1. 2013
Platform
  1. Windows
The following code does not do any sorting.
I have assumed you can sort the Summary sheet before running the code

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,217
Messages
5,594,887
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top