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
244 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Cheers mate !!!Excellent!!
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
Comment Utility
Thanks for the points...
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
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.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

743 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

22 Experts available now in Live!

Get 1:1 Help Now