Swithing between workbooks VBA

[finaljustice]

Hello, I have to put several excel files informations dispersed in many files into one single excel file.

The problem im facing is that I have one open workbook that is the one running the macro, and i have to tell it to OPEN a specific workbook and retrieve the information saving it in variables and then closing the newly open workbook.
ALL of this i'm being able to do, but when I close the workbook to go back to the one running the macro and i'm having issues...

Here is the part of the code, so you can understand what I mean:

Set wbd = ActiveWorkbook 'this is the workbook running the macro
Set wsd = ActiveSheet 'this is the activesheet on the running macro
Do While ActiveCell <> Empty
FiletoOpen = "C:\Users\Luis\Desktop\Carols\Pedidos\" & ActiveCell.Value
Application.Workbooks.Open (FiletoOpen) 'Abrir workbook cliente
Set wbt = ActiveWorkbook 'this is the TEMPORARY workbook which I extract information from.
Application.ScreenUpdating = False
    For i = 1 To wbt.Worksheets.Count
    Sheets(i).Unprotect Password:="AABBAAAAABBT"
    Sheets(i).Select
    Cells.Select
    Selection.UnMerge
    Next
'there are several manipulations here to extract the desired info which I believe aren't relevant to my issu.
 
ActiveWindow.Close 'Close TEMPORARY workbook after it's been used.
'NOW HERE IS MY ISSUE
 
wbd.Sheets(1).Range("A1048576").Select 'ERROR OCCURS here, basically i'm trying to go back to the workbook that is running the macro to PASTE the information I too from the other workbook.

I guess im not writting the code properly to tell the macro to GO BACK to the MAIN workbook that is running the macro to paste the desired information. Could someone please help me out, I'm pretty stuck on this...

Thanks for your attention
Final <!-- / message -->

 

[finaljustice]

Did you try the code I posted for the problem in your last post?

I've only had a brief look at your code and I'm pretty sure it can be tidied up.

One thing I notice is that you seem to have quite a few variables.

Also, this section of code,

Sheets("Files").Select
Range("C9").Select
Set wbd = ThisWorkbook
Set wsd = ActiveSheet
    Do While ActiveCell <> Empty
        FiletoOpen = "C:\Users\Luis\Desktop\Carols\Pedidos\" & ActiveCell.Value
        Application.Workbooks.Open (FiletoOpen) 'Abrir workbook cliente
        Set wbt = ActiveWorkbook

can be replaced with this:

Set wbd = ThisWorkbook
Set wsd = wbd.Sheets("Files")
Set rng = wsd.Range("C9")

    Do While rng <> Empty

        FiletoOpen = "C:\Users\Luis\Desktop\Carols\Pedidos\" & rng.Value
       Set wbt= Application.Workbooks.Open (FiletoOpen) 'Abrir workbook cliente

   ... loads of other code

        Set rng = rng.Offset(1)

You would then have a reference to the cell which holds the filename of the workbook to open.

With that you don't need to use ActiveCell for this part of the code.

I know you use ActiveCell later in the code but I've not had a close enough look to see what exactly its been used for.

Actually I can see how it's used and I'm pretty sure it can be replaced.

Perhaps with something like this.

Set rngNew =wsd.Range("A" & Rows.Count)
End(xlUp).Offset(1) ' reference to row to put data in

rngNew.Value = nomecliente
rngNew.Offset(, 1) = cpfcliente
etc

Hi i'm sorry it has taken me so long to answer back, work is hectic...

I know the code can be cleaner but as my knowledge of VBA is quite limited, I will try and leave the cleaning for later, after I can get this to work.

Norie, I tried the

Application.goto wbd.sheets(1).range("A1048576").select

That you had suggested, it still hasn't worked...
Andrew said I must have the worksheet active before I can select something on it... Shouldn't that be "wbd.sheets(1).select" shouldn't that "activate" the worksheet?

Thanks again for your help!

 

[Norie]

That isn't the code I suggested, mine didn't have Select.

 

[finaljustice]

That isn't the code I suggested, mine didn't have Select.

Ops, sorry, but I just tried double checked and still no avail...

Application.Goto Wbd.Sheets(1).Range("A1048576").ActiveCell.End(xlUp).Offset(1,0)

Nothing still... How can it be so hard to go back to the main workbook??!!

I must be doing something really wrong...

 

[Andrew Poulsom]

What's ActiveCell doing in there?

Application.Goto Wbd.Sheets(1).Range("A1048576").End(xlUp).Offset(1,0)

 

[finaljustice]

What's ActiveCell doing in there?

Application.Goto Wbd.Sheets(1).Range("A1048576").End(xlUp).Offset(1,0)

EUREKA!!!!!!!!!!
IT Worked! OMG!!!! Thanks Andrew!! A real life saver!

On another note, this was the only solution I could come up with, but is there a way to remove the desired information but without the use of a variable??

nomecliente = Trim(Mid(wbt.Sheets(i).Range("A1"), InStr(wbt.Sheets(i).Range("A1"), ":") + 1, Len(wbt.Sheets(i).Range("A1")) - InStr(wbt.Sheets(i).Range("A1"), ":") + 1))

Could I just make

Activecell = Trim(Mid(wbt.Sheets(i).Range("A1"), InStr(wbt.Sheets(i).Range("A1"), ":") + 1, Len(wbt.Sheets(i).Range("A1")) - InStr(wbt.Sheets(i).Range("A1"), ":") + 1))

would this work?

Thanks for the attention guys, I really appreciate it!!

 

[Norie]

I really don't think you want to use ActiveCell, that will take you way back.

What is that code doing anyway?

I can't recall it from anything you posted earlier.

 

[finaljustice]

I really don't think you want to use ActiveCell, that will take you way back.

What is that code doing anyway?

I can't recall it from anything you posted earlier.

It is simple in concept,
wbd is the workbook that runs the macro and will have information brought to it.
wbt are workbooks that are inside a FOLDER, so wbd opens a wbt retrieves all the clientes information and pastes in wbd. (the information i'm retrieving is using variables to manipulate the info such as trim, etc.)
Once i've taken the desired info and pasted them in the wbd, I loop everything and open a new wbt to retrieve new informations.

 

[Norie]

So where does ActiveCell come into that?

You shouldn't use ActiveCell, especially if you are working with multiple workboooks/worksheets.

I don't even see how you can replace a variable, nomecliente, with a reference to a range, ActiveCell.