• 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.

VBA CODE : Copy data and paste

Monty

Well-Known Member
Hello Experts.


Please find attached for Excel file which is dummy data tried to create prototype.

I have the following requirements
Steps
1) Insert a column after B column and name it as "Metrics
2) paste with the text Volume.
3) Copy A& B column append after the row and fill "Metrics column with text "Controls"

For detailed information i have mentioned in excel book raw data and out required...

Please help..
 

Attachments

AlanSidman

Active Member
this is quick and easy task using Power Query. First, here is the MCode extracted from PQ

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Name", type text}, {"Volume-1", Int64.Type}, {"Volume-2", Int64.Type}, {"Volume-3", Int64.Type}, {"Controls-1", type number}, {"Controls-4", type number}, {"Controls-6", type number}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date", "Name"}, "Attribute", "Value")
in
    #"Unpivoted Columns"
the actual steps
1. Highlight your table/range and select From Table on the Data Tab--Get and Transform.
2. Highlight in PowerQuery all columns except the first two.
3. Select Transform and then right click and select Unpivot
4. Load and Close your PQ. Data will be in your spreadsheet.
 

Monty

Well-Known Member
Thanks your time...But unfortunately we do not have pivot query at office...so trying with simple VBA solution.
 

Monty

Well-Known Member
Monty
Did You give clear rules for C-column texts
... gotta to guess?
... something like this with VBA?

You can add as many 'three sets' there as needed.
Hello Vletm.
Hope you are doing good.

Am unable to download the file and see as am on mobile...could you place the code here..

Will definitely come back to you with more info...


Thanks..
 

AlanSidman

Active Member
Power Query is a free download for 2010, 2013 and is already included in 2016. It is the new key to transformation of worksheets and companies that don't have it available to users are missing much.
 

Monty

Well-Known Member
Hello Vletm.

Thanks for your efforts...It's working fantastic..

But i was little in hurry providing you the data which is correct with small modification....Your code working like magic.

Am attaching with the number of columns actually existing in the working file and requirement....It's small amendment need from you...which will be great help.

Please note there is nothing to do with headers actually but dynamically should pickup the data as required.

Thanks a Ton
 

Attachments

vletm

Excel Ninja
Monty
hmm? ... with small modification... hmm?

Are there always those 27 columns?
Are there always those 9 rows data?
Are there always those three 'terms'' in J-column?
Now You left those Q:AB-columns filled ... hmm?
Are those sheet-names fixed?
What/where would be something 'dynamically'?

If You were little hurry ...
Why You couldn't at once this different the final layout?
... so I should do this twice ... hmm?

Where is that small amendment need from you,

I can notice that almost everything should do from zero ... hmm?
 

Monty

Well-Known Member
Are there always those 27 columnss
Yes
Are there always those 9 rows data?

Rows are not fixed that was only test purpose goes in thousands.

Are there always those three 'terms'' in J-column?

Yes

Now You left those Q:AB-columns filled ... hmm?
Ya with some stuff which is only test purpose

Are those sheet-names fixed?

No will have only tab...for our understanding I just divided.

What/where would be something 'dynamically'?

May be ..I thing column number if data moves then should be in position ifentfy which columns to copied...fingures crosses.


If You were little hurry ...
Why You couldn't at once this different the final layout?

... so I should do this twice ... hmm?

Ofcouse you been helping since long time...am sure we almost there..

Thanks..
 

vletm

Excel Ninja
#4 ... some stuff which is only test purpose ...
hmm? without those ... there are nothing to copy nor move!
1st You wrote about 'copy' and meant 'move' ... now ... which one?
#5 There should be fixed names ...
#6 A challenge ... how to make 'maybe' with Excel?

..am sure we almost there..
Where?

You missed too ...
... so I should do this twice ... hmm?
Where is that small amendment need from you,
 

Monty

Well-Known Member
#4 ... some stuff which is only test purpose ...
hmm? without those ... there are nothing to copy nor move!
1st You wrote about 'copy' and meant 'move' ... now ... which onw

Its only and paste

#5 There should be fixed names ...
Names ...honestly I gave dummy....so lets go with columns numbers.

#6 A challenge ... how to make 'maybe' with Excel?

..am sure we almost there..
Where?

As u are code already working aa per previous example...I just included the actual aizw of the columns but not the rows which are dynamic.


You missed too ...
... so I should do this twice ... hmm?

Really in need....am not sure if it is small change in ...Apologies for that.


Thanks..
 

Monty

Well-Known Member
Hello Guru.

Let's go with the format i have provided, trying to check any changes required and found nothing found ...we are good to go.
 

vletm

Excel Ninja
Monty
How possible?
You wrote small amendment ...

Do You know, what would You need there?
You haven't answer, so it's challenge ...
 

Monty

Well-Known Member
Sir

I have almost tried my best to answer and my out tab showscthe out format looking for...and data shows which is extract in raw data.

Kindly let me know if I can support ri make this happen...
 

vletm

Excel Ninja
You use terms 'almost tried' and
Your some answers are different than some 'samples'.
Some of Your answers ... hmm ... would be answer to totally different ...
You should f-o-c-u-s ...
 

Monty

Well-Known Member
Sir

As I have attatched latest format that is rhe final one....only rows will be dynamic but columns are fixed...
I sincerely apologies for my terms used am going mad and still working on it
 

vletm

Excel Ninja
As I wrote in #19 ...
Especially the last line.
I try to do something clear ... possible after 12hrs.
You should answer and make clear reply 'what?'
 

Monty

Well-Known Member
Hey Vletm.

You are magician.

Just sharing your code...may be it is useful to some one looking at same..

Big thank you.


Code:
Private Sub Do_It()
    On Error Resume Next
    With ActiveSheet
        If .Range("Q2") = Empty Then Exit Sub
        a_step = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Columns("J:J").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Application.ScreenUpdating = True
        .Range("J2") = "Metrics"
        DoEvents
        For j = 3 To a_step
            .Cells(j, 10) = "TIMELINES"
            DoEvents
        Next j
        For c = 1 To 2
            For a = 3 To a_step
                y = a + a_step - 1 + (a_step - 1) * (c - 1)
                For x = 1 To 9
                    .Cells(y, x) = .Cells(a, x)
                    DoEvents
                Next x
                tmp = "QUALITY"
                If c = 2 Then tmp = "ISSUES"
                .Cells(y, x) = tmp
                DoEvents
                For x = 0 To 5
                    .Cells(y, 11 + x) = .Cells(a, 11 + c * 6 + x)
                    .Cells(y, 11 + x).NumberFormat = .Cells(a, 11 + c * 6 + x).NumberFormat
                    .Cells(a, 11 + c * 6 + x) = Empty
                    DoEvents
                    If a = a_step Then .Cells(2, 11 + c * 6 + x).Clear
                Next x
            Next a
        Next c
    End With
    Application.ScreenUpdating = True
    MsgBox "Done"
End Sub
 
Top