none
Auto copy data from sheet1 (150 columns) to sheet2, but with different coulmn sequences, using VBA. RRS feed

  • Question

  • Hello All, 

    I have sheet1 with 150 columns, I want a code to copy the data to sheet2 but the sequence in the later are different from those in sheet1.

    Here is a link to the file. I'm thinking of using vlookup, but my brain is disturbed and I'm unable to catch the code.

    Can you help me?

    Thanks

    Monday, May 18, 2015 7:57 AM

Answers

  • On A Simple Layer.

    Pls note that To give input about sequence of Columns you need to put some hint like you have used in sample.

    Sub CopyonMyOrder()
    
       Dim rCopy As Range
       Dim rPasteColumn As Range
       Dim rLoop As Range
        
       Dim lToRow As Long
       Dim lFromRow As Long
       Dim lCopyCol As Long
        
       lFromRow = 2
       lToRow = 10
       
       On Error Resume Next
       Set rPasteColumn = Sheet2.Range("a1:G1")
       
        For Each rLoop In rPasteColumn
             lCopyCol = Sheet1.Rows(1).Find(what:=rLoop.Value, lookat:=xlWhole).Column
             
             With Sheet1
                .Range(.Cells(lFromRow, lCopyCol), .Cells(lToRow, lCopyCol)).Copy rLoop.Offset(1)
             End With
         Next rLoop
                            
        
    End Sub
    


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    • Marked as answer by Ahmed Morsyy Monday, May 18, 2015 11:53 AM
    Monday, May 18, 2015 11:39 AM
    Answerer

All replies

  • On A Simple Layer.

    Pls note that To give input about sequence of Columns you need to put some hint like you have used in sample.

    Sub CopyonMyOrder()
    
       Dim rCopy As Range
       Dim rPasteColumn As Range
       Dim rLoop As Range
        
       Dim lToRow As Long
       Dim lFromRow As Long
       Dim lCopyCol As Long
        
       lFromRow = 2
       lToRow = 10
       
       On Error Resume Next
       Set rPasteColumn = Sheet2.Range("a1:G1")
       
        For Each rLoop In rPasteColumn
             lCopyCol = Sheet1.Rows(1).Find(what:=rLoop.Value, lookat:=xlWhole).Column
             
             With Sheet1
                .Range(.Cells(lFromRow, lCopyCol), .Cells(lToRow, lCopyCol)).Copy rLoop.Offset(1)
             End With
         Next rLoop
                            
        
    End Sub
    


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    • Marked as answer by Ahmed Morsyy Monday, May 18, 2015 11:53 AM
    Monday, May 18, 2015 11:39 AM
    Answerer
  • Thank you Asadulla, 

    This is perfect :)

    Monday, May 18, 2015 11:54 AM