VBA help to copy variable range
 

I know that once I saw the answer but I was not able to find it again
I have an spreadsheet with macros that help me to create an EDI format file.
Just what I need is to be able to select and copy a variable range from one
workbook to another workbook.
The range vary depending on my fill down formula.
Information is in cell J10 to O??
Thank you

VBA help to copy variable range
 

Pick a column between J and O (inclusive) that will have the most information
displayed down the sheet. For this example we'll assume it's column M, plus
this keeps us with a method that works with any of the columns, not just J or
O

Dim lastRow as Long
dim rngAddress as String
dim rngToCopy as Range
lastRow = Range("M" & Rows.Count).End(xlUp).Row
rngAddress = "J10:O" & lastRow


that gives you address of the range and you can use that address to set a
range as:
Set rngToCopy = ActiveWorkbook.Worksheets("SourceSheet").Range(rng Address)

then go from there for your copy/paste into the other workbook and
worksheet. A generic way (change workbook/sheet names as needed) - this does
the same as copy, assuming second workbook is open:
(this would be all one line)

Workbooks("OtherBook.xls").Worksheets("CopySheet") .Range(rngAddress).Value =
rngToCopy.Value

That would copy it into J10:O## in the other book.

Hope this helps some.

"Eduardo" wrote:

I know that once I saw the answer but I was not able to find it again
I have an spreadsheet with macros that help me to create an EDI format file.
Just what I need is to be able to select and copy a variable range from one
workbook to another workbook.
The range vary depending on my fill down formula.
Information is in cell J10 to O??
Thank you

VBA help to copy variable range
 

Thank you for your answer I'm new on this just a question how I get the range
copy in the other worksheet starting in cell C17

"JLatham" wrote:

Pick a column between J and O (inclusive) that will have the most information
displayed down the sheet. For this example we'll assume it's column M, plus
this keeps us with a method that works with any of the columns, not just J or
O

Dim lastRow as Long
dim rngAddress as String
dim rngToCopy as Range
lastRow = Range("M" & Rows.Count).End(xlUp).Row
rngAddress = "J10:O" & lastRow


that gives you address of the range and you can use that address to set a
range as:
Set rngToCopy = ActiveWorkbook.Worksheets("SourceSheet").Range(rng Address)

then go from there for your copy/paste into the other workbook and
worksheet. A generic way (change workbook/sheet names as needed) - this does
the same as copy, assuming second workbook is open:
(this would be all one line)

Workbooks("OtherBook.xls").Worksheets("CopySheet") .Range(rngAddress).Value =
rngToCopy.Value

That would copy it into J10:O## in the other book.

Hope this helps some.

"Eduardo" wrote:

I know that once I saw the answer but I was not able to find it again
I have an spreadsheet with macros that help me to create an EDI format file.
Just what I need is to be able to select and copy a variable range from one
workbook to another workbook.
The range vary depending on my fill down formula.
Information is in cell J10 to O??
Thank you

VBA help to copy variable range
 

Since we've started down the path using a Range, we'll continue. Need
another definitition near the top
Dim rngToPaste As Range

just ahead of the line beginning Workbooks("OtherBook.xls")....
place this code:

rngAddress="C17:H" & lastrow+7
'remember next is one line of code
set rngToCopy =
Workbooks("OtherBook.xls").Worksheets("CopySheet") .Range(rngAddress)
'
'now change that line that starts as Workbooks("OtherBook.xls").... to simply:
rngToPaste.Value = rngToCopy.Value

A more condensed version of it all:
Sub CopyBetweenBooks()
Dim lastRow as Long
Dim rngAddress As String
Dim rngToCopy As Range
Dim rngToPaste As Range
lastRow = Range("M" & Rows.Count).End(xlUp).Row
rngAddress = "J10:O" & lastRow

'remember next is one line of code
Set rngToCopy = ActiveWorkbook.Worksheets("SourceSheet").Range(rng Address)

rngAddress="C17:H" & lastrow+7
'remember next is one line of code
set rngToPaste =
Workbooks("OtherBook.xls").Worksheets("CopySheet") .Range(rngAddress)

rngToPaste.Value = rngToCopy.Value
End Sub



"Eduardo" wrote:

Thank you for your answer I'm new on this just a question how I get the range
copy in the other worksheet starting in cell C17

"JLatham" wrote:

Pick a column between J and O (inclusive) that will have the most information
displayed down the sheet. For this example we'll assume it's column M, plus
this keeps us with a method that works with any of the columns, not just J or
O

