Previous Topic Next Topic

Excel macro create new sheet copy & paste cells

Closed
Matar - Jan 29, 2015 at 12:53 AM
pijaku Posts 12263 Registration date Wednesday May 14, 2008 Status Moderator Last seen January 4, 2024 - 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

1 response

Answer 1 / 1
pijaku Posts 12263 Registration date Wednesday May 14, 2008 Status Moderator Last seen January 4, 2024 1
Jan 29, 2015 at 09:06 AM
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
0