Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to copy&paste a variable range rows and colums | Excel Discussion (Misc queries) | |||
Variable Range | Excel Worksheet Functions | |||
Variable in Range | Excel Discussion (Misc queries) | |||
Locating variable range to copy | New Users to Excel | |||
How to use a variable for a range | Excel Worksheet Functions |