Hello everyone,

what I am trying to do: copy-paste a range of cells (they all contain strings) from one workbook to another ADDING a piece of string (always the same) to every cell in range.

Example:
Original cell content:

Sun
Is
Shining


Copied and modified cell content:
Sun_1
Is_1
Shining_1




What I did: the first solution I tried, in an example, is the following

1) Copy-paste the range
2) select the newly copied range
3) modify it

The code looks like this, and it works:

    Dim Rng As Range
    Set Rng = Range("A1")
    
    Dim Rng2 As Range
    Set Rng2 = Range("B1")

    Rng.Copy
    Rng2.PasteSpecial Paste:=xlPasteValues
    
    Rng2.Select
    For Each c In Selection
    If c.Value <> "" Then c.Value = "LSB " & c.Value
    Next

Now, if I move this solution to my main macro the code does not work.


    localFile.Sheets("PEC_SUM_PIVOT").Range(scRng.Address).Copy 'original values being copied
    dataSheet.Range(dataSheet.Cells(dataRow + 60, 1), dataSheet.Cells(dataRow + 70, 1)).PasteSpecial Paste:=xlPasteValues 'paste the values in the new sheet
    
    dataSheet.Range(dataSheet.Cells(dataRow + 60, 1), dataSheet.Cells(dataRow + 70, 1)).Select
    For Each c In Selection
    If c.Value <> "" Then c.Value = c.Value & "_COST"
    Next
Of course, all the variables have been declared before (dataSheet, dataRow, localFile, etc...). In fact, if I remove the last 4 rows of code the macro works perfectly fine so I am sure that the problem comes from the way I am doing this.

Being a beginner, I am assuming I am messing around in the wrong way with objects, methods and the like but I cannot figure out what is the problem.

FYI: the error I get when running the macro is: "Select Method of Range class failed".

Google tells me that this has something to do with changing sheets too many times, but I cannot find solution.

Thanks a lot for the help,

Konx