PDA

View Full Version : Solved: How to copy a dynamic range



IgnBan
08-08-2008, 10:34 AM
How can I modify this code to select a dynamic range;
I need to select the range that I'm coping, starting in the column A 5th row to colunm Y. The column that will always have data in the last row will be column C. I also want to copy the data with all the formulas formatting, etc exactly as is on the copied sheet.


Sub Paste_Data()


Range("J55:AR55").Select
Selection.Copy

Call OpenWB

ActiveWorkbook.Range("A").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Paste

ActiveWorkbook.Save

ActiveWorkbooks.Close

End Sub


Sub OpenWB()
Test = "C\Test.xls" 'Open File...........
Workbooks.Open Filename:=Test
End Sub



Thanks in advance for any input. :thumb

Bob Phillips
08-08-2008, 10:46 AM
Sub Paste_Data()
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
.Range("A5").Resize(LastRow - 4, 25).Copy
End With
Call OpenWB
ActiveSheet.Range("A1").Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

Sub OpenWB()
Test = "C\Test.xls" 'Open File...........
Workbooks.Open Filename:=Test
End Sub

IgnBan
08-08-2008, 11:11 AM
Great!

Thanks XLD...works perfect. in regard to copy all the cell attributes/properties, will the simple copy command will do it?


Thanks again.

IgnBan

Bob Phillips
08-08-2008, 11:35 AM
Should do.

IgnBan
08-08-2008, 02:10 PM
Thanks!