Dim lastRow as Long
dim rngAddress as String
dim rngToCopy as Range
lastRow = Range("M" & Rows.Count).End(xlUp).Row
rngAddress = "J10:O" & lastRow


that gives you address of the range and you can use that address to set a
range as:
Set rngToCopy = ActiveWorkbook.Worksheets("SourceSheet").Range(rng Address)

then go from there for your copy/paste into the other workbook and
worksheet. A generic way (change workbook/sheet names as needed) - this does
the same as copy, assuming second workbook is open:
(this would be all one line)

Workbooks("OtherBook.xls").Worksheets("CopySheet") .Range(rngAddress).Value =
rngToCopy.Value

That would copy it into J10:O## in the other book.

Hope this helps some.

"Eduardo" wrote:

I know that once I saw the answer but I was not able to find it again
I have an spreadsheet with macros that help me to create an EDI format file.
Just what I need is to be able to select and copy a variable range from one
workbook to another workbook.
The range vary depending on my fill down formula.
Information is in cell J10 to O??
Thank you

VBA help to copy variable range
 

Thank you very much

"JLatham" wrote:

Since we've started down the path using a Range, we'll continue. Need
another definitition near the top
Dim rngToPaste As Range

just ahead of the line beginning Workbooks("OtherBook.xls")....
place this code:

rngAddress="C17:H" & lastrow+7
'remember next is one line of code
set rngToCopy =
Workbooks("OtherBook.xls").Worksheets("CopySheet") .Range(rngAddress)
'
'now change that line that starts as Workbooks("OtherBook.xls").... to simply:
rngToPaste.Value = rngToCopy.Value

A more condensed version of it all:
Sub CopyBetweenBooks()
Dim lastRow as Long
Dim rngAddress As String
Dim rngToCopy As Range
Dim rngToPaste As Range
lastRow = Range("M" & Rows.Count).End(xlUp).Row
rngAddress = "J10:O" & lastRow

'remember next is one line of code
Set rngToCopy = ActiveWorkbook.Worksheets("SourceSheet").Range(rng Address)

rngAddress="C17:H" & lastrow+7
'remember next is one line of code
set rngToPaste =
Workbooks("OtherBook.xls").Worksheets("CopySheet") .Range(rngAddress)

rngToPaste.Value = rngToCopy.Value
End Sub



"Eduardo" wrote:

Thank you for your answer I'm new on this just a question how I get the range
copy in the other worksheet starting in cell C17

"JLatham" wrote:

Pick a column between J and O (inclusive) that will have the most information
displayed down the sheet. For this example we'll assume it's column M, plus
this keeps us with a method that works with any of the columns, not just J or
O

Dim lastRow as Long
dim rngAddress as String
dim rngToCopy as Range
lastRow = Range("M" & Rows.Count).End(xlUp).Row
rngAddress = "J10:O" & lastRow


that gives you address of the range and you can use that address to set a
range as:
Set rngToCopy = ActiveWorkbook.Worksheets("SourceSheet").Range(rng Address)

then go from there for your copy/paste into the other workbook and
worksheet. A generic way (change workbook/sheet names as needed) - this does
the same as copy, assuming second workbook is open:
(this would be all one line)

Workbooks("OtherBook.xls").Worksheets("CopySheet") .Range(rngAddress).Value =
rngToCopy.Value

That would copy it into J10:O## in the other book.

Hope this helps some.

"Eduardo" wrote:

I know that once I saw the answer but I was not able to find it again
I have an spreadsheet with macros that help me to create an EDI format file.
Just what I need is to be able to select and copy a variable range from one
workbook to another workbook.
The range vary depending on my fill down formula.
Information is in cell J10 to O??
Thank you

VBA help to copy variable range
 

This is almost what I was looking for. Can you help me?
I have one sheet that gets updated daily from an external source and need to
copy only the new rows into a second sheet in the same workbook, but only the
data in columns A through F, the other columns in those rows I do no need to
copy.
I can't figure out how to designate the range of the cells to copy. I tried
the following command but I get a compile error:

Sheets("sheet1").Range("A" & Newrow : "F" & Maxrowtxt).Copy
Destination:=.Range("A" & Newrow)

Help is greatly appreciated.

"JLatham" wrote:

Since we've started down the path using a Range, we'll continue. Need
another definitition near the top
Dim rngToPaste As Range

just ahead of the line beginning Workbooks("OtherBook.xls")....
place this code:

