PDA

View Full Version : Macro to Select Range and Copy



brennaboy
08-20-2010, 04:07 AM
Hi,

This is probably an easy question, but I can't get my head around it.

I want to do a macro to do the following:

Select a range which starts at cell A2 and goes right down to the end of the data (whcih varies) AND also then goes along to column V (which is always fixed)

Then Copy this selection and go to Workbook "Download Template" and paste at Cell A2.

Can this be done easily?

Cheers,

B.

Bob Phillips
08-20-2010, 04:18 AM
Off the top



Set rng = Range(Range("A2"), Range("A2").End(xlDown)).Resize(, 22)
rng.Copy Worksheets("Download Template").Range("A2")

Artik
08-20-2010, 04:36 AM
Sub CopyPasteRange()
Dim lRows As Long

With Range("A2")
lRows = .End(xlDown).Row - .Cells.Row + 1

.Resize(lRows, 22).Copy Workbooks("Download Template.xls").ActiveSheet.Range("A2")

End With
End Sub xld, I think that should be "Download Template.xls"

Artik

brennaboy
08-20-2010, 04:43 AM
Hi,

That works great.

Also - what I want to do is after that to close the original Workbook that the copied data came from.

The Workbook name changes (it is usually Book1) but not always.

So I guess what I need to do is before it selects the data, copies and pastes it. I need to store the name of the workbook as a string or something and then at the end of the macro tell it to close the workbook.

How do I do this?

Cheers,

B.

Bob Phillips
08-20-2010, 04:57 AM
Set wb = Activeworkbook
Set ws = wb.Worksheets("Sheet1")
Set rng = ws.Range(ws.Range("A2"), ws.Range("A2").End(xlDown)).Resize(, 22)
rng.Copy Worbooks("Download Template.xls").Worksheets(1).Range("A2")
wb.Close SaveChanges:=False

brennaboy
08-20-2010, 05:27 AM
This is all great stuff, but I have encountered another problem.

I am using the previously suppied code (as at the bottom) in another macro, but this time some of the cells in column A is blank so it is not selecting all the range down. Column B will always have data to the bottom, is there a way to use this column and then expand the range to the left one and the right 8? Or a better way?


Set rng = Range(Range("A2"), Range("A2").End(xlDown)).Resize(, 9)
rng.Copy Workbooks("Polaris download Template.xls").Worksheets("All Field Breaks").Range("A2")

Bob Phillips
08-20-2010, 05:47 AM
Set wb = Activeworkbook
Set ws = wb.Worksheets("Sheet1")
Lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Set rng = ws.Range("A2").Resize(Lastrow -1, 22)
rng.Copy Worbooks("Download Template.xls").Worksheets(1).Range("A2")
wb.Close SaveChanges:=False

brennaboy
08-20-2010, 08:19 AM
Hi,

I have a worksheet. The left half of the worksheet Cols A - W is populated with 3000 odd rows of data.

Range V2:AI2 has a row if formulas.

I want to write a macro that will copy down this row of formulas down to the bottom.

The way I thought I could do it was to use:

r = Application.CountA(Range("A:A"))

to count the number of rows of data and then use:

Range("W2:AI2").Select
Selection.AutoFill Destination:=Range("W2:AIr"), Type:=xlFillDefault
Range("W2:AIr").Select
ActiveSheet.Calculate

I am getting a debug because AIr is not right - what is the right way to express this OR is there a better way to do this?

Cheers,

B.

Bob Phillips
08-20-2010, 02:35 PM
Range("W2:AI2").AutoFill Destination:=Range("W2:AI2").Resize(r - 1), Type:=xlFillDefault
Range("W2:AI2").Resize(r - 1).Select
ActiveSheet.Calculate