Data moving 3
 

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

Data moving 3
 

S.O.S

"climate" wrote:

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

Data moving 3
 

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 wrote:

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

--

Dave Peterson

Data moving 3
 

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" wrote:

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 wrote:

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

--

Dave Peterson
.

Data moving 3
 

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 wrote:

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" wrote:

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 wrote:

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

--

Dave Peterson
.


--

Dave Peterson

Data moving 3
 

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

Sincerly yours

"Dave Peterson" wrote:

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 wrote:

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" wrote:

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 wrote:

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

--

Dave Peterson
.


--

Dave Peterson
.