SELECTING THE CORRECT SHEET FROM ANOTHER EXCEL

[budha465]

I'm having an issue with getting the MACRO to copy from the correct sheet when it is taking data from another excel work book. The issue is even though I have the sheet search on for the "TOP" it will copy form which ever sheet was last active when the excel was open. I currently have it set up to where each macro does its own section (TOP,BOTTOM,LEFT, RIGHT) so I know I have a bit of extra Dim information. If I can also kind of get a bit of an explanation as to why mine isn't working that would be a bonus.

 

[Joe4]

Welcome to the Board!

Since TOP appears to be a variable, and NOT a hard-coded value, you would NOT use:

.Sheets("TOP")

That tells Excel to select the sheet literally named "TOP" (everything enclosed in double-quotes is treated as literal text).

Since TOP is a variable, it should look like:

.Sheets(TOP)

 

[budha465]

The sheet I do want it to pull from is literally named "TOP".
When I did your suggested adjustment by removing the "" around the sheet I want it to search once I select the file it provides a message of "This workbook contains links or more external source that cold be unsafe. If you trust links, update them to get the latest data. Otherwise you can keep working with the data you have."
If I update it or not the macro just wont continue.
Thank you BTW I greatly appreciate your input and time.

 

[Joe4]

The sheet I do want it to pull from is literally named "TOP".

Not sure then why you are setting Worksheet variables if you are not using them!
I think you need to make a decision which way you want to go - use their literal names, or use the variables, and stick with that method.

When I did your suggested adjustment by removing the "" around the sheet I want it to search once I select the file it provides a message of "This workbook contains links or more external source that cold be unsafe. If you trust links, update them to get the latest data. Otherwise you can keep working with the data you have."

That is a good sign - it tells you it is finding your file and trying to open it.

If I update it or not the macro just wont continue.

Have you tried to step through the macro, one line at a time, using the F8 key, so you can see it progress through your code one line at a time, while you watch what happens to your workbooks? This is a common debugging technique. By doing this, you can often see where and why things go wrong.

If you cannot figure it out, please copy and paste your VBA code here so we can copy it and try it out for ourselves (we cannot go anything with an image of your code).
Here is a link that gives directions on how to post your code in a nice format that is easy to read and copy: How to Post Your VBA Code

 

[budha465]

I'm sorry I might not be understanding the variables thing.
I have the sheet i pull from has multiple sheets but the ones I'm trying to pull from are the TOP, BOTTOM, LEFT and RIGHT.
their exact name is that and in the document I'm pulling to has the same names.

Yes I have tried stepping through the macro and the one it is stuck on is the line of the set srchSht.

The weird thing is I've used a macro identical to this on another book and it works just fine. It pulls from the correct sheet that is named so I can't tell if I just was lucky it works on the other book or something.

Sub Load_TOP()
'
' Load_Top Macro
' Load Top Tab from support.
'
Dim TOP As Worksheet, LOAD As Worksheet

Set LOAD = Sheet1
Set TOP = Sheet2


    TOP.Select
' Open File
    FileToOpen = Application.GetOpenFilename
    Set srcSht = Workbooks.Open(FileToOpen).Sheets("TOP")
    Range("E4:M4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("DATA CONSOL.xlsm").Activate
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    LOAD.Select
    Close
    MsgBox "Upload Complete"

End Sub

 

[Joe4]

Yes I have tried stepping through the macro and the one it is stuck on is the line of the set srchSht.

That suggests that maybe the workbook you are opening might not have a sheet named "TOP" in it.

 

[mumps]

Your code seems to indicate that you are accessing one source sheet at a time. Do you want to pull data from all four sheets (TOP, BOTTOM, LEFT and RIGHT) all in one go?

 

[budha465]

That suggests that maybe the workbook you are opening might not have a sheet named "TOP" in it.

I verified multiple times before posting here that it does. The document has multiple sheets in it and they have those labels and other ones I don't need it to pull from. The initial issue I have is it doesn't pull from the correct one, it just pulls from the one that was last active when they closed the excel. Example being that if I try to pull the data from the TOP page and the user who used the excel last saved when they were on the BOTTOM sheet it pulls the data from the BOTTOM sheet instead of the TOP sheet. Again I really appreciate your time and patience with this.

 

[budha465]

Your code seems to indicate that you are accessing one source sheet at a time. Do you want to pull data from all four sheets (TOP, BOTTOM, LEFT and RIGHT) all in one go?

Currently I am just trying to pull from the one source sheet. I'm going to duplicate it for the other sheets afterwards. I'm trying to fix the singular issue first is all.

 

[Joe4]

I verified multiple times before posting here that it does. The document has multiple sheets in it and they have those labels and other ones I don't need it to pull from. The initial issue I have is it doesn't pull from the correct one, it just pulls from the one that was last active when they closed the excel. Example being that if I try to pull the data from the TOP page and the user who used the excel last saved when they were on the BOTTOM sheet it pulls the data from the BOTTOM sheet instead of the TOP sheet. Again I really appreciate your time and patience with this.

You could just select/activate the sheet you want after opening the file, i.e.

Sheets("TOP").Select

I don't think trying to include it in the file opening command works.