1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'VBA Macros' started by Monty, Oct 31, 2018.

  1. Monty

    Monty Well-Known Member

    Messages:
    863
    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..

    Attached Files:

  2. AlanSidman

    AlanSidman Active Member

    Messages:
    407
    this is quick and easy task using Power Query. First, here is the MCode extracted from PQ

    Code (vb):
    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 likes this.
  3. vletm

    vletm Excel Ninja

    Messages:
    4,299
    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.

    Attached Files:

    Monty likes this.
  4. Monty

    Monty Well-Known Member

    Messages:
    863
    Thanks your time...But unfortunately we do not have pivot query at office...so trying with simple VBA solution.
  5. Monty

    Monty Well-Known Member

    Messages:
    863
    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..
  6. vletm

    vletm Excel Ninja

    Messages:
    4,299
    Monty
    What would You do with code with Your mobile?
    It won't work with You mobile.
  7. AlanSidman

    AlanSidman Active Member

    Messages:
    407
    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.
  8. Monty

    Monty Well-Known Member

    Messages:
    863
    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

    Attached Files:

  9. vletm

    vletm Excel Ninja

    Messages:
    4,299
    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 likes this.
  10. Monty

    Monty Well-Known Member

    Messages:
    863
    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..
  11. vletm

    vletm Excel Ninja

    Messages:
    4,299
    #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 likes this.
  12. Monty

    Monty Well-Known Member

    Messages:
    863
    #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..
  13. Monty

    Monty Well-Known Member

    Messages:
    863
    Good morning Vletm.
  14. vletm

    vletm Excel Ninja

    Messages:
    4,299
    Monty
    Still waiting proper answers ...
    Monty likes this.
  15. Monty

    Monty Well-Known Member

    Messages:
    863
    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.
  16. Monty

    Monty Well-Known Member

    Messages:
    863
    Hey Vletm.

    Am Still struggling to edit your code ..
  17. vletm

    vletm Excel Ninja

    Messages:
    4,299
    Monty
    How possible?
    You wrote small amendment ...

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

    Monty Well-Known Member

    Messages:
    863
    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...
  19. vletm

    vletm Excel Ninja

    Messages:
    4,299
    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 ...
  20. Monty

    Monty Well-Known Member

    Messages:
    863
    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
  21. vletm

    vletm Excel Ninja

    Messages:
    4,299
    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 likes this.
  22. Monty

    Monty Well-Known Member

    Messages:
    863
    Hello Vletm

    #19 ? I didnt get you
  23. Monty

    Monty Well-Known Member

    Messages:
    863
    Good Morning...Veltm..
  24. vletm

    vletm Excel Ninja

    Messages:
    4,299
    Monty
    It Seems that You continue same same ...
    Press [ Do It ]-button

    Attached Files:

    Monty likes this.
  25. Monty

    Monty Well-Known Member

    Messages:
    863
    Hey Vletm.

    You are magician.

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

    Big thank you.


    Code (vb):
    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
     

Share This Page