must reference sheet1, sheet2, not sheet names

  • I want to ignore sheet names and instead reference sheets in a loop as sheet1, sheet2
    My sheet names have codes that are good for the user to pick from quickly, but not for my simple coding skills

    my vba doesn't work.

    loop through data sheets copying an area of known data
          For x = 3 To 15
          Sheets("Sheet " & x).Select
              Range(Sheet1.Range(Cells((42 + x - 3), 6).Value, Cells((42 + x - 3), 7).Value)).Copy
                 Sheets(CStr(rSheets(x, 1))).Cells(3, CStr(rSheets(x, 2))).PasteSpecial xlValues
                 Sheets(CStr(rSheets(x, 1))).Cells(3, CStr(rSheets(x, 2))).PasteSpecial xlPasteColumnWidths
          Next x
  • Re: must reference sheet1, sheet2, not sheet names

    Use worksheet.codename property. That is static from the point at which the worksheet is created. Bonus is that your code will still work if someone renames the sheets.

  • Re: must reference sheet1, sheet2, not sheet names

    Thanks -- I was trying to modify this original..

    When it worked it was copying one range to all of the sheets. I needed to copy a variable range from sheet3, sheet4, etc. onto a variable spot on the same sheets. I have the variable ranges on sheet1.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!