VBA Copy range

[edgarsrom]

Hello,

I require some help on modifying below code to be able to copy range from one workbook into another workbook only into blank cells:

Sub CopyRangeToRange()
    Dim CpyFrom As Range
    Dim Cell As Range
    
    Set CpyFrom = ActiveSheet.Range("A:A")


    For Each Cell In CpyFrom
        If Cell.Value <> vbNullString Then
            Cell.Offset(0, 2).Value = Cell.Value
        End If
    Next Cell
End Sub

So this is working if I need to paste range in the same worksheet - into column C, but I require to paste it into different workbook and for Range("A:O").

Any help much appreciated.

 

[edgarsrom]

Hello, I have added ID numbers on both spread sheets, added your code into module and when I try to execute it, then error appears on:

  logstart = Columns("A:A").Find(What:=anmax, After:=Range("A1"), LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Row + 1

Run-time error '91': Object variable or With block variable not set

Apologies for delay in reply.

 

[Joe4]

That means the value you are searching for is not found!
You will need to add some error handling code, like this:

Sub GetNewData()

    Dim anWB As Workbook
    Dim logWB As Workbook
    Dim anlr As Long
    Dim loglr As Long
    Dim anmax As Long
    Dim logmax As Long
    Dim logstart As Long
    
    Application.ScreenUpdating = False
    
'   Capture activework (Analysis workbook)
    Set anWB = ActiveWorkbook
    
'   Find last row in column A of Analysis workbook
    anlr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Find maximum ID in column A of Analysis workbook
    anmax = Application.WorksheetFunction.Max(Range("A:A"))
    
'   Go to Log workbook
    Windows("Log.xlsx").Activate

'   Capture activeworkbook (Log workbook)
    Set logWB = ActiveWorkbook
    
'   Find last row in column A of Analysis workbook
    loglr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Find maximum ID in column A of Analysis workbook
    logmax = Application.WorksheetFunction.Max(Range("A:A"))

'   Find row to start copy from
    On Error GoTo err_chk
    logstart = Columns("A:A").Find(What:=anmax, After:=Range("A1"), LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Row + 1
    On Error GoTo 0

'   Copy data from Log to Analysis
    If (logstart <= loglr) Then
        Range(Cells(logstart, "A"), Cells(loglr, "O")).Copy
        anWB.Activate
        Cells(anlr + 1, "A").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
    Else
        MsgBox "No new rows to copy"
    End If
    
    Application.ScreenUpdating = True
    
    Exit Sub
    
    
err_chk:
    If Err.Number = 91 Then
        MsgBox anmax & " cannot be found", vbOKOnly, "ERROR!"
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
    Application.ScreenUpdating = True
    
End Sub

 

[edgarsrom]

Thanks for your reply Joe4, I think the problem is that it is looking for "0" using anmax variable because on Analysis tab I have no records at the moment. I have added one record manually in the 2nd row(just under headings) with ID nr. "1" and it copied ID 2,3 and 4(all records from Log) as I require. Is there any way to fix it? i.e. if I have no records on Analysis tab at all then to copy all records from Log?

Thank you so much for all your help!!! I appreciate VBA code you wrote for me, and thank you for your time!

 

[Joe4]

I have no records at the moment.

So, is there anything at all in column A (including a header), or is it completely blank?

 

[edgarsrom]

Column A at the moment has only Header "Enquiry ID" in Cell A1, when I tried to execute your code it returned ERROR 0 not found(as you added error handling to the code). Then I tested your code other way(as I mentioned in my last reply) and added first record in 2nd row where I inputted manually in cell A2 number 1(like ID number "1") and on Log sheet tab I have 4 records, so your code copied ID 2,3 and 4. Code is working like it should be, just that one small thing left to fix, if it is difficult let's just leave it and I will make sure that I keep one record in the second row all the time.

 

[Joe4]

You could use an IF function to check to see if there are any records in column A, i.e.:

If Cells(Rows.Count,"A").End(xlUp).Row > 1 Then
[I]'    what to do if there is data in column A[/I]
Else
[I]'    what to do if there is no data in column A[/I]
End If

So, you would place that code before the anmax calculation. Most of your current code would go in the first spot, while the new code you need to add to copy all data if there is nothing in column A would go under the "Else" statement.

See if you can incorporate that into your code.

 

[edgarsrom]

Thank you very much Joe4, I am not sure if this is the right way, but I just wrote:

If Cells(Rows.Count, "A").End(xlUp).Row < 2 Then

Then - Open Log workbook and copy everything from range A2:P100000 and paste into "Analysis" workbook range A2.

So if there are only headings in my Analysis workbook without any data it will copy everything from Log else will run your script and copy only missing ID's.

I am sure there is a better way of incorporating it into your code(I just do not have that knowledge), but it is working for me. Thank you very much for your time and support!!!