I have 2 spreadheets PickX.xls and Picklist.xls
Picklist.xls is open and needs to receive data for column A only from
PickX.xls.
How do I write the code to get all of column A of PickX.xls into column A of
Picklist . I want all data whether it is numeric,blank or consisting of
characters.
I need this code to go just before the for loop that is set up in the
Picklist.xls.

Private Sub cmdRemoveEndOfData_Click()
Dim Rng As Range
Dim Rng2 As Range
Set Rng = Range("A1", Range("A65536").End(xlUp))
Set Rng2 = Range("K1")
For Each c In Rng
If Not IsNumeric(c) Then
c.Select
Selection.Cut
Rng2 = Range("K1").Select
ActiveSheet.Paste

End If
Next c
MsgBox "Mainframe character has been removed"
Range("K1").ClearContents
ThisWorkbook.Save
Call OpenTemplate
ThisWorkbook.Close

Cyndy,

The following line should do it

Workbooks("PickX.xls").Worksheets("Sheet1").Column s("A:A").Copy _
Workbooks("PickList.xls").Worksheets("Sheet1").Cel ls(1, 1)

The code assumes that both PickX.xls and PickList.xls are open

Alok Joshi

"CyndyG" wrote:

I have 2 spreadheets PickX.xls and Picklist.xls
Picklist.xls is open and needs to receive data for column A only from
PickX.xls.
How do I write the code to get all of column A of PickX.xls into column A of
Picklist . I want all data whether it is numeric,blank or consisting of
characters.
I need this code to go just before the for loop that is set up in the
Picklist.xls.

Private Sub cmdRemoveEndOfData_Click()
Dim Rng As Range
Dim Rng2 As Range
Set Rng = Range("A1", Range("A65536").End(xlUp))
Set Rng2 = Range("K1")
For Each c In Rng
If Not IsNumeric(c) Then
c.Select
Selection.Cut
Rng2 = Range("K1").Select
ActiveSheet.Paste

End If
Next c
MsgBox "Mainframe character has been removed"
Range("K1").ClearContents
ThisWorkbook.Save
Call OpenTemplate
ThisWorkbook.Close

Thanks Alok,but tis code is bypassing the for loop. I placed this code just
above the for loop. Picklist.xls is open,pickx.xls is not open yet.

"Alok" wrote:

Cyndy,

The following line should do it

Workbooks("PickX.xls").Worksheets("Sheet1").Column s("A:A").Copy _
Workbooks("PickList.xls").Worksheets("Sheet1").Cel ls(1, 1)

The code assumes that both PickX.xls and PickList.xls are open

Alok Joshi

"CyndyG" wrote:

I have 2 spreadheets PickX.xls and Picklist.xls
Picklist.xls is open and needs to receive data for column A only from
PickX.xls.
How do I write the code to get all of column A of PickX.xls into column A of
Picklist . I want all data whether it is numeric,blank or consisting of
characters.
I need this code to go just before the for loop that is set up in the
Picklist.xls.

Private Sub cmdRemoveEndOfData_Click()
Dim Rng As Range
Dim Rng2 As Range
Set Rng = Range("A1", Range("A65536").End(xlUp))
Set Rng2 = Range("K1")
For Each c In Rng
If Not IsNumeric(c) Then
c.Select
Selection.Cut
Rng2 = Range("K1").Select
ActiveSheet.Paste

End If
Next c
MsgBox "Mainframe character has been removed"
Range("K1").ClearContents
ThisWorkbook.Save
Call OpenTemplate
ThisWorkbook.Close

Cyndy,
Create this sub in a module.

Sub CopyFromPickXToPickList()

Dim wbPX As Workbook

Set wbPX = Workbooks.Open("path and file name goes here")
wbPX.Windows(1).Visible = False
wbPX.Worksheets("Sheet1").Columns("A:A").Copy _
Workbooks("PickList.xls").Worksheets("Sheet1").Cel ls(1, 1)
wbPX.Close SaveChages:=False

End Sub

then call this function from your code.

The function above will open the file PickX.xls and will copy column A to
Picklist.xls and will close the PickX.xls file once it is done. I have not
debugged it but you can surely do that.

Alok

"CyndyG" wrote:

Thanks Alok,but tis code is bypassing the for loop. I placed this code just
above the for loop. Picklist.xls is open,pickx.xls is not open yet.

"Alok" wrote:

Cyndy,

The following line should do it

Workbooks("PickX.xls").Worksheets("Sheet1").Column s("A:A").Copy _
Workbooks("PickList.xls").Worksheets("Sheet1").Cel ls(1, 1)

The code assumes that both PickX.xls and PickList.xls are open

Alok Joshi

"CyndyG" wrote:

I have 2 spreadheets PickX.xls and Picklist.xls
Picklist.xls is open and needs to receive data for column A only from
PickX.xls.
How do I write the code to get all of column A of PickX.xls into column A of
Picklist . I want all data whether it is numeric,blank or consisting of
characters.
I need this code to go just before the for loop that is set up in the
Picklist.xls.

Private Sub cmdRemoveEndOfData_Click()
Dim Rng As Range
Dim Rng2 As Range
Set Rng = Range("A1", Range("A65536").End(xlUp))
Set Rng2 = Range("K1")
For Each c In Rng
If Not IsNumeric(c) Then
c.Select
Selection.Cut
Rng2 = Range("K1").Select
ActiveSheet.Paste

End If
Next c
MsgBox "Mainframe character has been removed"
Range("K1").ClearContents
ThisWorkbook.Save
Call OpenTemplate
ThisWorkbook.Close