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

Micah05 · Aug 28th 2019

Hi,

I need help with the VBA for this automation.

I have this data set, but I want to show and use only the last 5 digits of the Sensor number. Then I want to copy all of this data (minus the heading in row 1) to another table on the worksheet labeled "test", but I want to paste it to the first empty row of the other table as that table contains data. [HR][/HR] [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 156"] [/TD]
[TD="width: 126"]A[/TD]
[TD="width: 247"]B[/TD]
[TD="width: 145"]C[/TD]

[/tr]

[tr]

[td]

1

[/td]

[td]

Date

[/td]

[td]

Sensor

[/td]

[td]

Temp

[/td]

[/tr]

[tr]

[td]

2

[/td]

[td]

7/9/2019

[/td]

[td]

1903130001000F0000000388

[/td]

[td]

93.81

[/td]

[/tr]

[tr]

[td]

3

[/td]

[td]

7/9/2019

[/td]

[td]

1903130001000F0000000370

[/td]

[td]

72.99

[/td]

[/tr]

[tr]

[td]

4

[/td]

[td]

7/9/2019

[/td]

[td]

1903130001000F0000000371

[/td]

[td]

91.86

[/td]

[/tr]

[/TABLE]
[HR][/HR]

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 [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 156"] [/TD]
[TD="width: 126"]A[/TD]
[TD="width: 247"]B[/TD]
[TD="width: 145"]C[/TD]

[/tr]

[tr]

[td]

1

[/td]

[td]

Date

[/td]

[td]

Sensor

[/td]

[td]

Temp

[/td]

[/tr]

[tr]

[td]

2

[/td]

[td]

Data already here

[/td]

[td]

Data already here

[/td]

[td]

Data already here

[/td]

[/tr]

[tr]

[td]

3

[/td]

[td]

Data already here

[/td]

[td]

Data already here

[/td]

[td]

Data already here

[/td]

[/tr]

[tr]

[td]

4

[/td]

[td]

Data already here

[/td]

[td]

Data already here

[/td]

[td]

Data already here

[/td]

[/tr]

[tr]

[td]

5

[/td]

[td]

7/9/2019

[/td]

[td]

00388

[/td]

[td]

93.81

[/td]

[/tr]

[tr]

[td]

6

[/td]

[td]

7/9/2019

[/td]

[td]

00370

[/td]

[td]

72.99

[/td]

[/tr]

[tr]

[td]

7

[/td]

[td]

7/9/2019

[/td]

[td]

00371

[/td]

[td]

91.86

[/td]

[/tr]

[/TABLE]

Mumps · Aug 29th 2019

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

Display More

Micah05 · Sep 3rd 2019

Thank you!!!!!!!!!!!!!!!!!!

Mumps · Sep 4th 2019

You are very welcome.

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

Participate now!

Share