Copy dynamic range
 

Hello
I have some code that was given to me and I have modified. I am trying to
modify again and I am having trouble. I am opening files manipulating data
and then copying to another workbook where inthe macro lies. All of the
manipulation works but when I try to copy I am getting nothing. I am
thinking that it may be in my use of "lastrow" but I am not sure how to fix
it. This is the excerpt of the code...

lastrow = Cells(Cells.Rows.Count, "a").End(xlUp).Row
Set sourceRange = mybook.Worksheets(1).Range("A2:H" & lastrow)
Call SortArray(MyFiles)

SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Range("A" & rnum)

SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Range("A" & rnum)

With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum,
"A"). _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value




Sub SortArray(myArr As Variant)
Dim iCtr As Long
Dim jCtr As Long
Dim Temp As Variant
For iCtr = LBound(myArr) To UBound(myArr) - 1
For jCtr = iCtr + 1 To UBound(myArr)
If LCase(Right(myArr(iCtr), 10)) _
LCase(Right(myArr(jCtr), 10)) Then
Temp = myArr(iCtr)
myArr(iCtr) = myArr(jCtr)
myArr(jCtr) = Temp
End If
Next jCtr
Next iCtr
End Sub


TIA
Sandy

Copy dynamic range
 

from
lastrow = Cells(Cells.Rows.Count, "a").End(xlUp).Row
to

lastrow = mybook.Worksheets(1).Cells(Rows.Count, "a").End(xlUp).Row



"Sandy" wrote:

Hello
I have some code that was given to me and I have modified. I am trying to
modify again and I am having trouble. I am opening files manipulating data
and then copying to another workbook where inthe macro lies. All of the
manipulation works but when I try to copy I am getting nothing. I am
thinking that it may be in my use of "lastrow" but I am not sure how to fix
it. This is the excerpt of the code...

lastrow = Cells(Cells.Rows.Count, "a").End(xlUp).Row
Set sourceRange = mybook.Worksheets(1).Range("A2:H" & lastrow)
Call SortArray(MyFiles)

SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Range("A" & rnum)

SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Range("A" & rnum)

With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum,
"A"). _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value




Sub SortArray(myArr As Variant)
Dim iCtr As Long
Dim jCtr As Long
Dim Temp As Variant
For iCtr = LBound(myArr) To UBound(myArr) - 1
For jCtr = iCtr + 1 To UBound(myArr)
If LCase(Right(myArr(iCtr), 10)) _
LCase(Right(myArr(jCtr), 10)) Then
Temp = myArr(iCtr)
myArr(iCtr) = myArr(jCtr)
myArr(jCtr) = Temp
End If
Next jCtr
Next iCtr
End Sub


TIA
Sandy

Copy dynamic range
 

Thanks for the response.
I stll get nothing after your suggested change.
S

"Joel" wrote:

from
lastrow = Cells(Cells.Rows.Count, "a").End(xlUp).Row
to

lastrow = mybook.Worksheets(1).Cells(Rows.Count, "a").End(xlUp).Row



"Sandy" wrote:

Hello
I have some code that was given to me and I have modified. I am trying to
modify again and I am having trouble. I am opening files manipulating data
and then copying to another workbook where inthe macro lies. All of the
manipulation works but when I try to copy I am getting nothing. I am
thinking that it may be in my use of "lastrow" but I am not sure how to fix
it. This is the excerpt of the code...

lastrow = Cells(Cells.Rows.Count, "a").End(xlUp).Row
Set sourceRange = mybook.Worksheets(1).Range("A2:H" & lastrow)
Call SortArray(MyFiles)

SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Range("A" & rnum)

SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Range("A" & rnum)

With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum,
"A"). _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value




Sub SortArray(myArr As Variant)
Dim iCtr As Long
Dim jCtr As Long
Dim Temp As Variant
For iCtr = LBound(myArr) To UBound(myArr) - 1
For jCtr = iCtr + 1 To UBound(myArr)
If LCase(Right(myArr(iCtr), 10)) _
LCase(Right(myArr(jCtr), 10)) Then
Temp = myArr(iCtr)
myArr(iCtr) = myArr(jCtr)
myArr(jCtr) = Temp
End If
Next jCtr
Next iCtr
End Sub


TIA
Sandy

Copy dynamic range
 

Nevermind its working THANKS!

"Joel" wrote:

from
lastrow = Cells(Cells.Rows.Count, "a").End(xlUp).Row
to

lastrow = mybook.Worksheets(1).Cells(Rows.Count, "a").End(xlUp).Row



"Sandy" wrote:

Hello
I have some code that was given to me and I have modified. I am trying to
modify again and I am having trouble. I am opening files manipulating data
and then copying to another workbook where inthe macro lies. All of the
manipulation works but when I try to copy I am getting nothing. I am
thinking that it may be in my use of "lastrow" but I am not sure how to fix
it. This is the excerpt of the code...

lastrow = Cells(Cells.Rows.Count, "a").End(xlUp).Row
Set sourceRange = mybook.Worksheets(1).Range("A2:H" & lastrow)
Call SortArray(MyFiles)

SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Range("A" & rnum)

SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Range("A" & rnum)

With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum,
"A"). _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value




Sub SortArray(myArr As Variant)
Dim iCtr As Long
Dim jCtr As Long
Dim Temp As Variant
For iCtr = LBound(myArr) To UBound(myArr) - 1
For jCtr = iCtr + 1 To UBound(myArr)
If LCase(Right(myArr(iCtr), 10)) _
LCase(Right(myArr(jCtr), 10)) Then
Temp = myArr(iCtr)
myArr(iCtr) = myArr(jCtr)
myArr(jCtr) = Temp
End If
Next jCtr
Next iCtr
End Sub


TIA
Sandy