Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Switching workbooks with Personal.xls
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? -- Paul Wilson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Switching workbooks with Personal.xls
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 wrote: 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? -- Paul Wilson -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Switching workbooks with Personal.xls
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 -- Paul Wilson "Dave Peterson" wrote: 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 wrote: 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? -- Paul Wilson -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Switching workbooks with Personal.xls
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. Paul Wilson wrote: 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 -- Paul Wilson "Dave Peterson" wrote: 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 wrote: 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? -- Paul Wilson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
switching between workbooks | Excel Programming | |||
switching between workbooks | Excel Programming | |||
Switching between workbooks | Excel Programming | |||
Switching between workbooks | Excel Discussion (Misc queries) | |||
Switching Between 2 workbooks using VBA | Excel Programming |