what is object "_Global" failed

[R Krishna]

hi
I have a macro to copy some data from one sheet to another. It works on the
first pass the second time it shows a na error, it says Range(Columns.Count
& "5").End....... =<Method"Range"of object"_Global" Failed>
what does this mean?, and how to I fix it?

TIA

Rkrishna

Dim MyRo As Long, DatCol As Long

MyRo = Range("A" & Rows.Count).End(xlUp).Row
DatCol = Range(Columns.Count & "5").End(xlToLeft).Column
Range("A1" & ":" & DatCol & MyRo).Select
Selection.Copy
Sheets(shname).Select
Range("A" & MyRow).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
'MyRow = Range("A" & Rows.Count).End(xlUp).Row
MyRow = MyRow + MyRo
Sheets(sname).Select
Range("A1" & ":" & DatCol & MyRo).Select
Application.CutCopyMode = False
Selection.Clear
Selection.QueryTable.Delete
Range("A1").Select
Next r

 

[Bob Phillips]

Haven't tried it with your data but try using this code

DatCol = Cells(5,Columns.Count).End(xlToLeft).Column

 

[R Krishna]

Hi
that helped me to get past that line but at the next line it stoped again!!
Range("A1" & ":" & DatCol & MyRo).Select
Run time Error 1004
Method "Range" of object "_Global"failed

what to do now?

TIA

RKrishna

 

[Bob Phillips]

That's because DatCol is a number, and you are trying to use it in A1
notation. Try this instead

Range("A1", Cells(MyRo, DatCol)).Select

See you on the next error<vbg>!

 

[R Krishna]

Thank you
That solved it

Rkrishna

That's because DatCol is a number, and you are trying to use it in A1
notation. Try this instead

Range("A1", Cells(MyRo, DatCol)).Select

See you on the next error<vbg>!

--

HTH

Bob Phillips