Copy range from one workbook to another

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have this code

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, wb1)
        wb2.Sheets(1).Range("A2:A" & wb2.Sheets(1).Range("A2").End(xlUp).Row).Copy wb1.Sheets("YPNames").Range("A2")
        
        wb1.Names.Add Name:="tblYPNames", RefersTo:=wb1.Sheets("YPNames").Range("A2").End(xlUp)
        Tracker.cboYP.ListFillRange = "tblYPNames"
        Tracker.cboYP.ListFillRange = "tblYPNames"
Application.DisplayAlerts = True
End Sub

Is this line the correct code to copy the list on sheet 1 of list.xlsm to the sheet called YPNames in the workbook that contains the sub?
VBA Code:
wb2.Sheets(1).Range("A2:A" & wb2.Sheets(1).Range("A2").End(xlUp).Row).Copy wb1.Sheets("YPNames").Range("A2")
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,867
Office Version
  1. 2016
Platform
  1. Windows
This line
wb2.Sheets(1).Range("A2:A" & wb2.Sheets(1).Range("A2").End(xlUp).Row)

You started with Range("A2") and then Ctrl+ArrowUp? You are not getting the range from Range("A2") and below, if that what you were trying to do.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,867
Office Version
  1. 2016
Platform
  1. Windows
Maybe something like this?
wb2.Sheets(1).Range("A2", wb2.Sheets(1).Cells(wb2.Rows.Count, "A").End(xlUp)).Copy wb1.Sheets("YPNames").Range("A2")
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks Zot. I tried adding some code and I have this now but I get the error on the line you provided that says Object doesn't support this property or method. I didn't think it was so hard to copy some data.

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)
    Call CheckFolderExists
    Call CheckFileExists
    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, wb1)
        wb2.Sheets(1).Range("A2", wb2.Sheets(1).Cells(wb2.Rows.Count, "A").End(xlUp)).Copy wb1.Sheets("YPNames").Range("A2")
        
        wb2.Save
        wb2.Close
        
        wb1.Names.Add Name:="tblYPNames", RefersTo:=wb1.Sheets("YPNames").Range("A2").End(xlUp)
        Tracker.cboYP.ListFillRange = "tblYPNames"
        Tracker.cboYP.ListFillRange = "tblYPNames"
Application.DisplayAlerts = True
End Sub

Sub CheckFileExists()

Dim strFileName As String
Dim strFileExists As String

    strFileName = ThisWorkbook.Path & "\Young People\List.xlsm"
    strFileExists = Dir(strFileName)

    If strFileExists = "" Then
        Workbooks.Add.SaveAs ThisWorkbook.Path & "\Young People\List", 52
    End If
End Sub


Sub CreateWB(newyp As String, wb1 As Workbook)
Dim V
    Call CheckFolderExists
    Workbooks.Add.SaveAs ThisWorkbook.Path & "\Young People\" & newyp, 52
    For Each V In Split("7 8 9 10 11 12 1 2 3 4 5 6")
        Sheets.Add(, Sheets(Sheets.Count)).Name = MonthName(V)
    Next
    Call SetupSheets
    Sheets("sheet1").Delete
ActiveWorkbook.Close savechanges:=True
Tracker.Cells(6, 4) = ""
End Sub

Sub CheckFolderExists()
    If Dir(ThisWorkbook.Path & "\Young People", vbDirectory) = "" Then
        MkDir Path:=ThisWorkbook.Path & "\Young People"
    End If
End Sub

With the code I added, I was trying to write code that would check if the List file exists and if not, create it. The list file just needs to be a plain .xlsm file.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

What have I done wrong?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Maybe copy the whole column
VBA Code:
wb2.Sheets(1).Columns("A:A").Copy wb1.Sheets("YPNames").Range("A1")
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

That works Michael, thank you!
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Just having another issue now. I need the new range that is copied to the sheet YPNames, to become the named range tblYPNames. As you can see from post 4, I have a line of code near the end that I thought would change the named range but it is not working. What is wrong with it?
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
wb1.Names.Add Name:="tblYPNames", RefersTo:=wb1.Sheets("YPNames").Range("A2").End(xlDown)
 

Forum statistics

Threads
1,147,823
Messages
5,743,405
Members
423,792
Latest member
travisds

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