Results 1 to 7 of 7

Thread: How to link multiple spreadsheet inside a single sheet via VBA

  1. #1
    Join Date
    Feb 2012
    Posts
    13

    How to link multiple spreadsheet inside a single sheet via VBA

    I am trying to link some cells that are in different worksheets (I have alot of sheets) in the same workbook to a Master worksheet in the same workbook. I'd like to use vba to do this. The cells to link to are the same in all worksheets. So for example, in Master worksheet, I need C2 to link to sheet1.C3 and C3 to link to sheet2.C3 Can anyone point me in the right direction on how to do this.

  2. #2
    Join Date
    Aug 2011
    Posts
    580

    Re: How to link multiple spreadsheet inside a single sheet via VBA

    I do have another question though that I don't have the expertise to figure out. I have a cell that I want to link to a cell in another worksheet that is one cell below the adjacent cell of the last entry in a column. For example, if my last entry in column C is C5, I want to link to cell D6 This sounds pretty complicated but any help would be greatly.

  3. #3
    Join Date
    Aug 2011
    Posts
    540

    Re: How to link multiple spreadsheet inside a single sheet via VBA

    Try this. I use The C column in Sheet2 and in the example I add the formula in the activecell.
    Code:
    Sub test()
    Dim lr As Long
    lr = Sheets("sheet2").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Row
    ActiveCell.Formula = _
    "='Sheet2'!" & Cells(lr, "C").Address(False, False)
    End Sub

  4. #4
    Join Date
    Aug 2011
    Posts
    695

    Re: How to link multiple spreadsheet inside a single sheet via VBA

    Thanks for the help. So I tried to link it to column D from all worksheets into Summary-Sheet like this:
    Code:
    For Each myoffCell In Sh.Range("C" & Rows.Count).End(xlUp).Offset(1,
    0).Row
    ColNum = ColNum + 1
    Newsh.Cells(RwNum, ColNum).Formula = _
    "='" & Sh.Name & "'!" & Cells(lr, "D").Address(False,
    False)
    Next myoffCell
    But For statement only works for collection or an array. I am not sure

  5. #5
    Join Date
    Aug 2011
    Posts
    566

    Re: How to link multiple spreadsheet inside a single sheet via VBA

    Test this one :
    Code:
    Sub Summary_All_Worksheets_With_Formulas()
    Dim Sh As Worksheet
    Dim Newsh As Worksheet
    Dim myCell As Range
    Dim ColNum As Integer
    Dim RwNum As Long
    Dim Basebook As Workbook
    Dim lr As Long
    
    With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    End With
    
    Set Basebook = ThisWorkbook
    Set Newsh = Basebook.Worksheets.Add
    
    On Error Resume Next
    Newsh.Name = "Summary-Sheet"
    If Err.Number > 0 Then
    MsgBox "The Summary sheet already exist in this workbook."
    With Application
    .DisplayAlerts = False
    Newsh.Delete
    .DisplayAlerts = True
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
    End With
    Exit Sub
    End If
    
    RwNum = 1
    'The links to the first sheet will start in row 2
    
    For Each Sh In Basebook.Worksheets
    If Sh.Name <> Newsh.Name And Sh.Visible Then
    lr = Sh.Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Row
    ColNum = 1
    RwNum = RwNum + 1
    
    Newsh.Cells(RwNum, 1).Value = Sh.Name
    'Copy the sheet name in the A column
    
    For Each myCell In Sh.Range("A1,D5:E5,Z10") ' <----Change the range
    ColNum = ColNum + 1
    Newsh.Cells(RwNum, ColNum).Formula = _
    "='" & Sh.Name & "'!" & myCell.Address(False, False)
    Next myCell
    
    ColNum = ColNum + 1
    Newsh.Cells(RwNum, ColNum).Formula = _
    "='" & Sh.Name & "'!" & Cells(lr, "D").Address(False, False)
    End If
    Next Sh
    
    Newsh.UsedRange.Columns.AutoFit
    
    With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
    End With
    End Sub

  6. #6
    Join Date
    Jul 2011
    Posts
    623

    Re: How to link multiple spreadsheet inside a single sheet via VBA

    That works but when I update column C, it doesn't look at the last entry in it and gets the value of column D unless I rerun the macro.

  7. #7
    Join Date
    Jul 2011
    Posts
    634

    Re: How to link multiple spreadsheet inside a single sheet via VBA

    If you copy the formula in Z1 for example it will display the value in the D column one cell below the last data in C. Is that correct what you see. Now you can use Z1 in the macro to build the links on the Summery sheet. It will always update if you add dates to the C column now.

Similar Threads

  1. How to get rid of single quote in Microsoft Excel sheet
    By Wisaal in forum MS Office Support
    Replies: 2
    Last Post: 10-02-2012, 05:22 PM
  2. Access to Excel spreadsheet, sheet name?
    By jacDaRippa in forum Windows Software
    Replies: 3
    Last Post: 14-01-2012, 02:03 PM
  3. Replies: 3
    Last Post: 11-01-2012, 11:13 AM
  4. How to share an Excel spreadsheet between multiple users
    By Blaise in forum Windows Software
    Replies: 6
    Last Post: 08-06-2011, 11:21 AM
  5. Apple ipad: how to work with spreadsheet or excel sheet
    By Baijnath in forum Portable Devices
    Replies: 3
    Last Post: 04-01-2011, 02:08 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,717,484.44615 seconds with 17 queries