0

I'm trying to write a code that will switch back and forth between sheets, taking the inputs from one sheet and then pasting them on another sheet. This is the code. 

For r = 1 To ActiveCell.End(xlDown).Row
    Cells(1, c).Select
        Do Until IsEmpty(Selection)

            name = ActiveCell.End(xlToLeft).Value
            email = ActiveCell.End(xlToLeft).Offset(0, 1).Value
            phoneNumber = ActiveCell.End(xlToLeft).Offset(0, 2).Value
            generation = ActiveCell.End(xlToLeft).Offset(0, 3).Value
            status = ActiveCell.End(xlToLeft).Offset(0, 13).Value
            ActiveCell.Offset(1, 0).Select
        Sheets("Compiled Data").Select

'paste data

        Cells(oRow, c) = name
        Cells(oRow, c + 1) = email
        Cells(oRow, c + 2) = phoneNumber
        Cells(oRow, c + 3) = generation
        Cells(oRow, c + 4) = status

        oRow = oRow + 1
        r = r + 1
        Sheets("Sheet 1").Activate
        Loop

Next

The problem is when I get to the .Activate part, Excel reads that the subscript is out of range.

What should I do?

Samuel Norton
  • 11
  • 1
  • 5
    [Read this ASAP](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) – BruceWayne Jun 16 '16 at 21:16
  • Assuming you used the original excel sheet names, this should be Sheets("Sheet1").Activate not Sheets("Sheet 1").Activate. – VBA Pete Jun 16 '16 at 21:46

1 Answers1

1

It looks like you're both looping through r using for, and incrementing r in your for. I also don't see c anywhere until it's used.

In addition, instead of using Active/Select as BruceWayne already pointed out, explicitly define your sheets and ranges.

Dim ws1  As Worksheet
Set ws1 = ThisWorkbook.Sheets("Sheet 1")

Same for ws2 and ThisWorkbook.Sheets("Compiled Data").

Finally, instead of setting the variables, why not just put the data straight in?

Dim leftCol as Integer
leftCol = ws1.Cells.Find("*", SearchOrder:=xlByColumns, searchdirection:=xlNext).Column
ws2.Cells(oRow, c).value = ws1.Cells(r, leftCol).value
Malil
  • 123
  • 8