Switching workbooks with Personal.xls

[Paul Wilson]

Hi,

I have a Personal.xls which I use to store my macros tha I use, this way
enabling them to be used on all workbooks I open.
I now need to edit a macro to open another workbook, copy a range switch
back to the original an paste at the end.
Because the macro is in the Personal.xls it won't switch back to the
original workbook from which I want it pasted into, it wants to go to the
Personal.xls.
The code below is the section where I'm having the problems.

Dim Here As Workbook, There As Workbook
Set Here = ThisWorkbook
Set There = Nothing

Set There = Workbooks.Open(fPath)

There.Activate
Range("A2:K37").Select
Selection.Copy

Here.Activate
Cells(LastCell + 1, 1).Select
ActiveSheet.Paste

Any ideas?

 

[Dave Peterson]

You don't need to keep track of where you started if you don't select/activate
stuff.

Dim LastCell as range
dim wkbk as workbook

with activesheet
set lastCell = .cells(.rows.count,"A").end(xlup)
end with

set wkbk = nothing
on error resume next
set wkbk = Workbooks.Open(fPath)
on error goto 0

if wkbk is nothing then
Msgbox "not found"
exit sub
end if

wkbk.worksheets("Sheet9999").range("a2:k37").copy _
destination:=lastcell.offset(1,0)

wkbk.close savechanges:=false

=============
I'd specify the name of that worksheet in the "sending" workbook. I wouldn't
rely on the activesheet when the workbook opened.

If it's always the leftmost worksheet, you could use:

wkbk.worksheets(1).range("a2:k37").copy _
destination:=lastcell.offset(1,0)

 

[Paul Wilson]

Hi Dave,

This didn't seem to work, when it opens up the file as in mine it switches
autmatically and stops because the personal.xls is hidden and I assume it
doesn't know it needs to go back to the orignal file.
Which won't be the same each time, so I can't set it to something specific.

Thanks for your input.

Cheers

 

[Dave Peterson]

I don't think it has anything to do with where the macro is.

But if the code stops right after it opens the other workbook, I bet you're
using a shortcut key assigned to that macro to start it.

If that's the case, remove the shift-key from the shortcut key combination and
try it again.

Hi Dave,

This didn't seem to work, when it opens up the file as in mine it switches
autmatically and stops because the personal.xls is hidden and I assume it
doesn't know it needs to go back to the orignal file.
Which won't be the same each time, so I can't set it to something specific.

Thanks for your input.

Cheers