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
250 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

617 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