OzGrid

How to use VBA to returning 5 left digits and pasting to bottom of existing data set

< Back to Search results

 Category: [Excel]  Demo Available 

How to use VBA to returning 5 left digits and pasting to bottom of existing data set

 

Requirement:

 

The user need shelp with the VBA for this automation.

The user has this data set, but wants to show and use only the last 5 digits of the Sensor number. Then wants to copy all of this data (minus the heading in row 1) to another table on the worksheet labeled "test", but the user  wants to paste it to the first empty row of the other table as that table contains data.

 

  A B C
1 Date Sensor Temp
2 7/9/2019 1903130001000F0000000388 93.81
3 7/9/2019 1903130001000F0000000370 72.99
4 7/9/2019 1903130001000F0000000371 91.86



On worksheet Test: the data from table above would paste into the first empty row (5), with only last 5 digits of the sensor number showing:

 

  A B C
1 Date Sensor Temp
2 Data already here Data already here Data already here
3 Data already here Data already here Data already here
4 Data already here Data already here Data already here
5 7/9/2019 00388 93.81
6 7/9/2019 00370 72.99
7 7/9/2019 00371 91.86

 

Solution:

 

Make the sheet containing the complete sensor number the active sheet and run this macro:

 

Code:
Sub Copydata()
    Application.ScreenUpdating = False
    Dim LastRow As Long, LastRow2 As Long, rng As Range, desWS As Worksheet
    Set desWS = Sheets("Test")
    LastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    ActiveSheet.UsedRange.Offset(1, 0).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0)
    With desWS
        LastRow2 = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        For Each rng In .Range("B" & LastRow + 1 & ":B" & LastRow2)
            rng = Right(rng, 5)
        Next rng
    End With
    Application.ScreenUpdating = True
End Sub

 

Obtained from the OzGrid Help Forum.

Solution provided by royUK.

 

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

 

See also:

How to use a macro or formula to copy data from cell to all cells in that group in adjacent column
How to compare two columns in excel, inserting blank rows moving associated data
How to run a macro if a column has a certain text in it
How to check if column header exists and if not then add column with that header

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


Gallery



stars (0 Reviews)