First, Cells defaults to the ActiveSheet, so

Set rng = ws1.Range("A1:F1000" & Cells(Rows.Count, "A").End(xlUp).Row)

is equivalent to

Set rng = ws1.Range("A1:F1000" & ActiveSheet.Cells(Rows.Count,
"A").End(xlUp).Row)

Second, even if ws1 were the active sheet, and if the last used cell in
column A were, say A100,

ws1.Range("A1:F1000" & Cells(Rows.Count, "A").End(xlUp).Row)

would try to set the range to

ws1.Range("A1:F1000100")

which would give you an out of range.

I suspect you're trying for something like:

Set rng = ws1.Range("A1:F" & ws1.Cells(Rows.Count,
"A").End(xlUp).Row)

or perhaps

With ws1
Set rng =.Range("A1:F" & .Cells(Rows.Count, "A").End(xlUp).Row)
End With


In article ,
"David" wrote:

When I execute I get the 1004 error message and when I click on Debug I get
a yellow highlight on:

Set rng = ws1.Range("A1:F1000" & Cells(Rows.Count, "A").End(xlUp).Row)

Can someone help me find why I am getting the error and how I may correct
it. I did google searches and 1004 has a few reasons like a different
workbook or overwriting which did not seem the issue.