Data moving 3

[climate]

Hi
I need to a code, when run it on any sheet, copy of column X paste to a file
(R.xls) in sheet1 and locate on column H respectively. in otherwords, first
run of expected code, paste column X to column H of R.xls and second
run,paste
column X to column I of R.xls and respectively.

best regards

 

[climate]

S.O.S

 

[Dave Peterson]

This assumes that R.xls is already open and that row 1 is always used in column
X of the activesheet:

Option Explicit
Sub testme()
Dim RngToCopy As Range
Dim DestCell As Range
Dim ActWks As Worksheet

Set ActWks = ActiveSheet

With ActWks
Set RngToCopy = .Range("x1").EntireColumn
End With

'r.xls has to be open and have a sheet named Sheet1
With Workbooks("r.xls").Worksheets("Sheet1")
Set DestCell = .Cells(1, .Columns.Count).End(xlToLeft)
If DestCell.Column < .Range("H1").Column Then
Set DestCell = .Range("H1")
End If
End With

RngToCopy.Copy _
Destination:=DestCell

End Sub

(untested, but it did compile.)

 

[climate]

Hi Dave
Your code work's, but, when run it on sheet2 or worksheets of another file,
new copy of column X replace to prior.
I want to set copy of column X in successive (back to back).
For example: i run your code on 10 worksheet, as a result, i will have 10
column X in r.xls ( column H to Q).

Best regards

 

[Dave Peterson]

You're right. I was just overwriting the same column.

It was a minor(!) typo.

Change:
Set DestCell = .Cells(1, .Columns.Count).End(xlToLeft)
to
Set DestCell = .Cells(1, .Columns.Count).End(xlToLeft).offset(0,1)

(that offset moves one column to the right)

And remember row 1 has to have data in it to find that next column.

 

[climate]

Dear Dave
Thank you very much.
My problem solved, your code is very nice.

Sincerly yours