VBA: Copy X Col to new sheet
Results 1 to 6 of 6

Thread: VBA: Copy X Col to new sheet
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2010
    Location
    Singapore
    Posts
    229
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA: Copy X Col to new sheet

    I like to copy a marco to copy 'masterlist' sheet of X roll below

    HOPE somone can help me

    B16 C16 E16 H16 Q16 AD16 R16 S16 T16 U16 V16 W16 X16 Y16 Z16 AA16 AB16 AC16
    to new wk
    A2 B2 C2 D2 E2 F2 G2 H2 I2 J2 K2 L2 M2 N2 O2 P2 Q2 R2

  2. #2
    Board Regular
    Join Date
    Dec 2013
    Posts
    76
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Copy X Col to new sheet

    Try this simple Macro
    Code:
    Option Explicit
    
    
    Sub Copy_columns()
    Rem =====>>This is a simple example
    Dim i%
    Dim First_sh As Worksheet
    Dim Sec_sh As Worksheet
    Dim First_arr(), Sec_arr()
    Set First_sh = Sheets("Sheet1") ' Change Sheet1 by the sheet's name of data source
    Set Sec_sh = Sheets("Sheet2") ' Change Sheet2  by the sheet's name of data target
    First_arr = Array("$B$4", "$c$6", "$e$1") 'Complete the array
    Sec_arr = Array("$A$2", "$c$2", "$d$2") 'Complete the array
    For i = LBound(Sec_arr) To UBound(Sec_arr)
     Sec_sh.Range(Sec_arr(i)) = First_sh.Range(First_arr(i))
     Next
    End Sub
    Last edited by salim hasan; Aug 19th, 2019 at 03:08 AM.

  3. #3
    Board Regular
    Join Date
    Apr 2010
    Location
    Singapore
    Posts
    229
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Copy X Col to new sheet

    hi,
    this isnt what i want.. i want to copy the WHOLE COLUMN. Start from B16 below

    ur code only copy 1 row?


    i want to copy COLUMN not row.


    Quote Originally Posted by salim hasan View Post
    Try this simple Macro
    Code:
    Option Explicit
    
    
    Sub Copy_columns()
    Rem =====>>This is a simple example
    Dim i%
    Dim First_sh As Worksheet
    Dim Sec_sh As Worksheet
    Dim First_arr(), Sec_arr()
    Set First_sh = Sheets("Sheet1") ' Change Sheet1 by the sheet's name of data source
    Set Sec_sh = Sheets("Sheet2") ' Change Sheet2  by the sheet's name of data target
    First_arr = Array("$B$4", "$c$6", "$e$1") 'Complete the array
    Sec_arr = Array("$A$2", "$c$2", "$d$2") 'Complete the array
    For i = LBound(Sec_arr) To UBound(Sec_arr)
     Sec_sh.Range(Sec_arr(i)) = First_sh.Range(First_arr(i))
     Next
    End Sub
    Last edited by harky; Aug 19th, 2019 at 11:07 PM.

  4. #4
    Board Regular
    Join Date
    Apr 2010
    Location
    Singapore
    Posts
    229
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Copy X Col to new sheet

    Hi,
    I manage to get the code work myself


    Code:
    Sub Step1_CopyRange()
        Dim lr As Long
        
        Application.ScreenUpdating = False
        With Sheets("ad_hoc")        'Source worksheet name
        Set DestSh = Sheets("test")  'Destination worksheet name
        
        'START of confirmation message box'
        response = MsgBox("Run Macro?", vbYesNo)
        If response = vbNo Then
        MsgBox ("Macro Canceled!")
        Exit Sub
        End If
        'END of confirmation message box'
        
             lr = .Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
            
      'Ir = last column of x
            .Range(.Cells(17, "B"), .Cells(lr, "B")).Copy DestSh.Cells(4, "A")
            .Range(.Cells(17, "C"), .Cells(lr, "C")).Copy DestSh.Cells(4, "B")
            .Range(.Cells(17, "E"), .Cells(lr, "E")).Copy DestSh.Cells(4, "C")
            .Range(.Cells(17, "H"), .Cells(lr, "H")).Copy DestSh.Cells(4, "D")
            .Range(.Cells(17, "Q"), .Cells(lr, "Q")).Copy DestSh.Cells(4, "E")
            .Range(.Cells(17, "AD"), .Cells(lr, "AD")).Copy DestSh.Cells(4, "F")
            .Range(.Cells(17, "R"), .Cells(lr, "R")).Copy DestSh.Cells(4, "G")
            .Range(.Cells(17, "S"), .Cells(lr, "S")).Copy DestSh.Cells(4, "H")
            .Range(.Cells(17, "T"), .Cells(lr, "T")).Copy DestSh.Cells(4, "I")
            .Range(.Cells(17, "U"), .Cells(lr, "U")).Copy DestSh.Cells(4, "J")
            .Range(.Cells(17, "V"), .Cells(lr, "V")).Copy DestSh.Cells(4, "K")
            .Range(.Cells(17, "W"), .Cells(lr, "W")).Copy DestSh.Cells(4, "L")
            .Range(.Cells(17, "X"), .Cells(lr, "X")).Copy DestSh.Cells(4, "M")
            .Range(.Cells(17, "Y"), .Cells(lr, "Y")).Copy DestSh.Cells(4, "N")
            .Range(.Cells(17, "Z"), .Cells(lr, "Z")).Copy DestSh.Cells(4, "O")
            .Range(.Cells(17, "AA"), .Cells(lr, "AA")).Copy DestSh.Cells(4, "P")
            .Range(.Cells(17, "AB"), .Cells(lr, "AB")).Copy DestSh.Cells(4, "Q")
            .Range(.Cells(17, "AC"), .Cells(lr, "AC")).Copy DestSh.Cells(4, "R")
            
        End With
        Application.ScreenUpdating = True
        
    'START MSG'
          MsgBox "Copy Completed!"
          Exit Sub
     'End MSG'
     
    End Sub
    Last edited by harky; Aug 20th, 2019 at 01:29 AM.

  5. #5
    Board Regular
    Join Date
    Dec 2013
    Posts
    76
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Copy X Col to new sheet

    You can shorten the code to this
    Code:
    Option Explicit
    Sub Copy_columns_Modified()
    Rem =====>>This is a simple example
    Dim i%, lr%
    Dim First_sh As Worksheet
    Dim Sec_sh As Worksheet
    Dim First_arr(), Sec_arr()
    
    
    Set First_sh = Sheets("Sheet1") ' Change Sheet1 by the sheet's name of data source
    Set Sec_sh = Sheets("Sheet2") ' Change Sheet2  by the sheet's name of data target
    lr = First_sh.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    
    
    First_arr = Array("B", "E", "H", "Q") 'Complete the array
    Sec_arr = Array("A", "B", "C", "D") 'Complete the array
    
    
    For i = LBound(Sec_arr) To UBound(Sec_arr)
     First_sh.Cells(17, First_arr(i)).Resize(lr).Copy _
      Sec_sh.Cells(4, Sec_arr(i))
     Next
    End Sub

  6. #6
    Board Regular
    Join Date
    Apr 2010
    Location
    Singapore
    Posts
    229
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Copy X Col to new sheet

    This work great but how to Copy and paste special value?

    Quote Originally Posted by salim hasan View Post
    You can shorten the code to this
    Code:
    Option Explicit
    Sub Copy_columns_Modified()
    Rem =====>>This is a simple example
    Dim i%, lr%
    Dim First_sh As Worksheet
    Dim Sec_sh As Worksheet
    Dim First_arr(), Sec_arr()
    
    
    Set First_sh = Sheets("Sheet1") ' Change Sheet1 by the sheet's name of data source
    Set Sec_sh = Sheets("Sheet2") ' Change Sheet2  by the sheet's name of data target
    lr = First_sh.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    
    
    First_arr = Array("B", "E", "H", "Q") 'Complete the array
    Sec_arr = Array("A", "B", "C", "D") 'Complete the array
    
    
    For i = LBound(Sec_arr) To UBound(Sec_arr)
     First_sh.Cells(17, First_arr(i)).Resize(lr).Copy _
      Sec_sh.Cells(4, Sec_arr(i))
     Next
    End Sub

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •