VBA Offset copy and pasting

[mvervair]

Ok, I'm back at it with another copy and paste question. In this sub I have a cell I click on(customer name) and it begins copy and pasting information from one sheet to another. However I'm not even able to compile this code. I have a feeling the ActiveCell is causing the problem. Any ideas?

Dim jobTracking As Worksheet
Dim resApp As Worksheet

Set resApp = Worksheets("residential_applications")
Set jobTracking = Worksheets("residential_job_tracking")

    jobTracking.Range("C1").End(xlDown).Offset(0, 1).Value = resApp.ActiveCell.Value
    'acct
    jobTracking.Range("C1").End(xlDown).Offset(-1, 1).Value = resApp.ActiveCell.Offset(-1, 0).Value
    'address
    jobTracking.Range("C1").End(xlDown).Offset(1, 1).Value = resApp.ActiveCell.Offset(3, 0).Value
    'city
    jobTracking.Range("C1").End(xlDown).Offset(2, 1).Value = resApp.ActiveCell.Offset(4, 0).Value
    'zip
    jobTracking.Range("C1").End(xlDown).Offset(3, 1).Value = resApp.ActiveCell.Offset(5, 0).Value
    'app received date
    jobTracking.Range("C1").End(xlDown).Offset(8, 1).Value = resApp.ActiveCell.Offset(-2, 0).Value
    'phone
    jobTracking.Range("C1").End(xlDown).Offset(12, 1).Value = resApp.ActiveCell.Offset(2, 0).Value
    'em
    jobTracking.Range("C1").End(xlDown).Offset(13, 1).Value = resApp.ActiveCell.Offset(1, 0).Value

 

[JoeMo]

What is the compile error(s) you get? Wiil you run this code while the sheet resApp is the active sheet?

 

[mvervair]

It says method or data member not found, then highlights the first ActiveCell in the code.

Whether I run it in the resApp or jobTracking sheets it throws the same error.

 

[JoeMo]

It says method or data member not found, then highlights the first ActiveCell in the code.

Whether I run it in the resApp or jobTracking sheets it throws the same error.

Try running it from the resApp sheet after you remove "resApp." from the right side of the "=" sign on every line. The active cell, by definition, is on the active sheet (only) and so neither needs nor wants a sheet qualifier.

Also, be sure you have something below C1 on the jobTracking sheet or End(xlDown) will take you to the last cell at the bottom of the sheet and your attempts to offset that by +ve row amounts will take you off the sheet and generate a run time error.

 

[mvervair]

Yes, since the end(xlDown) is in every line it was starting an additional row after it created the first new row so I had to change some things. Also I got my offset values backwards in my first post. Here's the final version that is working:

    jobTracking.Range("C1").End(xlDown).Offset(1, 0).Value = ActiveCell.Value
    'acct
    jobTracking.Range("C1").End(xlDown).Offset(0, -1).Value = ActiveCell.Offset(0, -1).Value
    'address
    jobTracking.Range("C1").End(xlDown).Offset(0, 1).Value = ActiveCell.Offset(0, 3).Value
    'city
    jobTracking.Range("C1").End(xlDown).Offset(0, 2).Value = ActiveCell.Offset(0, 4).Value
    'zip
    jobTracking.Range("C1").End(xlDown).Offset(0, 3).Value = ActiveCell.Offset(0, 5).Value
    'app received date
    jobTracking.Range("C1").End(xlDown).Offset(0, 8).Value = ActiveCell.Offset(0, -2).Value
    'phone
    jobTracking.Range("C1").End(xlDown).Offset(0, 12).Value = ActiveCell.Offset(0, 2).Value
    'em
    jobTracking.Range("C1").End(xlDown).Offset(0, 13).Value = ActiveCell.Offset(0, 1).Value