1

First off, I am extremely new to excel vba. I have some code that works well in one workbook but doesn't work in the workbook I need it in. When using F8, it skips over the portion of the code beginning with If Cells(i, 4) = customername Then through End If.

I have searched for several days trying to find an answer online. When I run the macro, the screen flashes but the data disappears. Here is the code I am trying to use, any help will be greatly appreciated. 

Sub CustomerReport()
    Dim datasheet As Worksheet
    Dim reportsheet As Worksheet
    Dim customername As String
    Dim finalrow As Integer
    Dim i As Integer

    Set datasheet = Sheet3
    Set reportsheet = Sheet8
    customername = reportsheet.Range("D6").Value

    reportsheet.Range("C8:M500").ClearContents

    datasheet.Select
    finalrow = Cells(Rows.Count, 1).End(xlUp).Row

    For i = 8 To finalrow
        If Cells(i, 4) = customername Then
            Range(Cells(i, 3), Cells(i, 13)).Copy
            reportsheet.Select
            Range("C200").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
            datasheet.Select
            End If
    Next i
    reportsheet.Select

    Range("D6").Select
End Sub
Community
  • 1
  • 1
NewtoVBA
  • 11
  • 2
  • 1
    [THIS](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros/10718179#10718179) will get you started. I believe if you avoid using select and fully qualify the objects then you will not face the issues :) – Siddharth Rout Jun 14 '17 at 21:17
  • What I mean is, see how you used `reportsheet` in `reportsheet.Range("D6").Value` but forgot to do the same in `If Cells(i, 4) = customername Then` and other places? :) – Siddharth Rout Jun 14 '17 at 21:22
  • Put a message box to get the value of finalrow after the row `finalrow = Cells(Rows.Count, 1).End(xlUp).Row`, what shows up? – dwirony Jun 14 '17 at 21:22
  • @SiddharthRout - `If Cells(i, 4) = customername Then` is inherently referencing the `datasheet` worksheet. –  Jun 14 '17 at 21:27
  • 1
    Are you sure you want to `xlPasteFormulasAndNumberFormats`? Typically a report does not rely upon formulas for its numbers; especially so when data is copied from another worksheet. –  Jun 14 '17 at 21:29
  • @jeeped. True that. I am advising the user to fully qualify the cells to negate any possibility of the code not finding a match. BTW I just posted a bug in meta. You may want to see it [Here](https://meta.stackexchange.com/questions/297189/incorrect-information-portrayed-for-closed-quesitons) – Siddharth Rout Jun 14 '17 at 21:38

0 Answers0