Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Multiple File Calling

  Asked By: Richard    Date: Dec 21    Category: MS Office    Views: 834
  

I am opening one Excel File (B) from within another (A) and I am
launching a macro in the second file (B). I have the following code
in a module in the second file (B):

ActiveSheet.Unprotect
Range("C34").Select
Selection.Copy
Range("D34:K34").Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone,
SkipBlanks:= False, Transpose:=False
Application.CutCopyMode = False
Range("D33:K34").Select
Selection.Sort Key1:=Range("D34"), Order1:=xlDescending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False,
Orientation:=xlLeftToRight
Range("D33:K33").Select

If I run this code, somewhere after the paste events, the active
file reference changes to the first file (A). If I F8 step through
the code, the second file stays referenced (B) as I would like.
Does anyone know why this is happening and how can I produce the
desired pastes and sort in the second file (B)?

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Alejandro Smith     Answered On: Dec 21

I'm not sure what's causing it, but I can suggest a couple of things to try...

1. Comment out the line Application.CutCopyMode = False and see if it
runs as expected. I have seen other cases where this command caused problems
(you got from the macro  recorder, right?)

2. Tell Excel explicitly which workbook and worksheet to use, either by fully
qualifying the ranges:
ThisWorkbook.Sheets("Whatever").Range("D33:K34").Select
or by issuing commands to make sure the correct workbook and worksheet are
active:
ThisWorkbook.Activate
ThisWorkbook.Sheets("Whatever").Activate
before the range commands that are being misdirected.

 
Answer #2    Answered By: Mena Schmidt     Answered On: Dec 21

Thanx for the suggestions, but I am afraid this problem may be a
little more elusive. I have tried #1 below - no effect. I had
tried #2 in different ways - the statement

ThisWorkbook.Sheets("Whatever").Range("D33:K34").Select

is not being allowed in my VBA. After the ..("Whatever"). I am used
to having the VBA options pop up, however, no option at all pop up.

I have even tried putting message boxes showing the ActiveWorkbook
just after the Range Selection statements and just prior to the
pasting statement - and the alternate (incorrect file  A) file name
shows up in the msgbox. It seems that when running the file B macro
from within the VB editor, their seems to be some relationship with
file A (since file B was opened within a macro  in file A). There is
a definite reason why running a macro in file B, with no known
references to file A, there is a switching of activeworkbooks.

The thing that I am suspicious of is that when I "step" through the
macro by pressing the F8 button, after the "pasting" statement, I go
through 2 user defined functions before the next macro line of code
runs. One is a function to return the color index of the cell/cells
I am dealing with, while the other is a function to count the cells
of a particular color. These functions were in both file A & B of
the same name, but I have since renamed the function in file B to a
different name. I believe that I had changed all references to the
new function call name, however, when running the macro in file B,
stepping through, I get diverted to the original function in file
A. Sorry for the wordiness, and I appreciate you help.

 
Answer #3    Answered By: Jessie Banks     Answered On: Dec 21

How about renaming file  A and then running the code. Where it activates A it
will report an error and you can then see what line is causing the problem.

 
Didn't find what you were looking for? Find more on Multiple File Calling Or get search suggestion and latest updates.




Tagged: