must reference sheet1, sheet2, not sheet names

Ken Santopietro · Aug 2nd 2016

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.

Code

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

cytop · Aug 2nd 2016

Re: must reference sheet1, sheet2, not sheet names

What is 'rSheets' - where is it declared, where is it assigned a value?

Infomage · Aug 2nd 2016

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.

Ken Santopietro · Aug 2nd 2016

Re: must reference sheet1, sheet2, not sheet names

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

Code

' Used help of Ozgrid to do this!
      Dim r As Long, rSheets As Range, rData As Range
' blank on purpose
      With Sheets("USD")
          Set rSheets = .Range("d39").CurrentRegion
          Set rData = .Range(.Range("d27").Value, .Range("d28").Value)
          For r = 1 To rSheets.Rows.Count
              rData.Copy
             Sheets(CStr(rSheets(r, 1))).Cells(3, CStr(rSheets(r, 2))).PasteSpecial xlValues
             Sheets(CStr(rSheets(r, 1))).Cells(3, CStr(rSheets(r, 2))).PasteSpecial xlPasteColumnWidths
          Next r
      End With

Display More

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.
Thanks.

must reference sheet1, sheet2, not sheet names

Participate now!

Share