Hi, thanks for answering. I'm not sure I'm making anything very clear. It's
what I'm trying to copy that I'm trying to find the range of. There's an
excel spreadsheet. It has a header row. I want everything else but the header
row. It's pasting just fine to where I'm pasting it to, it's just including
the header row, which is what I don't want to do.

Nothing in what you wrote worked, and it's kind of confusing. So was the
answer from the guy in the UK. I'm not sure what he meant by
"code:.................."

Of course, I really know nothing about writing macros, so, it's not
surprising that I don't understand what everyone is writing. I only know I
pasted what you wrote into a spreadsheet and I went straight into debug mode.

It's been that kind of day, ya know? I think it's because Mercury went
retrograde.

"joel" wrote:

xldown and xlup work the same way on a worksheet using SHift-CNTL Up or down
arrow. I f you just put an item in A1 then use Shift-Cntl- Down arrow you
will go to the end of the worksheet. then adding 1 to the last item will
cause an error becuase you have gone below the end of the worksheet.

You can use the following
LastRow = Range("A" & Rows.Count).End(Xlup).Row
'where rows.count is the last row of
'the worksheet and move up

'if last row = 1 you have to test if the cell is empty or yo really have
data in row 1 to determine the next row.

I usually use this combination
if Range("A1") = "" then
NewRow = 1
else
LastRow = Range("A" & Rows.Count).End(Xlup).Row
Newrow = LastRow + 1
end if

You are getting an error if you have either no data or you have 1 row of data.

try this

Workbooks("Copy from.xlsm").Worksheets("Sheet1").CurrentRegion.Cop y
with Sheets("Sheet1")
if .Range("A1") = "" then
NewRow = 1
else
LastRow = .Range("A" & Rows.Count).End(Xlup).Row
Newrow = LastRow + 1
end if
.Range("A" & NewRow).Paste
end with


"FSt1" wrote:

hi
yes. you will run into the overflow problem each time you exceed the
dimentions that you have set. you can't put a quart of water in a pint jar so
the only solution is to get a bigger jar.
in your case, you are exceeding what an interger will hold so time to get a
bigger container. a long if decimals are not needed, a double if decimals are
needed.

in vb help, type data type summary for more info.

regards
FSt1


"jknapp1005" wrote:

I'm new to writing macros or using VBA. This question might seem obvious to
programmers, but I'm trying to find a way to copy a range where I don't know
exactly how big it's going to be, but I don't want to copy the header row.
CurrentRegion seems to copy everything, and I don't want that. Here is the
way it is now:

Workbooks("Copy from.xlsm").Worksheets("Sheet1").CurrentRegion.Cop y
Sheets("Sheet1").Rows.End(xlDown).Offset(1).Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Application.CutCopyMode = False

How can I write it to make it so it will start on (for instance) A2, find
the rest of the range automatically and copy and paste that? I've tried it a
lot of different ways, but keep coming back with only the cell A2.