Excel macro create new sheet copy & paste cells [Closed]

- - Latest reply: pijaku
Posts
12253
Registration date
Wednesday May 14, 2008
Status
Moderator
Last seen
July 19, 2019
- Jan 29, 2015 at 09:06 AM
Dear Sir
I need help in a macro to create a new sheets with colum A name and copy row cells to the new sheet.

Name Age Gender
XX 25 M
X1 30 F
X2 27 F

So I can get a new sheet with XX name and Name , Age, Gender cell in it.

thanks
See more 

1 reply

Posts
12253
Registration date
Wednesday May 14, 2008
Status
Moderator
Last seen
July 19, 2019
1
0
Thank you
Hi,

How many rows in Column "A"?

Warning, because Excel has limited Sheets's count to 255 max.

Is there duplicates in your column "A"?

Try this with a few data in column "A", this code is the first part of your question :

to create a new sheets with colum A name
Option Explicit

Sub CreateNewSheets()
Dim LastRow As Long, lngRow As Long

Application.ScreenUpdating = False
With Sheets("Feuil1") '------------> ADAPT with your Sheets's name
    LastRow = .Range("A" & Rows.Count).End(xlUp).Row
    For lngRow = 2 To LastRow '-----> Start Row = 2
        If Exist_Sheet(CStr(.Cells(lngRow, 1).Value)) = False Then
            ThisWorkbook.Sheets.Add
            ActiveSheet.Name = CStr(.Cells(lngRow, 1).Value)
        End If
        .Activate
    Next lngRow
End With
Application.ScreenUpdating = True
End Sub

'Test if Sheets already exist
Function Exist_Sheet(strWsh As String) As Boolean
On Error Resume Next
    Exist_Sheet = (Sheets(strWsh).Name = strWsh)
End Function


If all is well for you, we will continue with the second part : copy row cells to the new sheet