Getting #REF error on copy/pasted cells with macro!

[Martyn]

Dear NG members,

Here is the macro for copying ( and later comparing) a range of data from
two workbooks onto a new one I am trying to work out but fail. I have
described the situation a few days back as "coplex" for me. Tom and Tushar
said it is a simple problem but it sure is aint for me. Please let me remind
my aim in some detail if you do not remember the situation or couldn't find
the thread below...
====================
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 22/03/2004 by Martyn
'
'
Workbooks.Open(Filename:="C:\My Documents\Martyn - 16.10.2003.xlt",
Editable:=True).RunAutoMacros Which:=xlAutoOpen

Worksheets("Sheet13").Activate
Range("C21:C30").Select
Selection.Copy
Windows("ANALIZ.XLS").Activate
Worksheets("Sheet1").Activate
Range("B1:B10").Select
ActiveSheet.Paste

Workbooks.Open(Filename:="C:\My Documents\Martyn - 24.02.2004.xlt",
Editable:=True).RunAutoMacros Which:=xlAutoOpen

'Sheets("Sheet13").Select
'Range("C21:C30").Select
'Selection.Copy
'Windows("ANALIZ.XLS").Activate
'Sheets("Sheet2").Select
'Range("B1:B10").Select
'ActiveSheet.Paste
'ActiveSheet.Paste

Worksheets("Sheet13").Activate
Range("C21:C30").Select
Selection.Copy
Windows("ANALIZ.XLS").Activate
Worksheets("Sheet2").Activate
Range("B1:B10").Select
ActiveSheet.Paste

' Finished copy

Application.CutCopyMode = False
End Sub

Hope someone can help me solve this.
TIA
Martyn

 

[Martyn]

Thanks a lot Brad,
ActiveSheet.Paste , True
solved the first part of my problem.
The remaining one is to Be able to input the comparing file names manually
for the macro to use
Any suggestion on that?
Martyn

 

[Guest]

I'm not sure I'm properly understanding you, but maybe this is what you mean

Workbooks.Open (Application.GetOpenFilename("Excel Templates (*.xlt),*.xlt"), Editable:=True).RunAutoMacros Which:=xlAutoOpe

That's a one line wonder, would be better to

strFileName = Application.GetOpenFilename("Excel Templates (*.xlt), *.xlt"
if strFileName = "" Then Exit Su
Workbooks.Open (strFileName, Editable:=True).RunAutoMacros Which:=xlAutoOpe

Something like that

Good luck

-Brad

 

[Martyn]

Thank you very much Brad,
This was what I was looking for.
One final additional question if I may. Suppose I want to compare results
from more then 2 source files all in one go!. In that case the comparison
with the criteria of (a. greater then, b. equal to c. less then) will not be
much meaningful. Thus, for such a case the comparison should be in the form
of a bar chart maybe. How can I implement that?
TIA
Martyn

I'm not sure I'm properly understanding you, but maybe this is what you mean:

Workbooks.Open (Application.GetOpenFilename("Excel Templates

(*.xlt),*.xlt"), Editable:=True).RunAutoMacros Which:=xlAutoOpen