rngAddress="C17:H" & lastrow+7
'remember next is one line of code
set rngToCopy =
Workbooks("OtherBook.xls").Worksheets("CopySheet") .Range(rngAddress)
'
'now change that line that starts as Workbooks("OtherBook.xls").... to simply:
rngToPaste.Value = rngToCopy.Value

A more condensed version of it all:
Sub CopyBetweenBooks()
Dim lastRow as Long
Dim rngAddress As String
Dim rngToCopy As Range
Dim rngToPaste As Range
lastRow = Range("M" & Rows.Count).End(xlUp).Row
rngAddress = "J10:O" & lastRow

'remember next is one line of code
Set rngToCopy = ActiveWorkbook.Worksheets("SourceSheet").Range(rng Address)

rngAddress="C17:H" & lastrow+7
'remember next is one line of code
set rngToPaste =
Workbooks("OtherBook.xls").Worksheets("CopySheet") .Range(rngAddress)

rngToPaste.Value = rngToCopy.Value
End Sub



"Eduardo" wrote:

Thank you for your answer I'm new on this just a question how I get the range
copy in the other worksheet starting in cell C17

"JLatham" wrote:

Pick a column between J and O (inclusive) that will have the most information
displayed down the sheet. For this example we'll assume it's column M, plus
this keeps us with a method that works with any of the columns, not just J or
O

Dim lastRow as Long
dim rngAddress as String
dim rngToCopy as Range
lastRow = Range("M" & Rows.Count).End(xlUp).Row
rngAddress = "J10:O" & lastRow


that gives you address of the range and you can use that address to set a
range as:
Set rngToCopy = ActiveWorkbook.Worksheets("SourceSheet").Range(rng Address)

then go from there for your copy/paste into the other workbook and
worksheet. A generic way (change workbook/sheet names as needed) - this does
the same as copy, assuming second workbook is open:
(this would be all one line)

Workbooks("OtherBook.xls").Worksheets("CopySheet") .Range(rngAddress).Value =
rngToCopy.Value

That would copy it into J10:O## in the other book.

Hope this helps some.

"Eduardo" wrote:

I know that once I saw the answer but I was not able to find it again
I have an spreadsheet with macros that help me to create an EDI format file.
Just what I need is to be able to select and copy a variable range from one
workbook to another workbook.
The range vary depending on my fill down formula.
Information is in cell J10 to O??
Thank you

VBA help to copy variable range
 

Hi

You need another & and put the colon within the quotes before the F

Sheets("sheet1").Range("A" & Newrow & ":F" & Maxrowtxt).Copy

--
Regards
Roger Govier

"BRB" wrote in message
...
This is almost what I was looking for. Can you help me?
I have one sheet that gets updated daily from an external source and need
to
copy only the new rows into a second sheet in the same workbook, but only
the
data in columns A through F, the other columns in those rows I do no need
to
copy.
I can't figure out how to designate the range of the cells to copy. I
tried
the following command but I get a compile error:

Sheets("sheet1").Range("A" & Newrow : "F" & Maxrowtxt).Copy
Destination:=.Range("A" & Newrow)

Help is greatly appreciated.

"JLatham" wrote:

Since we've started down the path using a Range, we'll continue. Need
another definitition near the top
Dim rngToPaste As Range

just ahead of the line beginning Workbooks("OtherBook.xls")....
place this code:

rngAddress="C17:H" & lastrow+7
'remember next is one line of code
set rngToCopy =
Workbooks("OtherBook.xls").Worksheets("CopySheet") .Range(rngAddress)
'
'now change that line that starts as Workbooks("OtherBook.xls").... to
simply:
rngToPaste.Value = rngToCopy.Value

A more condensed version of it all:
Sub CopyBetweenBooks()
Dim lastRow as Long
Dim rngAddress As String
Dim rngToCopy As Range
Dim rngToPaste As Range
lastRow = Range("M" & Rows.Count).End(xlUp).Row
rngAddress = "J10:O" & lastRow

'remember next is one line of code
Set rngToCopy =
ActiveWorkbook.Worksheets("SourceSheet").Range(rng Address)

rngAddress="C17:H" & lastrow+7
'remember next is one line of code
set rngToPaste =
Workbooks("OtherBook.xls").Worksheets("CopySheet") .Range(rngAddress)

rngToPaste.Value = rngToCopy.Value
End Sub



"Eduardo" wrote:

Thank you for your answer I'm new on this just a question how I get the
range
copy in the other worksheet starting in cell C17

"JLatham" wrote:

Pick a column between J and O (inclusive) that will have the most
information
displayed down the sheet. For this example we'll assume it's column
M, plus
this keeps us with a method that works with any of the columns, not
just J or
O

