Hi, So I have the below code which should read each row on the active worksheet and depending on the value of column H copy to the relevant worksheet in the workbook. Although when I run, y = ThisWorkbook.Sheets(area).UsedRange.Rows.Count + 1 does not update. Value is always 1 Dim area As StringSet MS = ActiveWorkbook For x = 5 To ActiveSheet.UsedRange.Rows.Count ' Lookup Area Code for each row area = Range("H" & x).Value y = ThisWorkbook.Sheets(area).UsedRange.Rows.Count + 1 ThisWorkbook.Sheets(area).Range("A" & y).Value = ThisWorkbook.Sheets("input").Range("A" & x).Value next x

If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Count on another worksheet not working

Thread starter cleary1981
Start date Feb 19, 2016
Tags

referencing worksheet

cleary1981

New Member

Feb 19, 2016

Hi,

So I have the below code which should read each row on the active worksheet and depending on the value of column H copy to the relevant worksheet in the workbook. Although when I run, y = ThisWorkbook.Sheets(area).UsedRange.Rows.Count + 1 does not update. Value is always 1

Code:

Dim area As StringSet MS = ActiveWorkbook


For x = 5 To ActiveSheet.UsedRange.Rows.Count
' Lookup Area Code for each row
area = Range("H" & x).Value
y = ThisWorkbook.Sheets(area).UsedRange.Rows.Count + 1
ThisWorkbook.Sheets(area).Range("A" & y).Value = ThisWorkbook.Sheets("input").Range("A" & x).Value
next x