0

I have a a piece of code that is being called from a front sheet within a workbook and making reference to two sheets within the work book. I know this code does what I need it to do when I am on the "Data" tab. However when I run it from the "Control Menu" tab it does not populate. I can see where this is failing, because when I look at the line:

If Range("B" & i + 1).Offset(0, -1).Value = 1 And Range("B" & i + 1).Value = "UK" Then

I hover my cursor over the .Value command and box displays 'Empty', so I know that this code is looking at the 'Control Menu' tab even though I am starting my code using the With Sheets("Data") command.

Essentially on the Data tab I have a few hundred rows of data being pulled through and I'm trying to loop through table copy out the data where the ReportID = 1 and the Country = UK and move it to another tab where I have charts linking to.

The full code is below:

With Sheets("Data")

    RowCount = .Cells(.Rows.Count, "B").End(xlUp).Row

    For i = 1 To RowCount

     Range("B1").Offset(1, 0).Select

    If Range("B" & i + 1).Offset(0, -1).Value = 1 And Range("B" & i + 1).Value = "UK" Then

        ActiveSheet.Cells.Range(Selection, Selection.End(xlToRight)).Select
        Selection.Copy
        With Sheets("DynamicCharts")
            .Range("A" & Cells.Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End With

    End If

Next i

End With

Any help would be much appreciated.

Thanks in advance

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Carlos80
  • 433
  • 15
  • 32
  • 2
    You need to predfix lines like this `Range("B1").` `with` `.` as you are using with so `.Range("B1").` etc. – Nathan_Sav Feb 28 '19 at 15:47
  • Your `With` block only works if you use `.Range` rather than just `Range` – Rory Feb 28 '19 at 15:47
  • Thanks I updated to .Range("B1") and got the following error Select method of range class failed. I know using select is a bad habit but I need to start at this cell before I loop through. – Carlos80 Feb 28 '19 at 15:57
  • You can only select a cell on the active sheet. The best thing is to avoid Select as you say, otherwise you have to activate the Data sheet first. – SJR Feb 28 '19 at 16:00
  • 1
    Do not use `.Select`. This is a front end think we do to communicate to the back end. You are working on the back end so cut out the middle man steps. VBA can be indifferent to what sheet is active or what cell is selected. When you code like this, you get better run times and usually get less errors – urdearboy Feb 28 '19 at 16:00
  • Thanks but how do you reference a specific cell in a back end sheet without using select? Sorry I know its probably a basic question. – Carlos80 Feb 28 '19 at 16:02
  • 1
    Read https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – SJR Feb 28 '19 at 16:08
  • Thanks SJR some really interesting examples on that link. However I still can't work out how you combine a With within an If statement like in the line below: If Range("B" & i + 1).Offset(0, -1).Value = 1 And Range("B" & i + 1).Value = "UK" Then – Carlos80 Mar 01 '19 at 10:25

0 Answers0