how to select cells dynamically in excel macro?

[Guest]

Following is the sample macro. I want to get 2 worksheets, one beside the
other on a single new worksheet.

Sub Macro7()
Workbooks.Open Filename:="C:\Sujatha\Excel\Jan_sales.xls"
Application.Goto Reference:="JAN_SALES"
Selection.Copy
Windows("Book3.xls").Activate
ActiveSheet.Paste
Range("D1").Select
Application.CutCopyMode = False
Workbooks.Open Filename:="C:\Sujatha\Excel\Feb_sales.xls"
Application.Goto Reference:="FEB_SALES"
Selection.Copy
Windows("Book3.xls").Activate
ActiveSheet.Paste
End Sub

I have specified 'Range("D1").Select' explictly. Suppose I don't know where
the first range ends, I want the macro to go to the last +2 columns and then
make the next copy.

Thanks in advance

 

[Dick Kusleika]

Excel user

Try this:

Dim rPaste as Range
Dim wbCopy as Workbook

Set wbCopy = Workbooks.Open("C:\Sujatha\Excel\Jan_sales.xls")
Set rPaste = Workbooks("Book3.xls").Sheets(1).Range("A1")
wbCopy.Sheets(1).Range("JAN_SALES").Copy Destination:=rPaste

Set rPaste = rPaste.End(xlToRight).Offset(0,2)
wbCopy.Close False

Set wbCopy = Workbooks.Open("C:\Sujatha\Excel\Feb_sales.xls")
wbCopy.Sheets(1).Range("FEB_SALES").Copy Destination:=rPaste
wbCopy.Close False