Dim lastCol as Long


With Worksheets("DataAll")
lastCol = .Range("IV" & 1).End(xltoLeft).Column
.Cells(1,lastCol + 1,).PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With



--
Regards,
Tom Ogilvy


"saziz" wrote in
message ...

Thank you both for the help.

Actually I have a code which is working fine. But it copies a dynamic
range from an active work bood and go to DataAll work book and find the
last row with data and pate below it.

Now what I wanted was to copy and go to DataAll and instead of finding
an empty row, find the next empty column and paste it. I am putting my
code here. i would appreciate very much if you can make it work.

I tried Tom's changes it gives me an error 'object not defined'

Sub mycode11()

Dim c As Range
Dim rRng As Range
Dim lastRow As Long
Dim count As Long


'Formula Worksheets(1).Range("a2") = Mid(CELL("filename", A1),
Find("]", CELL("filename", A1)) + 1, 255)
With Worksheets(1).Range("A2").Select
ActiveCell.FormulaR1C1 = _


"=MID(CELL(""filename"",R[-3]C[-6]),FIND(""["",CELL(""filename"",R[-3]C[-6])
)+1,FIND(""]"",CELL(""filename"",R[-3]C[-6]))-FIND(""["",CELL(""filename"",R
[-3]C[-6]))-1)"

With Worksheets(1).Range("A:A")
Set c = .Find("Ave", LookIn:=xlValues)
If Not c Is Nothing Then
Range("A1:E" & c.Row - 1).Copy
End If
End With

Windows("DataAll.xls").Activate
With Worksheets("DataAll")
lastRow = .Range("B" & Rows.count).End(xlUp).Row (This is where I want
the change from row to column)
Cells(lastRow + 1, 1).PasteSpecial Paste:=xlValues _
, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End WithEnd With
Application.CutCopyMode = False
count = 5
Do

If Application.Range("B" & count) = "Orifice Axis 1" Then
Application.Range("B" & count).Select
Selection.EntireRow.Delete
End If
count = count + 1
Loop Until Application.Range("B" & count).Value = ""

End Sub

Thank you
Syed


--
saziz
------------------------------------------------------------------------
saziz's Profile:
http://www.excelforum.com/member.php...fo&userid=6350
View this thread: http://www.excelforum.com/showthread...hreadid=494770