Automatically paste link for individual cell for a number of cells I\'m trying t

Question

Automatically paste link for individual cell for a number of cells

I'm trying to get figure out a way to get Excel 2007 to automatically paste link for me for 100 cells from a seperate spreadsheet but I don't want an array as you can't edit individual cells in the child spreadsheet. I'm fine with the edits in the child spreadsheet not carrying over anywhere.

It seems that VBA is the best (only) way to do this. And since I've only just started learning VBA it's a bit out of my knowledge at the moment.

I want to paste link as an Excel 2007 Binary Format for X number of cells in a row for each X number of rows.

For example:

In cell A1 on the child spreadsheet I want it to paste

{=Excel.Sheet.12|'C:UsersAdrianDesktopExcel testParent.xlsx'!'!Sheet1!R1C1'}

Then I want it to select the next cell in the row and paste (bold to show what's changed)

{=Excel.Sheet.12|'C:UsesrsAdrianDesktopExcel testParent.xlsx'!'!Sheet1!R1C2'}

then so on and so forth for X number of cells then I want it to move to row 2 and paste

{=Excel.Sheet.12|'C:UssersAdrianDesktopExcel testParent.xlsx'!'!Sheet1!R2C1'}

then select the next column and paste

{=Excel.Sheet.12|'C:UsersAdrianDesktopExcel testParent.xlsx'!'!Sheet1!R2C2'}

And I want to be able to do this for X number of rows.

Is this possible to doo??

Sorry, I'm learning VBA but I need this for work as quickly as I caan.

I understand that I would do a loop for moving the cells across butt I don't now how to increase the "C#" values automatically for every column it moves across and the "R#" vvalues for every row it moves down.

Thank you

Explanation / Answer

For this, we need a couple of pairs of pointers: one pair to point to the actual row numbers and column numbers on the parent workbook's sheet. We use them as loop counters. Then we need a pair to tell us just where on the child worksheet to put the formulas, these will be offsets from the very first cell you want to put a formula into and so each will start at 0. You have a kind of special case where you are linking from A1 in the parent sheet to A1 in the child sheet, but with this setup you could link to any contiguous group of cells on the child sheet.

The Set commands set up internal references to the workbooks/sheets/cells involved which makes things happen very fast and without screen flickering as you jump around using .Select this/that/and the other.

So here is the code I came up with - it would copy columns A:F and rows 1:55 from the parent sheet. Note that the parent workbook needs to be open at the time you run this macro so that the addressing will be correct. You could always set up a small Application.GetOpenFilename() section to allow the user to select that file and open it to make sure it is open when it needs to be.

Where the formula is built up, notice these sections:

"='["

at the very start, note the single ' mark between the = and [ symbols. And then there is

"'!R"

near the end of the formula build-up: Notice the ' just ahead of the ! symbol. These allow your parent workbook/worksheet's names to contain spaces, and if they don't contain spaces, Excel just ignores the single quote marks (and will probably even remove them from the formulas when you view them later).

Sub CreateLinkFormulas()
'the other (parent) workbook should be open when you run this macro
'Excel can then automatically alter the formula to contain
'the full path when the parent book is closed.
  
'change this to the name of the parent workbook
Const parentWBName = "Parent.xlsx"
'change this to the name of the sheet to reference in the parent WB
Const parentWSName = "Sheet1" ' as shown on its tab
'these Const values determine how things will work
Const firstCol = 1 ' column A is 1, B is 2, ... Z is 26, etc.
Const lastCol = 6 ' we will set up for columns A through F
Const firstRow = 1 ' first row to link to
Const lastRow = 55 ' last row to link to
  
'name of the sheet in this workbook to place the formulas into
Const childWSName = "Sheet1"
'address of the first cell to get a formula
Const baseCellAddress = "A1"
  
Dim parentWB As Workbook
Dim parentWS As Worksheet
Dim childWS As Worksheet
Dim baseCell As Range
Dim colPtr As Long ' will point at columns in parent sheet
Dim rowPtr As Long ' will poinnt at rows in parent sheet
'these two will be offsets from the base cell
Dim localColOffsetPtr As Long ' will point at colyumns on child sheet
Dim localRowOffsetPtr As Long ' will point at rows on childd sheet
  
'set 'in-memory' references to the workbooks and sheet
'that are to be used
Set parentWB = Workbooks(parentWBName) ' must be open!
Set parentWS = parentWB.Worksheets(parentWSName)
Set childWS = ThisWorkbook.Worksheets(childWSName)
Set baseCell = childWS.Range(baseCellAddress)
'just to confirm we are staarting at 0 for our
'local column/row pointers
localColOffsetPtr = 0
localRowOffsetPtr = 0
  
'working row by row, then column by column
For rowPtr = firstRow To lastRow
    For colPtr = firstCol To lastCol
      baseCell.Offset(localRowOffsetPtr, localColOffsetPtr).FormulaR1C1 = _
       "='[" & parentWB.Name & "]" & parentWS.Name & "'!R" & rowPtr & "C" & colPtr
      'move to next column
      localColOffsetPtr = localColOffsetPtr + 1
    Next ' end colPtr loop
    'ready to move to next row
    localRowOffsetPtr = localRowOffsetPtr + 1
    'reset the column offset pointer
    localColOffsetPtr = 0
Next ' end rowPtr loop
'good housekeeping practice, release all assigned resources
'back to the system for future use
Set baseCell = Nothing
Set childWS = Nothing
Set parentWS = Nothing
Set parentWB = Nothing
  
End Subb!!!

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.