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
Bookmarks