VBA Copy Text using Right Function

I was unable to find any thing the achieves, but think this should be easy but was not able to figure out.  I have the following vba:

ws.Range("a1").Copy
                ThisWorkbook.Sheets("DataSource").Range("H1").PasteSpecial xlPasteAll

Open in new window


I would like to update the vba so that I am only capturing a part of the text that in A1 (=Right($A1, Len($a1)-30) and copy it over to H1 in the current workbook.

Here is what I have declared in the exiting code:
    Dim wb As Workbook
    Dim fso As FileSystemObject
    Dim myFolder As Folder
    Dim myFile As File
    Dim newestFile As File
    Dim ws As Worksheet

Any help would be appreciated.
jmac001Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
You can simply do this if you are picking up value from one cell..

Sheets("DataSource").Range("H1").Value = Right(ws.Range("A1").Value, Len(ws.Range("A1").Value) - 30)

Open in new window


Saurabh...
Martin LissOlder than dirtCommented:
If you want the rightmost 30 characters then modify Saurabh's answer like this

Sheets("DataSource").Range("H1").Value = Right(ws.Range("A1").Value, 30)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saurabh Singh TeotiaCommented:
Martin,

Not necessary the above and this one will give same answer..The answer will only match if the text length is 60.. However if the text length is 75 or 85..The answer won't match...

Saurabh...
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

jmac001Author Commented:
Saurabh and Martin,

I tried both and received and received an error.

Here is the code, new vba added at line 74

Sub CopyDataSource()
    Dim wb As Workbook
    Dim fso As FileSystemObject
    Dim myFolder As Folder
    Dim myFile As File
    Dim newestFile As File
    Dim ws As Worksheet
    
    Set fso = New FileSystemObject
    
    '--------------------------------------------------------------------------------
    'For test purposes, I am using the "My Documents" folder... this needs to change
    ' To use whatever folder you need
    '--------------------------------------------------------------------------------
    
    With Sheets("DataSource")
        Rows("4:65536").Select
        Selection.Delete
        Range("A4").Select
    End With
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    Set myFolder = fso.GetFolder("\\SSFilePrint\GROUPSHARE\Store Planning\Projects\Reports\Electronic Store Schedule")

    
    For Each myFile In myFolder.Files
        Select Case UCase(fso.GetExtensionName(myFile.Path))
           Case "XLS", "XLSM", "XLSB", "XLSX":
        
                If newestFile Is Nothing Then
                    Set newestFile = myFile
                ElseIf myFile.DateLastModified > newestFile.DateLastModified Then
                    Set newestFile = myFile
                End If
        End Select
    Next
    
     '--------------------------------------------------------------------------------
    'At this point... "newestFile" is a File object that is the newest Excel File in your folder
    ' The following code will open it, and now you have to copy from whatever range you need data from
    '--------------------------------------------------------------------------------
   
    If Not newestFile Is Nothing Then
        Application.Workbooks.Open newestFile.Path
        Set wb = Application.Workbooks(newestFile.Name)
    'or if you now the name of the sheet it could be something like : Set ws = wb.Sheets("Sheet1")
        Set ws = wb.Sheets(1)
      
     Set lastSourceCell = LastCell(ws)
        If lastSourceCell Is Nothing Then
            MsgBox "Nothing to copy - stopping"
            wb.Close
            Exit Sub
        End If
        
        Set lastDestCell = LastCell(ThisWorkbook.Sheets("DataSource"))
        If lastDestCell Is Nothing Then
            destinationRow = 1
        Else
            destinationRow = lastDestCell.Row + 1
        End If
        
        For i = 2 To lastSourceCell.Row
            If ws.Range("A" & i).Value = "P" And ws.Range("B" & i).Value = "V" Then
                ws.Range("A" & i).EntireRow.Copy
                ThisWorkbook.Sheets("DataSource").Range("A" & destinationRow).PasteSpecial xlPasteAll
                destinationRow = destinationRow + 1
           End If
        Next
        
        
           Sheets("DataSource").Range("H1").Value = Right(ws.Range("A1").Value, 30)
               ' ws.Range("D1").Copy
               ' ThisWorkbook.Sheets("DataSource").Range("H1").PasteSpecial xlPasteAll
                
        Application.ScreenUpdating = True
        Application.DisplayAlerts = False
        wb.Close
        MsgBox "Copy Complete"

    End If
    
'Add date ran

   Sheets("DataSource").Range("F1").Value = Date
   Application.Calculation = xlCalculationAutomatic
   
'Add date of file


    
     MsgBox "All Updates Complete"
     
End Sub

Open in new window

Saurabh Singh TeotiaCommented:
Change this line to this...

ThisWorkbook.Sheets("DataSource").Range("H1").Value = Right(ws.Range("A1").Value, Len(ws.Range("A1").Value) - 30)

Open in new window


Saurabh...
Martin LissOlder than dirtCommented:
Not necessary the above and this one will give same answer..The answer will only match if the text length is 60.. However if the text length is 75 or 85..The answer won't match...
I'm not sure if you are agreeing with me or disagreeing with me. Just to be clear if the value is for example "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz" my answer gives "wxyzabcdefghijklmnopqrstuvwxyz" while yours gives "efghijklmnopqrstuvwxyz".
Saurabh Singh TeotiaCommented:
Martin,

I had a disagreement that as both the answers will be different and i'm not sure what the end user is looking for as per the original post he is looking for len()-30 which when the text length varied more then 60 the answer will differ then what he is actually looking for...

Saurabh...
jmac001Author Commented:
Martin and Saurabh,

I decided to split the points evenly between you.  Martin your solution is the actual solution that I was looking for and Saurabh you gave the solution based on the information that I provided you with.

Thanks you both for the quick response.
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.