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
245 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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now