Solved: copying dynamic range from Sheet1 to Sheet2

[Anomandaris]

I'm trying to copy dynamic ranges across sheets. Lets say Sheet 1 has a dynamic range of data in Column C that I need to be copied to Sheet 2, Column F......how do I do this?

thanks

[p45cal]

You have set up a Named range for the dynamic range?

Otherwise the likes of:[vba]Range(Sheets("Sheet1").Range(C3"), Sheets("Sheet1").Range("C13").End(xlDown)).Copy Sheets("Sheet2").Range("F26")[/vba]
?

[Anomandaris]

Yes I have named the dynamic ranges in both sheets,
thanks I will try this method

[mdmackillop]

If both ranges are named, try
[VBA]
Range("Trial").Copy Range("Test")

[/VBA]

[Anomandaris]

thanks mdmack...it works but how do i tweak it to copy the cell value without the formula
The dynamic range i'm copying has formula in it

thanks

[p45cal]

Range("Test")=Range("Trial").value

or you may have to specify the size of the destination range, so if that fails try:

Range("Test").resize(Range("Trial").rows.count,Range("Trial").Columns.Count )=Range("Trial").value

(untested)

[Anomandaris]

thnks guys it works but its only copying the first 52 rows,
not sure why its not capturing the whole range

is there a problem with the named range?
this is how i named the dynamic ranges using the following macro


[VBA]
Sub CreateNames()

Dim wb As Workbook, ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long
Dim myName As String, Start As String
' set the row number where headings are held as a constant
' change this to the row number required if not row 1
Const Rowno = 1
' set the Offset as the number of rows below Rowno, where the
' data begins
Const ROffset = 1
' set the starting column for the data, in this case 1
' change if the data does not start in column A
Const Colno = 1

' Set an Offset from the starting column, for the column number that
' will always have data entered, and will therefore be used in calculating lrow

Const COffset = 0 ' in this case, the first column will always contain data.
On Error GoTo CreateNames_Error
Set wb = ActiveWorkbook
Set ws = ActiveSheet
' count the number of columns used in the row designated to
' have the header names

lcol = Cells(Rowno, Columns.Count).End(xlToLeft).Column
lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row
Start = Cells(Rowno, Colno).Address

wb.Names.Add Name:="lcol", RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")"
wb.Names.Add Name:="lrow", RefersToR1C1:="=COUNTA(C" & Colno + COffset & ")"
wb.Names.Add Name:="myData", RefersTo:= _
"=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)"
For i = Colno To lcol
' if a column header contains spaces, replace the space with an underscore
' spaces are not allowed in range names.
myName = Replace(Cells(Rowno, i).Value, " ", "_")
If myName = "" Then
' if column header is blank, warn the user and stop the macro at that point
' names will only be created for those cells with text in them.
MsgBox "Missing Name in column " & i & vbCrLf _
& "Please Enter a Name and run macro again"
Exit Sub
End If
wb.Names.Add Name:=myName, RefersToR1C1:= _
"=R" & Rowno + ROffset & "C" & i & ":INDEX(C" & i & ",lrow)"
nexti:
Next i
On Error GoTo 0
MsgBox "All dynamic Named ranges have been created"
Exit Sub
Exit Sub
CreateNames_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CreateNames of Module Technology4U"
End Sub

[/VBA]

so my named range looks like

=Page!$C$2:INDEX(Page!$C:$C,lrow)
where lrow is =COUNTA(Page!$A:$A)


is there smthn wrong with that?

thanks

[Anomandaris]

i can see now that the problem is with the named ranges, not your vba.....it just selects 52 rows for the range, not sure why

[p45cal]

Are there as many missing rows as there are blanks in the first column of your data?

[Anomandaris]

no, I have around 600 rows of data but the named range is only selecting 52 rows

you can check the file........I'm trying to copy Column D from Sheet'Fund1' to Column H in Sheet'Omega'

[p45cal]

if you look at the refersto: of lrow it is:
=COUNTA(Omega!$A:$A)
not what you intended. In the spreadsheet you attached this comes to 0 for lrow, but I suspect that you have 52 non-empty cells in column A of the Omega sheet in your version.

Type
=lrow
in any cell and see what it returns.

Your code to define the lrow name is:
[vba]wb.Names.Add Name:="lrow", RefersToR1C1:="=COUNTA(C" & Colno + COffset & ")"[/vba] which doesn't specify the sheet. Excel adds the sheet reference itself- the active sheet name. So either specify the sheet or make sure the right sheet is active first.

[Anomandaris]

you're right i see the problem, I'm just going to manually name the dynamic ranges to avoid this problem

thanks again for your help!