Dim lastRow as Long
dim rngAddress as String
dim rngToCopy as Range
lastRow = Range("M" & Rows.Count).End(xlUp).Row
rngAddress = "J10:O" & lastRow


that gives you address of the range and you can use that address to
set a
range as:
Set rngToCopy =
ActiveWorkbook.Worksheets("SourceSheet").Range(rng Address)

then go from there for your copy/paste into the other workbook and
worksheet. A generic way (change workbook/sheet names as needed) -
this does
the same as copy, assuming second workbook is open:
(this would be all one line)

Workbooks("OtherBook.xls").Worksheets("CopySheet") .Range(rngAddress).Value
=
rngToCopy.Value

That would copy it into J10:O## in the other book.

Hope this helps some.

"Eduardo" wrote:

I know that once I saw the answer but I was not able to find it
again
I have an spreadsheet with macros that help me to create an EDI
format file.
Just what I need is to be able to select and copy a variable range
from one
workbook to another workbook.
The range vary depending on my fill down formula.
Information is in cell J10 to O??
Thank you

VBA help to copy variable range
 

It works. Thanks,

"Roger Govier" wrote:

Hi

You need another & and put the colon within the quotes before the F

Sheets("sheet1").Range("A" & Newrow & ":F" & Maxrowtxt).Copy

--
Regards
Roger Govier

"BRB" wrote in message
...
This is almost what I was looking for. Can you help me?
I have one sheet that gets updated daily from an external source and need
to
copy only the new rows into a second sheet in the same workbook, but only
the
data in columns A through F, the other columns in those rows I do no need
to
copy.
I can't figure out how to designate the range of the cells to copy. I
tried
the following command but I get a compile error:

Sheets("sheet1").Range("A" & Newrow : "F" & Maxrowtxt).Copy
Destination:=.Range("A" & Newrow)

Help is greatly appreciated.

"JLatham" wrote:

Since we've started down the path using a Range, we'll continue. Need
another definitition near the top
Dim rngToPaste As Range

just ahead of the line beginning Workbooks("OtherBook.xls")....
place this code:

rngAddress="C17:H" & lastrow+7
'remember next is one line of code
set rngToCopy =
Workbooks("OtherBook.xls").Worksheets("CopySheet") .Range(rngAddress)
'
'now change that line that starts as Workbooks("OtherBook.xls").... to
simply:
rngToPaste.Value = rngToCopy.Value

A more condensed version of it all:
Sub CopyBetweenBooks()
Dim lastRow as Long
Dim rngAddress As String
Dim rngToCopy As Range
Dim rngToPaste As Range
lastRow = Range("M" & Rows.Count).End(xlUp).Row
rngAddress = "J10:O" & lastRow

'remember next is one line of code
Set rngToCopy =
ActiveWorkbook.Worksheets("SourceSheet").Range(rng Address)

rngAddress="C17:H" & lastrow+7
'remember next is one line of code
set rngToPaste =
Workbooks("OtherBook.xls").Worksheets("CopySheet") .Range(rngAddress)

rngToPaste.Value = rngToCopy.Value
End Sub



"Eduardo" wrote:

Thank you for your answer I'm new on this just a question how I get the
range
copy in the other worksheet starting in cell C17

"JLatham" wrote:

Pick a column between J and O (inclusive) that will have the most
information
displayed down the sheet. For this example we'll assume it's column
M, plus
this keeps us with a method that works with any of the columns, not
just J or
O

Dim lastRow as Long
dim rngAddress as String
dim rngToCopy as Range
lastRow = Range("M" & Rows.Count).End(xlUp).Row
rngAddress = "J10:O" & lastRow


that gives you address of the range and you can use that address to
set a
range as:
Set rngToCopy =
ActiveWorkbook.Worksheets("SourceSheet").Range(rng Address)

then go from there for your copy/paste into the other workbook and
worksheet. A generic way (change workbook/sheet names as needed) -
this does
the same as copy, assuming second workbook is open:
(this would be all one line)

Workbooks("OtherBook.xls").Worksheets("CopySheet") .Range(rngAddress).Value
=
rngToCopy.Value

That would copy it into J10:O## in the other book.

Hope this helps some.

"Eduardo" wrote:

I know that once I saw the answer but I was not able to find it
again
I have an spreadsheet with macros that help me to create an EDI
format file.
Just what I need is to be able to select and copy a variable range
from one
workbook to another workbook.
The range vary depending on my fill down formula.
Information is in cell J10 to O??
Thank you