VBA To copy a variable range of rows to another sheet

[CurtisC]

Hello everyone,

Like the typical guy that finally stops to ask for directions after driving in circles for hours, here I am! Though my circles have been going on for days now, I've finally broken down to ask for outside help in solving my vba issue.

If there is anyone here that could help with the following problem, I'd be greatly appreciative to you for your time and assistance.

I have imported data going into my source sheet "CF_Review_Import" range BM11:CZ500. The amount of rows could be anything from 1 to 500 to be safe, but always different amount of rows.

Once imported, I have Excel assign a code to each data row and then Excel checks this code against a data sheet that holds similar data and that also has the very same formula to assign code to this data. Purpose being that if the code comes up twice, Excel knows not to import this row of data because it's already in the data list.

So now I should be left with only the NEW data to import to my target sheet "CF_Data_Hold".

The problem: The ranges are not identical, the code is dirty as I've cannibalized it from various sources (Thanks Walkenbach!) and for some reason, it keeps pasting the imported data on row 500 even though I already have non duplicate data in those rows.

Here is the code I have so far;

Sub Copy_1_Value_Property()

Dim SourceRange As Range, DestRange As Range
Dim DestSheet As Worksheet, Lr As Long
Dim Notice1 As Variant


If Worksheets("CF_Review_Import").Range("AP9").Value = False Then

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'fill in the Source Sheet and range
Set SourceRange = Sheets("CF_Review_Import").Range("bm11:cz500")

'Fill in the destination sheet and call the LastRow
'function to find the last row
Set DestSheet = Sheets("CF_Data_Hold")
Lr = LastRow(DestSheet)

'With the information from the LastRow function we can create a
'destination cell
Set DestRange = DestSheet.Range("G" & Lr + 1)

'We make DestRange the same size as SourceRange and use the Value
'property to give DestRange the same values
With SourceRange
Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)
End With
DestRange.Value = SourceRange.Value

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

ElseIf Worksheets("CF_Review_Import").Range("AP9").Value = True Then
Notice1 = MsgBox("There are no more records to Import at this time", vbOKOnly, "Trainer's Aid Notice")


End If


End Sub


Function LastRow(sh As Worksheet)
On Error Resume Next
'You need to change G510 to the value that will be the NEW record start
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("G510"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


Any ideas where I went wrong?

Thanks in advance,

-Crazy in Toronto

[fredlo2010]

Hi, Can you update the code with code tags just highlight it and press "#" in the editing bar.

Thanks

[lexusap]

Hi Curtis,

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Cheers
Lex

[lexusap]

Hi Curtis,

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Cheers
Lex

[CurtisC]

Hello again,

Thanks to lexusap and fredlo2010 for your quick response.

I'm still looking for the # key on the "edit bar" and am starting to think this is perhaps some rookie initiation trick on me I'd still like to know what you mean by;

"...update the code with code tags just highlight it and press "#" in the editing bar."

And after I started on the mock up example, I thought I'd take one more try at it and I got it working!

My real problem it seems was with the lastrow function, it was not starting to drop the data on the right row that I wanted it on, so I added in some input for Excel to work with and directly pointed the lastrow function to the exact row I'm looking for by pointing it to a cell that contained the maximum row value.

And now, it works great.

I do find it strange that it can be fixed with such a simple direction statement. Almost too simple....?

So thanks again and thanks to the Excel Forum for allowing this communication to go on.

My issue is solved.




On Error Resume Next
'After:=sh.Range("G510"),
Dim LastRec As Integer

LastRec = Worksheets("CF_Data_Hold").Range("DE9").Value


LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("G" & LastRec), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

[fredlo2010]

Here

jkjk.png