Results 1 to 3 of 3

Thread: Moving Column Data to another sheet using VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2014
    Location
    Florida
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Moving Column Data to another sheet using VBA

    Hello,
    I am moving data with VBA from 1 sheet to another and for the most part it works great. All the data is moved and is where i need it to be. The problem with one of the columns is that somehow the format is changed and the change causes my other reference to break. Below is the VBA i am using.

    Code:
    Sub MoveData()
    
        Dim lastRow As Long
    'DATALINK
        lastRow = Sheets("Raw Data").Range("A" & Rows.Count).End(xlUp).Row
        Sheets("DATA").Range("A2:A" & lastRow).Value = Sheets("Raw Data").Range("A2:A" & lastRow).Value
    'AGENT
        lastRow = Sheets("Raw Data").Range("B" & Rows.Count).End(xlUp).Row
        Sheets("DATA").Range("B2:B" & lastRow).Value = Sheets("Raw Data").Range("B2:B" & lastRow).Value
    'DIRECTOR
        lastRow = Sheets("Raw Data").Range("E" & Rows.Count).End(xlUp).Row
        Sheets("DATA").Range("C2:C" & lastRow).Value = Sheets("Raw Data").Range("E2:E" & lastRow).Value
    'CENTER
        lastRow = Sheets("Raw Data").Range("F" & Rows.Count).End(xlUp).Row
        Sheets("DATA").Range("D2:D" & lastRow).Value = Sheets("Raw Data").Range("F2:F" & lastRow).Value
     'SURVEY   
        lastRow = Sheets("Raw Data").Range("G" & Rows.Count).End(xlUp).Row
        Sheets("DATA").Range("E2:E" & lastRow).Value = Sheets("Raw Data").Range("G2:G" & lastRow).Value
    'REP SAT    
        lastRow = Sheets("Raw Data").Range("H" & Rows.Count).End(xlUp).Row
        Sheets("DATA").Range("F2:F" & lastRow).Value = Sheets("Raw Data").Range("M2:M" & lastRow).Value
        
    
    
        
    End Sub
    the problem is that the last part of the script for moving G2:G to M2:M i need the data to read this way
    +5 Highly satisfied
    +3
    +5 Highly satisfied
    +5 Highly satisfied
    +4

    but instead the data is changed to read this way
    +5 Highly satisfied
    3
    +5 Highly satisfied
    +5 Highly satisfied
    4


    and breaks my reference =ABS(MID(F2,2,1)) on my output table.


    Thanks for the help! and if i did not explain well enough i can put something in a drop

  2. #2
    Board Regular
    Join Date
    May 2017
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Moving Column Data to another sheet using VBA

    This should do the trick

    Sub HideRows()
    Dim lastRow As Integer, Col As Integer, Row As Integer
    lastRow = Sheets("Raw Data").Range("A" & Rows.Count).End(xlUp).Row
    For Col = 1 To 6
    For Row = 2 To lastRow
    Sheets("DATA").Cells(Row, Col).Value = "'" & Sheets("Raw Data").Cells(Row, Col).Value
    Next Row
    Next Col
    End Sub

  3. #3
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,813
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Moving Column Data to another sheet using VBA

    Try this:

    Code:
    Sub MoveData()
    Application.ScreenUpdating = False
    Dim Del As Variant
    Dim lastRow As Long
    Dim x As Long
    Del = Array("A", "B", "E", "F", "G", "M")
    x = 1
        For i = 0 To 5
            lastRow = Sheets("Raw Data").Cells(Rows.Count, Del(i)).End(xlUp).Row
            Sheets("Raw Data").Range(Del(i) & "2" & ":" & Del(i) & lastRow).Copy Sheets("DATA").Cells(2, x)
            x = x + 1
        Next
    Application.ScreenUpdating = True
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

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
  •