VBA Help for Copy Left String from Sheet1 Paste in Sheet2

[garciausmc]

Hello. Requesting some VBA help here. I have data in one range in Sheet1 (say Range Q10:Q1000). I would like to copy only 6 digits from the string and paste it into a different range in Sheet2. Would be great if you could also help with only copying data if the cell is not blank. I understand I can use the Left function but I want to simplify it with a macro. Example string I am trying to extract the left 6 characters from: M2141032040001

I only want "M21410" to be copied from Sheet1 and Pasted into Sheet2.

Thanks!

 

[Michael M]

Maybe this way

Sub MM1()
Dim r As Long, x As Long
x = 1
    For r = 10 To 1000
        Sheets("Sheet2").Range("A" & x) = Left(Range("Q" & r), 6)
        x = x + 1
    Next r
End Sub

 

[garciausmc]

Maybe this way

Sub MM1()
Dim r As Long, x As Long
x = 1
    For r = 10 To 1000
        Sheets("Sheet2").Range("A" & x) = Left(Range("Q" & r), 6)
        x = x + 1
    Next r
End Sub

Almost perfect but I have row headers in Sheet2. I would like it to paste start on the second row (e.g. B2). How can I modify your code to do this?

 

[Michael M]

Change this to 2

x = 1

 

[garciausmc]

Change this to 2

x = 1

Thank you!

 

[Michael M]

Glad to help...