Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying an entire column
I am copying info from a group of cells from one workbook to anothe workbook as shown below, but I have a lot of cells to copy. Is i possible to shorten this up by copying entire columns rather than on cell at a time or possibly even an entire group of columns and rows? Dim BNDL1 As String Dim BNDL2 As String Dim BNDL3 As String BNDL1 = Range("A4").Value BNDL2 = Range("A5").Value BNDL3 = Range("A6").Value Windows("VERCO.xls").Activate With Worksheets("BOM") .Cells(19, 1).Value = BNDL1 .Cells(20, 1).Value = BNDL2 .Cells(21, 1).Value = BNDL3 End Wit -- dl ----------------------------------------------------------------------- dlb's Profile: http://www.excelforum.com/member.php...fo&userid=1648 View this thread: http://www.excelforum.com/showthread.php?threadid=52286 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying an entire column
would something like this help? this may be one way. i assumed both sheets were
in the same workbook and the source sheet was called sheet1. Option Explicit Sub test() Dim lastRow As Long Dim i As Long, z As Long Dim rng As Range, rng2 As Range i = 19 z = 4 lastRow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row With Worksheets("BOM") For i = 19 To lastRow Set rng = .Cells(i, 1) Set rng2 = Sheets("Sheet1").Cells(z, 1) rng.Value = rng2 z = z + 1 Next End With End Sub -- Gary "dlb" wrote in message ... I am copying info from a group of cells from one workbook to another workbook as shown below, but I have a lot of cells to copy. Is it possible to shorten this up by copying entire columns rather than one cell at a time or possibly even an entire group of columns and rows? Dim BNDL1 As String Dim BNDL2 As String Dim BNDL3 As String BNDL1 = Range("A4").Value BNDL2 = Range("A5").Value BNDL3 = Range("A6").Value Windows("VERCO.xls").Activate With Worksheets("BOM") Cells(19, 1).Value = BNDL1 Cells(20, 1).Value = BNDL2 Cells(21, 1).Value = BNDL3 End With -- dlb ------------------------------------------------------------------------ dlb's Profile: http://www.excelforum.com/member.php...o&userid=16483 View this thread: http://www.excelforum.com/showthread...hreadid=522860 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying an entire column
Thanks for the reply I have some questions, my programming ability is VERY limited, I just learn from asking questions on this forum, so I appologize. The program is running, but no info is being copied. Here's the workbooks/sheets/cells im working with: Copying from a worksheet named "sheet1" in a workbook named "TESTBK" and pasting to a worksheet named "sheet2" in workbook "Verco". Both of these sheets are new worksheets, and im wanting to copy the first 5 rows from columns A and B, and pasting them into the same location on the Verco worksheet. This is what I have for the Macro, I think the problem is in the sheet and workbook changes, might have called out wrong, or in the wrong location (also not sure what the i and z are used for either): Option Explicit Sub COPYTST1() Dim lastRow As Long Dim i As Long, z As Long Dim rng As Range, rng2 As Range i = 1 z = 19 lastRow = Worksheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row Windows("VERCO.xls").Activate With Worksheets("sheet2") For i = 19 To lastRow Set rng = .Cells(i, 1) Set rng2 = Sheets("SHEET2").Cells(z, 1) rng.Value = rng2 z = z + 1 Next End With End Sub End Sub -- dlb ------------------------------------------------------------------------ dlb's Profile: http://www.excelforum.com/member.php...o&userid=16483 View this thread: http://www.excelforum.com/showthread...hreadid=522860 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying an entire column
if both workbooks are open, this will copy a1:b5 from testbk.xls and paste it
into sheet 2 of verco.xls Sub COPYTST1() With Workbooks("Testbk.xls").Sheets("Sheet1") ..Range("a1:B5").Copy Workbooks("verco.xls").Sheets("Sheet2").Paste End With End Sub -- Gary "dlb" wrote in message ... Thanks for the reply I have some questions, my programming ability is VERY limited, I just learn from asking questions on this forum, so I appologize. The program is running, but no info is being copied. Here's the workbooks/sheets/cells im working with: Copying from a worksheet named "sheet1" in a workbook named "TESTBK" and pasting to a worksheet named "sheet2" in workbook "Verco". Both of these sheets are new worksheets, and im wanting to copy the first 5 rows from columns A and B, and pasting them into the same location on the Verco worksheet. This is what I have for the Macro, I think the problem is in the sheet and workbook changes, might have called out wrong, or in the wrong location (also not sure what the i and z are used for either): Option Explicit Sub COPYTST1() Dim lastRow As Long Dim i As Long, z As Long Dim rng As Range, rng2 As Range i = 1 z = 19 lastRow = Worksheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row Windows("VERCO.xls").Activate With Worksheets("sheet2") For i = 19 To lastRow Set rng = .Cells(i, 1) Set rng2 = Sheets("SHEET2").Cells(z, 1) rng.Value = rng2 z = z + 1 Next End With End Sub End Sub -- dlb ------------------------------------------------------------------------ dlb's Profile: http://www.excelforum.com/member.php...o&userid=16483 View this thread: http://www.excelforum.com/showthread...hreadid=522860 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying an entire column
Thanks Gary, that should wor -- dl ----------------------------------------------------------------------- dlb's Profile: http://www.excelforum.com/member.php...fo&userid=1648 View this thread: http://www.excelforum.com/showthread.php?threadid=52286 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying conditional formatting to an entire column in Excel 2007 | Excel Worksheet Functions | |||
Copying entire row | Excel Programming | |||
Copying an entire row or Rows based on column criteria | Excel Programming | |||
copying conditional formatting to an entire column | Excel Discussion (Misc queries) | |||
copying entire column | Excel Programming |