I'm working in sheet A, and want to copy some cell values from sheet B. I can do it like this:
Range("B1").Select
ActiveCell.FormulaR1C1 = "='B'!RC"
And I want to do the same for C1, D1, etc. Can one achieve this in a simpler way?
Asked
Active
Viewed 36 times
0
-
Try `Range("B1:D1").FormulaR1C1 = "=B!RC"`. If you need more columns in row 1, replace `D1` with the last column. Also, please read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Foxfire And Burns And Burns Jun 09 '22 at 09:54
-
Thank you. Can I somehow format "RC" in another way? I.e ```Range("B1:D1").FormulaR1C1 = "=B!"B1:D1"" ? – Oskar Jun 09 '22 at 10:37
-
Depending of what are you trying to do. Why do you want to format RC? You are using RC notation. ¿Do you mean using A1 notation? https://excelchamps.com/formulas/r1c1/ – Foxfire And Burns And Burns Jun 09 '22 at 11:07
-
Thank you. If I wanted not to set a limit on D1, that means, getting data from sheet B until cells are blank, how would one approach that? – Oskar Jun 09 '22 at 13:56
-
That's a different question. You'll need to get last non blank column from sheet B and then use it in your code. There are thousands of examples about how to get last column here on SO. – Foxfire And Burns And Burns Jun 09 '22 at 17:51