Excel macro copy from a single sheet to different sheet

Jyotirmaya

Board Regular
Joined
Dec 2, 2015
Messages
192
I am using the below code to copy data from a sheet to different sheets if the sheet name matches with text of column E

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

I have data in RAW DATA sheet and I am copying data from this sheet to other by using this macro. I have data till column H in RAW DATA sheet, now in a new column added for example if in column H Text is there as "ABC" in any row then I want that row shouldn't be copied to other sheets & it should be copied to sheet name "ABC" instead of the sheet name mentioned in column E, for this what should be the change in the code ??
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,586
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
For rownum = 2 To lastrow
    If ws.Cells(rownum, 8) = "ABC" Then
        ws2name = "ABC"
    Else
        ws2name = ws.Cells(rownum, 5)
    End If
    Set ws2 = Sheets(ws2name)
    lastrow2 = ws2.Cells(ws2.Rows.Count, "E").End(xlUp).Row
    ws.Rows(rownum).Copy ws2.Rows(lastrow2 + 1)
Next rownum
 
Solution

Jyotirmaya

Board Regular
Joined
Dec 2, 2015
Messages
192
Thanks @Fluff

Kindly let me know what to add in code if I need to add a new sheet for text XYZ in row number 8 along with ABC
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,586
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
For rownum = 2 To lastrow
    Select Case ws.Cells(rownum, 8)
      Case "ABC"
         ws2name = "ABC"
      Case "XYZ"
         ws2name = "XYZ"
      Case Else
        ws2name = ws.Cells(rownum, 5)
    End Select
    Set ws2 = Sheets(ws2name)
    lastrow2 = ws2.Cells(ws2.Rows.Count, "E").End(xlUp).Row
    ws.Rows(rownum).Copy ws2.Rows(lastrow2 + 1)
Next rownum
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,586
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,625
Messages
5,659,941
Members
418,538
Latest member
alc51103

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