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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.