0

I hope someone can help me because I have tried many suggestions from other sites, but can not find the solution.

I have 2 workbooks. One contains the list of fields to be copied to the other. So far looks very easy, but when I try to assign the sheet name to variable, it does not store it so I am not able to return that sheet later.

I will give you an example

Function getActiveWBName()
    getActiveWBName = ActiveWorkbook.NAME
End Function

Dim homeWB

Sub pull_data()
    Set homeWB = Workbooks(getActiveWBName)
    homeWB.Activate
    Call Update_List
End Sub

Sub Update_List()
    Workbooks.Open Filename:=FullFilePath, UpdateLinks:=3

    Sheets("List").Activate
    Cells.Select
    Selection.Copy
    ActiveWorkbook.Close

    homeWB.Activate
    Sheets("List").Activate
    Range("A1").Select
    Selection.Paste
End Sub

After getting to homeWB.activate in the sub Update_List it returns runtime error 424 - Object required.

What am I doing wrong?

I would like to retain the function that returns active sheet name.

Can anyone please help me with this?

Community
  • 1
  • 1
user2287712
  • 11
  • 1
  • 1
  • 4

3 Answers3

1

You don't have to go down that road :) I am not sure which site you referred to but you should avoid the use of .Select and .Activate. See this

Your code can be re-written as (Change as applicable)

Sub Sample()
    Dim wbInput As Workbook, wbOutput As Workbook
    Dim wsInput As Worksheet, wsOutput As Worksheet

    '~~> From where you want to copy
    Set wbInput = ThisWorkbook
    '~~> This is the sheet which has the fields you want to copy
    Set wsInput = wbInput.Sheets("List")

    '~~> Destination workbook
    Set wbOutput = Workbooks.Open("C:\Sample.xlsx")
    '~~> Destination sheet
    Set wsOutput = wbOutput.Sheets("List")

    '~~> Copy the relevant range
    wsInput.Range("A1:A10").Copy wsOutput.Range("A1")
End Sub
Community
  • 1
  • 1
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
0

You're getting an Object Required error because homeWB is not declared as a public variable, nor is it passed in to the Update_List subroutine.

You can fix that by passing the variable homeWB to the Update_List subroutine:

Sub pull_data()
Set homeWB = Workbooks(getActiveWBName)
homeWB.Activate
Call Update_List(homeWB)

End Sub

Sub Update_List(homeWB as Workbook)
Workbooks.Open Filename:=FullFilePath, UpdateLinks:=3

Sheets("List").Activate
Cells.Select
Selection.Copy
ActiveWorkbook.Close

homeWB.Activate
Sheets("List").Activate
Range("A1").Select
Selection.Paste
End Sub

However, I would heed Siddhart Rout's advice above, regarding the use of Activate and Select which should be avoided 99% of the time.

David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • Is homeWB required to be public ? – Santosh Apr 16 '13 at 19:02
  • If you pass the variable explicitly as a required argument for the `Update_List` function, no. If you would prefer not to pass the variable value between the subroutines, then yes, you would have to declare as public variable. – David Zemens Apr 16 '13 at 19:05
  • I made a revision to the way the `Update_List` sub is called. There is no error when passing an object variable between subroutines like `Call Update_List(homeWB)`. – David Zemens Apr 16 '13 at 19:09
0

Try below code.

Dim homeWB As Workbook
Const FullFilePath = "C:\Users\Santosh\Desktop\ssss.xlsx" ' specify your path here

Sub pull_data()
    Set homeWB = ThisWorkbook
    Call Update_List
End Sub

Sub Update_List()

    Dim wkb As Workbook
    Set wkb = Workbooks.Open(Filename:=FullFilePath, UpdateLinks:=xlUpdateLinksAlways)
    wkb.Sheets("List").Cells.Copy homeWB.Sheets("List").Range("A1")

    wkb.Close
End Sub
Santosh
  • 12,175
  • 4
  • 41
  • 72