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

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

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

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

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