• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy Column and create new sheet

coolkiran

Member
Hi to all

I am creating macro application.

I have a sheet with few columns, Below is the format of sheets.

Class Subject Kiran Rahul Mary Susan
X Language1 85 45 60 94
X Language2 80 40 60 95
X Maths 60 42 60 92
X Physics 45 43 60 91
X Chemistry 35 75 60 90
X Biology 60 60 60 92

I need to create new sheet based on column header from Column C, so new sheet will be Kiran, Rahul, Mary and Susan.

And i need data from Column A and B common in all sheets, and respective column. For example Susan sheet will be like below.
Class Subject Susan
X Language1 94
X Language2 95
X Maths 92
X Physics 91
X Chemistry 90
X Biology 92

Any suggestion, I have attached sample file as well.
 

Attachments

  • chandoo.xlsm
    8.9 KB · Views: 1
Why don't you just make a sheet like this?
Class Subject <name, as a dropdown menu>
x Language1 =index(<grades>;match(names;susan);match(subjects))

e.g. starting at b1, you're formula would be:
=index(C3:F7;match(C2:F2;"susan";0);match(B3:B7;"language1";0))
 
Thanks for your reply

I am getting file in that format, like in Input sheet, I need to split to sheets, after that i have few formulas, need to apply individual students, that part completed.

Instead of copy and creating new sheets and pasting is taking some time. So i decided to do some macro, So that macro will create new sheets for all students, then i will add remaining macros to that code.
 
I'm currently at the office and don't have a lot of time to write a full macro.

You can try something like this:

Code:
For i = 1 to NumberofNames
Worksheets.add.name = name(1, i)
Cells(x,x).value = worksheet.cells(xx).value 'change xx to cells you wish to use <subject and class>
Cells(x, x+2) = name(1, i)
Cells(x+1, x+2) = "=index(C3:F7;match(B3:B7;"language1";0);match(C2:F2;"susan";0))
next i
 
I am creating macro application.
Hi,

it's odd people saying creating macro and there is nothing to see !
Where is the code you start to create ?‼ :rolleyes:

At least use Macro recorder offering a code skeleton and,
with a few amending, you will get a final code :​
Code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded the 2015/05/22 by The Noob Simulator 
'
    With Sheet1.Cells(1).CurrentRegion.Columns
        Application.ScreenUpdating = False
        For C& = .Count To 3 Step -1
            Worksheets.Add(, .Parent).Name = .Cells(C).Value
            Union(.Item("A:B"), .Item(C)).Copy ActiveSheet.Cells(1)
        Next
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !
 
Back
Top