how to select cells dynamically in excel macro?
 

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

how to select cells dynamically in excel macro?
 

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

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com


"Excel user" <Excel wrote in message
...
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