If your static workbook is opened from the code, then you probably are
keeping the 'dynamic' workbook open first. So it would be best to replace
your underlined code above with *Set wb = ActiveWorkbook*
Regards,
Sam Mathai Chacko
On Sun, Apr 7, 2013 at 7:28 AM, LJGIT <lgem...@earthlink.net> wrote:
>
>
> I need to switch between two workbooks several times to copy/paste data.
> One workbook is static (SHS form.xls) and can be explicitly named in macro
> but the other is dynamic so need to use variable. Macro is stored in
> PERSONAL.xls and is launched from the dynamic workbook. Macro opens SHS
> form.xls and switches back and forth between the workbooks but does not
> work consistently, sometimes the data is incomplete due to the macro
> selecting the incorrect workbook. The beginning of macro is below, found
> the underlined code and added to macro but still doesn't work right. Is
> there any way to exclude PERSONAL.xls from workbook list or to save the
> name of the dynamic workbook in a variable? Have been struggling with this
> for several hours, thank you in advance for any assistance.
>
> *Dim wb As Workbook, x As String*
>
> *For Each wb In Workbooks*
> *If (wb.Name <> "Personal.xls") Then x = wb.Name*
> *Next wb*
> *Workbooks(x).Activate*
> ChDir "C:\Documents and Settings\shs\Desktop\database file
> location\11.017.4"
> Workbooks.Open Filename:="C:\Documents and
> Settings\shs\Desktop\database file location\11.017.4\SHS form.xls", _
> UpdateLinks:=3
> Workbooks(x).Activate
> Range("A10").Select
> Selection.Copy
> Windows("SHS form.xls").Activate
> Range("H7").Select
> ActiveSheet.Paste
> Workbooks(x).Activate
> Range("D10").Select
> Application.CutCopyMode = False
> Selection.Copy
> Windows("SHS form.xls").Activate
> Range("H6").Select
> ActiveSheet.Paste
>
> ...
>
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
--
Sam Mathai Chacko
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
