0

I have a workbook called 'EvaluationLog.xlsm' and I need to transfer specific cells (not the whole row) from the first worksheet to another existing workbook called 'IndicatorLog.xlsm' located in the same directory. The target worksheet is also the first one. I'm trying to have the macro hosted in the 'IndicatorLog' workbook.

Specific cells in each row from the source are only to be copied if the contents in column 'O' is 'No' or if the contents of column 'J' is 'Initial'. The actual source data starts on row 8 and the target range also starts on row 8.

I've never had to code in VBA before except for a few very simple tasks, so I'm stuck.

Any help will be greatly appreciated! :)

Sub MergeFromLog()

Dim TargetSheet As Worksheet
Dim FolderPath As String
Dim NRow As Long
Dim SourceFileName As String
Dim WorkBk As Workbook
Dim LastRow As Integer, i As Integer, erow As Integer

' Set destination file.
Set TargetSheet = ActiveWorkbook.Worksheets(1)

' Modify this folder path to point to the files you want to use as source.
FolderPath = ""

' Set source file.
SourceFileName = FolderPath & "2015-2016 Evaluation Log.xlsm"

' NRow keeps track of where to insert new rows in the destination workbook.
NRow = 8

' Open the source workbook in the folder
Set WorkBk = Workbooks.Open(SourceFileName)

LastRow = WorkBk.ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

For i = 8 To LastRow

    If WorkBk.Range(“O” & i) = "No" Or WorkBk.Range(“J” & i) = “Initial” Then

        ' Copy Student Name
        TargetSheet.Range("A" & NRow).Value = WorkBk.Range(“A” & i).Value
        ' Copy DOB
        TargetSheet.Range("B" & NRow).Value = WorkBk.Range(“C” & i).Value
        ' Copy ID#
        TargetSheet.Range("C" & NRow).Value = WorkBk.Range(“D” & i).Value
        ' Copy Consent Day
        TargetSheet.Range("D" & NRow).Value = WorkBk.Range(“L” & i).Value
        ' Copy Report Day
        TargetSheet.Range("E" & NRow).Value = WorkBk.Range(“N” & i).Value
        ' Copy FIE within District Timelines?
        TargetSheet.Range("F" & NRow).Value = WorkBk.Range(“O” & i).Value
        ' Copy Qualified?
        TargetSheet.Range("H" & NRow).Value = WorkBk.Range(“A” & i).Value
        ' Copy Primary Eligibility
        TargetSheet.Range("I" & NRow).Value = WorkBk.Range(“U” & i).Value
        ' Copy ARD Date
        TargetSheet.Range("J" & NRow).Value = WorkBk.Range(“R” & i).Value
        ' Copy ARD within District Timelines?
        TargetSheet.Range("K" & NRow).Value = WorkBk.Range(“S” & i).Value
        ' Copy Ethnicity
        TargetSheet.Range("M" & NRow).Value = WorkBk.Range(“F” & i).Value
        ' Copy Hisp?
        TargetSheet.Range("N" & NRow).Value = WorkBk.Range(“G” & i).Value
        ' Copy Diag/LSSP
        TargetSheet.Range("O" & NRow).Value = WorkBk.Range(“X” & i).Value

        NRow = NRow + 1

    End If

Next i

End Sub
shA.t
  • 16,580
  • 5
  • 54
  • 111
Claudia Lorena
  • 43
  • 7

2 Answers2

1

I believe a range has to refer to a sheet.

Change

If WorkBk.Range(“O” & i) = "No" Or WorkBk.Range(“J” & i) = “Initial” Then

To

If WorkBk.ActiveSheet.Range("O" & i) = "No" Or WorkBk.ActiveSheet.Range("J" & i) = "Initial" Then
MatthewD
  • 6,719
  • 5
  • 22
  • 41
  • Good catch -- if her code is executing it will raise a 438 error on that line, but this is not the error that OP has, which is "code not doing anything". She will need to step through her code and figure out why the `For` loop is not executing. – David Zemens Jul 20 '15 at 16:55
  • Thank you both! Your answers helped me! Now I'm getting this error: 'Object doesn't support this property of method' in the following line: TargetSheet.Range("A" & NRow).Value = WorkBk.Range(“A” & i).Value – Claudia Lorena Jul 20 '15 at 17:05
  • Wasn't sure if the error would raise if "Option explicit" was not used. – MatthewD Jul 20 '15 at 17:06
  • @MatthewD it will raise an error, but it won't raise a compile error so the code can actually execute as long as those statements aren't executed. – David Zemens Jul 20 '15 at 17:07
  • Same issue. Change WorkBk.Range("A" & i).Value to WorkBk.ActiveSheet.Range("A" & i).Value – MatthewD Jul 20 '15 at 17:08
  • @David Zemens Thanks for the info. – MatthewD Jul 20 '15 at 17:11
  • I truly appreciate all your help, guys!! I'm finally making progress thanks to you!! Now I'm getting this error: Application-defined or object-defined error (1004)' in the same line: TargetSheet.Range("A" & NRow).Value = WorkBk.ActiveSheet.Range(“A” & i).Value – Claudia Lorena Jul 20 '15 at 17:14
  • Is your code "behind" one of the sheets? This may be a problem with it wanting to write to the active sheet. I don't get any error and i have the code behind a form. – MatthewD Jul 20 '15 at 17:21
  • Thanks, Matthew! I'm not sure. :( I entered the code by going to Macros and clicking on 'Create'. Should I be doing something different? – Claudia Lorena Jul 20 '15 at 17:24
  • Matthew, I think I know what you meant now. I added a button and assigned the macro to the button, but I still got the same error. Not sure what to do now. :/ – Claudia Lorena Jul 20 '15 at 17:47
  • I'm not sure. I would open a new question with your code in it's current condition and the title of the error you are getting and copying data from one sheet to another. – MatthewD Jul 20 '15 at 18:01
1

I'm guessing that the value of LastRow is <= 8, so the For i = 8 to lastRow loop is simply not executing.

For better method of finding the last row, refer here:

Error in finding last used cell in VBA

If it were executing, most of the statements within the loop will raise a 438 error, as @MatthewD notes in his answer, the Workbook object does not have a Range method, you would have to qualify .Range to a specific Worksheet object within the workbook.

All of the statements like ... WorkBk.Range(... must be changed to something like:

... WorkBk.ActiveSheet.Range(...

Community
  • 1
  • 1
David Zemens
  • 53,033
  • 11
  • 81
  • 130