• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

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

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
RIAS
Asked:
RIAS
  • 3
  • 2
1 Solution
 
Eric ZwiekhorstTSE service engineerCommented:
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
 
RIASAuthor Commented:
Hi,
How do I copy the columns names from the sheet1 to the newly added worksheet2.

Cheers
0
 
Eric ZwiekhorstTSE service engineerCommented:
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
 
RIASAuthor Commented:
Cheers mate !!!Excellent!!
0
 
Eric ZwiekhorstTSE service engineerCommented:
Thanks for the points...
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now