Copy and paste from a sheet to different sheet with matching sheet name

Jyotirmaya

Board Regular
Joined
Dec 2, 2015
Messages
192
I have a Excel file with Header from A1 to H1 in sheet "RAW DATA"
In the Column E of RAW DATA sheet I need to filter the data and paste into different sheet.
There are 25 type of data in column E and I have 25 sheets with that matching Text as sheet name
Presently I am copying data from Raw Data sheet and pasting in different sheets.
In other 25 sheets also I have the same header from A1 to H1, I want a VBA code to copy and paste data from A2 onwards in different sheet.

Kindly help with the code
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,907
Try:

VBA Code:
Sub CopyToSheets()

Dim ws As Worksheet
Dim ws2 As Worksheet
Dim lastrow As Long
Dim lastrow2 As Long
Dim rownum As Long
Dim ws2name As String

Set ws = Sheets("RAW DATA")
lastrow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row

For rownum = 2 To lastrow
    ws2name = ws.Cells(rownum, 5)
    Set ws2 = Sheets(ws2name)
    lastrow2 = ws2.Cells(ws2.Rows.Count, "E").End(xlUp).Row
    ws.Rows(rownum).Copy ws2.Rows(lastrow2 + 1)
Next rownum

End Sub
 
Solution

Jyotirmaya

Board Regular
Joined
Dec 2, 2015
Messages
192
Sub CopyToSheets()

Dim ws As Worksheet
Dim ws2 As Worksheet
Dim lastrow As Long
Dim lastrow2 As Long
Dim rownum As Long
Dim ws2name As String

Set ws = Sheets("RAW DATA")
lastrow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row

For rownum = 2 To lastrow
ws2name = ws.Cells(rownum, 5)
Set ws2 = Sheets(ws2name)
lastrow2 = ws2.Cells(ws2.Rows.Count, "E").End(xlUp).Row
ws.Rows(rownum).Copy ws2.Rows(lastrow2 + 1)
Next rownum

End Sub
Thank you so much its working, I have a sheet COUNT and in COUNT sheet from A1 to A25 I have the sheet names, I want to know how much rows copied to each sheets from RAW DATA sheet to different sheets in COUNT sheet in column B1 to B25 against the sheet names. Please help
 

Jyotirmaya

Board Regular
Joined
Dec 2, 2015
Messages
192

ADVERTISEMENT

You're welcome

B1 =COUNTIF(INDIRECT(A1&"!E:E"),"<>")
Thanks its working but I am getting the result +1, for example I have 50 rows then its showing 51.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,617
Messages
5,659,882
Members
418,535
Latest member
Ajith55

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