How to copy data to another spreadsheet? see end comment

[kremstep]

Sub Macro7()
'
' Macro7 Macro


Dim lastcell As String
Dim rangeStr As String

Sheets("Sheet2").Select
Range("R1").Select
Selection.AutoFilter

Selection.AutoFilter Field:=1, Criteria1:="<>"
Range("S2:T100").Select ' assumes there will not be more than 100
missing symbols

Selection.Copy
Windows("Suke Cusips.xls").Activate
lastcell = CStr(Range("A2").End(xlDown).Row + 1)
rangeStr = "A2:B" + lastcell
Range(rangeStr).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False 'Problem with formula is when it copies
and pastes it tells me that the cell size is not the same size. So I
must resize cell or choose cell range. You can not choose cell range
because it is always changing as you add data. How would you solve
this problem?
End Sub

 

[Damon Longworth]

Have you tried pasting into A2 only?

--

Damon Longworth

2007 East Coast Excel / Access User Conference
April 18-20, 2007 - Providing Microsoft Excel training and Microsoft Excel
Classes
Atlantic City, New Jersey
www.ExcelUserConference.com/ECEUC.html

Sub Macro7()
'
' Macro7 Macro


Dim lastcell As String
Dim rangeStr As String

Sheets("Sheet2").Select
Range("R1").Select
Selection.AutoFilter

Selection.AutoFilter Field:=1, Criteria1:="<>"
Range("S2:T100").Select ' assumes there will not be more than 100
missing symbols

Selection.Copy
Windows("Suke Cusips.xls").Activate
lastcell = CStr(Range("A2").End(xlDown).Row + 1)
rangeStr = "A2:B" + lastcell
Range(rangeStr).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False 'Problem with formula is when it copies
and pastes it tells me that the cell size is not the same size. So I
must resize cell or choose cell range. You can not choose cell range
because it is always changing as you add data. How would you solve
this problem?
End Sub