none
Copy Paste Error RRS feed

  • General discussion

  • Hi, 

    I have used the below code to copy and paste data. But when I paste the data to other sheet then the width of the column does not change . Please help :)

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$L$21" Then
    
            Dim origSheet As Worksheet
            Dim destSheet As Worksheet
            Dim SrcRange As Worksheet
            
        Set origSheet = ActiveWorkbook.Worksheets(1)
        Set SrcRange = ActiveWorkbook.Worksheets(2)
        Set destSheet = ThisWorkbook.Worksheets(Target.Value)
        
            origSheet.Columns("A").Copy (destSheet.Columns("A"))
            origSheet.Columns("B").Copy (destSheet.Columns("B"))
            origSheet.Columns("C").Copy (destSheet.Columns("C"))
            origSheet.Columns("D").Copy (destSheet.Columns("D"))
            origSheet.Columns("E").Copy (destSheet.Columns("E"))
            origSheet.Columns("F").Copy (destSheet.Columns("F"))
            origSheet.Columns("G").Copy (destSheet.Columns("G"))
            origSheet.Columns("H").Copy (destSheet.Columns("H"))
            origSheet.Columns("I").Copy (destSheet.Columns("I"))
            origSheet.Columns("J").Copy (destSheet.Columns("J"))
            origSheet.Columns("K").Copy (destSheet.Columns("K"))
            
            SrcRange.Columns("G").Copy (destSheet.Columns("L"))
            
            origSheet.Columns("m").Copy (destSheet.Columns("m"))
            origSheet.Columns("N").Copy (destSheet.Columns("N"))
            origSheet.Columns("O").Copy (destSheet.Columns("O"))
            origSheet.Columns("P").Copy (destSheet.Columns("P"))
            origSheet.Columns("Q").Copy (destSheet.Columns("Q"))
            
            End If
    
    End Sub
    


    Nitesh

    Saturday, April 13, 2013 6:13 PM

All replies

  • on my computer this code works just fine! but i don't know why you make it that complicated! this is how i would write the part between if then - end if:

            Dim origSheet As Worksheet
            Dim destSheet As Worksheet
            Dim SrcRange As Worksheet
            
        Set origSheet = ActiveWorkbook.Worksheets(1)
        Set SrcRange = ActiveWorkbook.Worksheets(2)
        Set destSheet = ThisWorkbook.Worksheets(Target.Value)
        
        For i = 1 To 17
        origSheet.Columns(i).Copy
        destSheet.Columns(i).PasteSpecial
        Next
        
        SrcRange.Columns("G").Copy
        destSheet.Columns("L").PasteSpecial

    i used the pastespecial function instead of the paste function, in this way it should hold the lay-out as well. Llike you can see i use a loop: this is because it's easier to write and read, and it will work just as fast. Also notice that you don't have to insert "A", but you can also just use 1 (otherwise the loop shouldn't work). It's only at the end i do the exception, because in this way i didn't have to include it in the loop (you could say: if i = 12 then ... but it would take memory and time of the program every time it passes that construction).

    I hope my anwser was helpful!



    • Edited by Mr. Rik Saturday, May 4, 2013 4:38 PM
    Saturday, May 4, 2013 4:37 PM