Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel macro to add a new worksheet and cut and paste few rows from existing worksheet to rthe new one.

Posted on 2011-03-01
5
Medium Priority
?
255 Views
Last Modified: 2012-05-11
Hi,
I have a excel worksheet with columns 'PI_AGECAT' and 'pg'
I need to write a excel macro to create a new worksheet named "Cu_Agecat2.
Cut rows from worksheet1 where column 'PI_AGECAT' changes its value and paste it in new worksheet named.
example:
worksheet1

PI_AGECAT    pg
under50         34
under50         15


new worksheet named "Cu_Agecat2

PI_AGECAT    pg
under55        4
under55        1


Cheers



0
Comment
Question by:RIAS
  • 3
  • 2
5 Comments
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 35006306
Dear RIAS
This might do...
You can change column number to match your needs.
If you need it I can search for columns with tiltle PI_AGECAT .. just ask..

Sub CU_Agecat()
Dim sht As String
Dim i As Integer
Dim PI_Agecat(1000, 2) As Variant
Dim mem As Variant
i = 1
    sht = ActiveSheet.Name
    mem = ""
    Do While Cells(i, 1) > ""
    If PI_Agecat(i, 0) <> mem Then
        PI_Agecat(i, 0) = Cells(i, 1) '1 = columns A
        PI_Agecat(i, 1) = Cells(i, 2) '2 = column B
        mem = PI_Agecat(i, 0)
    End If
    i = i = 1
    Wend
   
    Sheets.Add
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "CU_Agecat2"
    i = 1
   While PI_Agecat(i, 0) <> ""
        Cells(i, 1) = PI_Agecat(i, 0)
        Cells(i, 2) = PI_Agecat(i, 1)
        i = i + 1
    Wend
   
End Sub


Kind regards

Eric
0
 

Author Comment

by:RIAS
ID: 35006362
Hi,
How do I copy the columns names from the sheet1 to the newly added worksheet2.

Cheers
0
 
LVL 6

Accepted Solution

by:
Eric Zwiekhorst earned 2000 total points
ID: 35006404
There was a error in the privious code please use this one.
If your column names are in the first row they are copied automatic..

Sub CU_Agecat()
Dim sht As String
Dim i As Integer
Dim PI_Agecat(1000, 2) As Variant
Dim mem As Variant
i = 1
    sht = ActiveSheet.Name
    mem = ""
    While Cells(i, 1) > ""
    If Cells(i, 1) <> mem Then
        PI_Agecat(i, 0) = Cells(i, 1)
        PI_Agecat(i, 1) = Cells(i, 2)
        mem = PI_Agecat(i, 0)
    End If
    i = i + 1
    Wend
   
    Sheets.Add
   
    ActiveSheet.Name = "CU_Agecat2"
    i = 1
   While PI_Agecat(i, 0) <> ""
        Cells(i, 1) = PI_Agecat(i, 0)
        Cells(i, 2) = PI_Agecat(i, 1)
        i = i + 1
    Wend
   
End Sub
0
 

Author Closing Comment

by:RIAS
ID: 35006481
Cheers mate !!!Excellent!!
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 35006499
Thanks for the points...
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question