Changing my spreadsheet to reference another spreadsheet instead of an internal sheet

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I was recording the Young People in a separate sheet in my spreadsheet but I would like it to be in a separate document, so multiple people can access it at once.

I have started to try and change the code. The separate file is list.xlsm and is stored in the same directory as the Young people files.
There is nothing on the spreadsheet, except for a list of young people starting in A2 of the first sheet and going down.

I have tried to start to change the code and have ran into a few problems.

VBA Code:
Sub AddYP()
Application.DisplayAlerts = False
Dim newyp As String, rng As Range, wb1 As Workbook, wb2 As Workbook
    Set wb1 = ThisWorkbook
        newyp = Tracker.Cells(6, 4)
    Workbooks.Open Filename:=ThisWorkbook.Path & "\Young People\List.xlsm"
    Set wb2 = Workbooks("List")
    With wb2.Sheets(1).Range("A:A") 'searches all of column A
        Set rng = .Find(What:=newyp, After:=.Cells(.Cells.Count), LookIn:=xlValues, _
                        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not rng Is Nothing Then
        MsgBox "This name is already in the list."
        Exit Sub
        Else
            wb2.Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = newyp
       End If
        End With
        Call CreateWB(newyp)
        wb2.sheets(1).range("A2:A" & wb2.Sheets(1).range("A2").end(xldown).row & ")"
    ThisWorkbook.Names.Add Name:="tblYPNames", _
    RefersTo:=Range("tblYPNames").Resize(Range("tblYPNames").Rows.Count + 1)
        Tracker.cboYP.ListFillRange = "tblYPNames"
        Tracker.cboYP.ListFillRange = "tblYPNames"
Application.DisplayAlerts = True
End Sub

This line is red and I am not sure why
VBA Code:
wb2.sheets(1).range("A2:A" & wb2.Sheets(1).range("A2").end(xldown).row & ")"



What I am trying to do is copy the list from the list workbook and paste it into the sheet YP each time I run the sub so it is always up to date.

Could someone help me please?
 
Last edited:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,410
Office Version
  1. 2016
Platform
  1. Windows
Should the line just be like
wb2.Sheets(1).Range ("A2:A" & wb2.Sheets(1).Range("A2").End(xlDown).Row)
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
When the new range is copied across from the list workbook to the current workbook, I then need to define a new named range. What is wrong with my code as it tells me there is a syntax error

VBA Code:
Sub AddYP()
Application.DisplayAlerts = False
Dim newyp As String, rng As Range, wb1 As Workbook, wb2 As Workbook, ListName As String
    Set wb1 = ThisWorkbook
        newyp = Tracker.Cells(6, 4)
    Workbooks.Open Filename:=ThisWorkbook.Path & "\Young People\List.xlsm"
    Set wb2 = Workbooks("List")
    With wb2.Sheets(1).Range("A:A") 'searches all of column A
        Set rng = .Find(What:=newyp, After:=.Cells(.Cells.Count), LookIn:=xlValues, _
                        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not rng Is Nothing Then
        MsgBox "This name is already in the list."
        Exit Sub
        Else
            wb2.Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = newyp
       End If
        End With
        Call CreateWB(newyp)
        wb2.Sheets(1).Range("A2:A" & wb2.Sheets(1).Range("A2").End(xlDown).Row).Copy wb1.Sheets("YPNames").Range("A2")
        
        wb1.Names.Add Name:="tblYPNames",
        RefersTo:=Range("tblYPNames").Resize(Range("tblYPNames").Rows.Count + 1)
        Tracker.cboYP.ListFillRange = "tblYPNames"
        Tracker.cboYP.ListFillRange = "tblYPNames"
Application.DisplayAlerts = True
End Sub

The error is with this bit
VBA Code:
        wb1.Names.Add Name:="tblYPNames",
        RefersTo:=Range("tblYPNames").Resize(Range("tblYPNames").Rows.Count + 1)
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,410
Office Version
  1. 2016
Platform
  1. Windows
You wrote in two line, thus you need to add underscore after Name:="tblYPNames",

wb1.Names.Add Name:="tblYPNames", _
RefersTo:=Range("tblYPNames").Resize(Range("tblYPNames").Rows.Count + 1)
 

Forum statistics

Threads
1,136,206
Messages
5,674,410
Members
419,507
Latest member
karenvanegas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top