copying an entire column

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

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

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

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

Thanks Gary, that should work