VBA help to copy variable range

[Guest]

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

 

[Guest]

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(rngAddress)

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.

 

[Guest]

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

 

[Guest]

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(rngAddress)

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

 

[Guest]

Thank you very much

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(rngAddress)

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

 

[BRB]

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.

 

[Roger Govier]

Hi

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

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

 

[BRB]

It works. Thanks,

Hi

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

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