I have a macro that sorts data into the correct order, then I manually copy
and paste into a different application. I would like to write code into the
macro that copies the data after sorting, but the length of the data list is
variable, i.e. sometimes it could be spread from A1 to A52, sometimes A1 to
A105. Each time can give a different length, (but always in column A only).
Also, some of the cells are blank, but must be copied as blank.
Thanks.

Hi Black country boy,

To define your variable column A range try something like:

'================
Public Sub Tester001()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range

Set WB = Workbooks("Book1") '<<==== CHANGE
Set SH = WB.Sheets("Sheet11") '<<==== CHANGE

iLastRow = SH.Cells(Rows.Count, "A").End(xlUp).Row

Set rng = SH.Range("A1:A" & iLastRow)

'Your code

End Sub
'<<================


---
Regards,
Norman


"Black country boy" wrote in
message ...
I have a macro that sorts data into the correct order, then I manually copy
and paste into a different application. I would like to write code into
the
macro that copies the data after sorting, but the length of the data list
is
variable, i.e. sometimes it could be spread from A1 to A52, sometimes A1
to
A105. Each time can give a different length, (but always in column A
only).
Also, some of the cells are blank, but must be copied as blank.
Thanks.

Hi Black country boy,

If your workbook has previously been saved, change:

Set WB = Workbooks("Book1") '<<==== CHANGE

to

Set WB = Workbooks("Book1.xls") '<<==== CHANGE



---
Regards,
Norman

On Apr 16, 5:10 pm, Black country boy
wrote:
I have a macro that sorts data into the correct order, then I manually copy
and paste into a different application. I would like to write code into the
macro that copies the data after sorting, but the length of the data list is
variable, i.e. sometimes it could be spread from A1 to A52, sometimes A1 to
A105. Each time can give a different length, (but always in column A only).
Also, some of the cells are blank, but must be copied as blank.
Thanks.

Hi, Black Country Boy:

Would you please give me an example telling me what you need? I am not
sure whether I can solve your problem.

Thanks,

Thanks Norman. I will try this at work tomorrow and let you know how I got on.

"Norman Jones" wrote:

Hi Black country boy,

To define your variable column A range try something like:

'================
Public Sub Tester001()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range

Set WB = Workbooks("Book1") '<<==== CHANGE
Set SH = WB.Sheets("Sheet11") '<<==== CHANGE

iLastRow = SH.Cells(Rows.Count, "A").End(xlUp).Row

Set rng = SH.Range("A1:A" & iLastRow)

'Your code

End Sub
'<<================


---
Regards,
Norman


"Black country boy" wrote in
message ...
I have a macro that sorts data into the correct order, then I manually copy
and paste into a different application. I would like to write code into
the
macro that copies the data after sorting, but the length of the data list
is
variable, i.e. sometimes it could be spread from A1 to A52, sometimes A1
to
A105. Each time can give a different length, (but always in column A
only).
Also, some of the cells are blank, but must be copied as blank.
Thanks.

Norman
I did as you instructed, but the macro would not start. I found the
following in Excel help:
ActiveSheet.UsedRange.Copy
This was sufficent because only column 1 has data in, and it was the whole
of column 1 I needed to copy.
Thanks for your help.
BCB


"Norman Jones" wrote:

Hi Black country boy,

If your workbook has previously been saved, change:

Set WB = Workbooks("Book1") '<<==== CHANGE

to

Set WB = Workbooks("Book1.xls") '<<==== CHANGE



---
Regards,
Norman

George
Thanks for the interest, but I managed to help myself with:
ActiveSheet.UsedRange.Copy
from Excel help.
Thanks
BCB

"George" wrote:

On Apr 16, 5:10 pm, Black country boy
wrote:
I have a macro that sorts data into the correct order, then I manually copy
and paste into a different application. I would like to write code into the
macro that copies the data after sorting, but the length of the data list is
variable, i.e. sometimes it could be spread from A1 to A52, sometimes A1 to
A105. Each time can give a different length, (but always in column A only).
Also, some of the cells are blank, but must be copied as blank.
Thanks.

Hi, Black Country Boy:

Would you please give me an example telling me what you need? I am not
sure whether I can solve your problem.

Thanks,