0

I am struggling with an excel vba macro which will update the contents of a spreadsheet (call it the client activity log) with values from the master log spreadsheet when client log file is opened. The method which I was using was to copy and paste cells since I keep getting errors when I try to assign values directly. I know that this is poor form, and I have stability issues with the client log macro crashing sometimes for unclear reasons. So I'm trying to clean this up (and avoid copying unwanted formatting).

What I am doing now is:

Private Sub Workbook_Open()
    ...
    Run "Sheet2.UpdateActivites"
End Sub

The code for Sheet2:

Sub UpdateActivites() 
...   
Set MasterClientListWb = _
    Workbooks.Open(pathToMasterLogs & "Master Client Database.xlsx")
Set MasterClientListWs = MasterClientListWb.Sheets("Sheet1")

MasterClientListWs.Range("A2").Select
For i = 1 To MasterClientListWs.UsedRange.Rows.Count
    If ActiveCell.Value = clientNumber Then
        ' Sets last name
        ActiveCell.Offset(columnoffset:=1).Copy
        Me.Range("C1").PasteSpecial

        ' A lot more similar statements
     End If
Next i

As I understand it, what I should be doing is more like:

 Me.Cells("C1").Value = ActiveCell.Offset(columnoffset:=1).Value

but this throws an error whenever it runs. I suspect I may be referring to the file which I am opening vs the file which is running the macro incorrectly somehow.
I would really appreciate any help, I have fairly little experience with vba, as this no doubt shows. Thanks

L42
  • 19,427
  • 11
  • 44
  • 68
user3229306
  • 153
  • 1
  • 1
  • 8
  • I don't think you can assign to the `.Value`, which may be the source of your error. Also, here is a great S.O. link with tons of info on how to avoid using `.Select`: http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select – Dan Wagner Apr 16 '14 at 19:42
  • Better to write `UpdateActivies` in a `Module` rather than in a `Sheet Code`. Also instead of using `ActiveCell`, explicitly write the cell address you want to start your comparison. @DanWagner already provided the link which will help you improve your code. – L42 Apr 17 '14 at 03:32

1 Answers1

0

I think this sample will help you lock in what you'd like to do:

Option Explicit
Sub UpdateActivites()
'...other stuff
Dim MasterClientListWb As Workbook
Dim MasterClientListWs As Worksheet
Dim LastRow As Long

Set MasterClientListWb = Workbooks.Open(pathToMasterLogs & "Master Client Database.xlsx")
Set MasterClientListWs = MasterClientListWb.Sheets("Sheet1")

LastRow = MasterClientListWs.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

For i = 1 To LastRow
    If MasterClientListWs.Cells(i, 1).Value = clientNumber Then '<~~ if column A value matches client number
        MasterClientListWs.Cells(i, 3) = MasterClientListWs.Cells(i, 2).Value '<~~assign value to column C (i.e. column 3)
    End If
Next i
'...other stuff
End Sub
Dan Wagner
  • 2,693
  • 2
  • 13
  • 18
  • Will Me.Cells(i,3) work, as in Me.Cells(i,3) = MasterClientListWs.Cells(i,4).Value or do I need to use a reference (or whatever vba calls it) to my current sheet? I think you've mostly answered my question, but I need to copy values from a sheet in the file I've opened to a sheet in my current file. – user3229306 Apr 17 '14 at 19:28
  • I'm unfamiliar with `Me.Cells` so I'm afraid I can't definitively answer that. I do recommend using worksheet references everywhere though, as you can never be sure what a user might do while your code is running – Dan Wagner Apr 17 '14 at 21:17