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.

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.

CurrentRegion needs to be used with a cell, like

Code:
--------------------

Sheet1.Cells(2, 1).CurrentRegion.Copy

--------------------


An alternative would be UsedRange, which refers to all the data stored
on a sheet

Code:
--------------------

Sheet1.UsedRange.Copy
--------------------


Tip: Highlight any words in the code, e.g CurrentRegion, in the VB
Editor & press F1 to get help relevant to the selected term.


--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=95429

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.

hi
disregard this answer. i posted to the wrong post.
sorry.

regards
FSt1

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

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.

try this

With Workbooks("Copy from.xlsm").Worksheets("Sheet1")
'get last cell on worksheet
Set LastCell = .Cells.SpecialCells(xlCellTypeLastCell)
'set range from A2 to Last cell
Set CopyRange = .Range(.Range("A2"), LastCell)
'copy range skipping first row
CopyRange.Copy
End With


With Sheets("Sheet1")
'If no data in A1 then past data starting in row 1
If .Range("A1") = "" Then
NewRow = 1
Else
'get last row of data in column A
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'set new row the row after lat data
NewRow = LastRow + 1
End If
'Paste data in new row
.Range("A" & NewRow).Paste
End With


"jknapp1005" wrote:

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.

Try this

Code:
--------------------

Dim rCopy As Range
Dim NextRw As Long

With ThisWorkbook.Sheet1
NextRw = .Cells(.Rows.Count, 1).End(xlUp).Row
If NextRw 1 Then NextRw = NextRw + 1
End With

With Workbooks("Copy from.xlsm").Worksheets("Sheet1")
Set rCopy = .Cells(2, 1).CurrentRegion
rCopy.Offset(1, 0).Resize(rCopy.Rows.Count - 1, _
rCopy.Columns.Count).Copy ThisWorkbook.Sheet1.Cells(NextRw, 1)
End With
Set rCopy = Nothing
--------------------


--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=95429

Yes, that did work, except for the last line. I kept getting an error message
"object does not support method". I went to the help section and replaced the
last line with "ActiveSheet.Paste
Destination:=Worksheets("Sheet1").Range("A1" & NewRow").Paste"
It seemed like what you wrote should have worked, but when I put the line in
this form, it worked. Thanks so much!

"joel" wrote:

try this

With Workbooks("Copy from.xlsm").Worksheets("Sheet1")
'get last cell on worksheet
Set LastCell = .Cells.SpecialCells(xlCellTypeLastCell)
'set range from A2 to Last cell
Set CopyRange = .Range(.Range("A2"), LastCell)
'copy range skipping first row
CopyRange.Copy
End With


With Sheets("Sheet1")
'If no data in A1 then past data starting in row 1
If .Range("A1") = "" Then
NewRow = 1
Else
'get last row of data in column A
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'set new row the row after lat data
NewRow = LastRow + 1
End If
'Paste data in new row
.Range("A" & NewRow).Paste
End With


"jknapp1005" wrote:

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.