I have an input box that asks the user to select a cell, storing this as a range.
Then, this range will be converted to an address (string) so the !worksheet isn’t also saved.
A For loop will cycle through the worksheets, however, I need to reference the start cell (range) to run a macro – the problem is, I cannot reference the start cell any longer, as this is now String, not a range.
I need to re-convert the address (string) back to a range, within the For loop.
Set myCell = Application.InputBox( _ prompt:="Select a cell", Type:=8) celladdress = myCell.Address() NoSheets = Sheets.Count For x = 2 To NoSheets 'covert address back to range 'Identify blend data NumRows = Range(myCell, myCell.Offset(1, 0).End(xlDown)).Rows.Count NumColumns = Range(myCell, myCell.Offset(0, 1).End(xlToRight)).Columns.Count Set StartRange = Range(myCell, myCell.Offset(NumRows - 1, NumColumns - 1)) StartRange.Copy
You want a
Worksheet object reference inside that loop:
For x = 2 To NoSheets Dim currentSheet As Worksheet Set currentSheet = thatWorkbook.Worksheets(x) ... Next
Once you have the sheet, you can still use
myCell to get a
Range on the
currentSheet, without dealing with cell address strings:
Set currentCell = currentSheet.Cells(myCell.Row, myCell.Column)
Avoid unqualified calls to
Cells; they’re implicitly referring to the
ActiveSheet, which typically means code that only works with
Worksheet.Activate calls sprinkled through (you generally want to avoid having to use
Answered By – Mathieu Guindon
Answer Checked By – Jay B. (AngularFixing Admin)