Prev: Subtotals for a variable number of rows
Next: Median in Excel 2007 pivot-tables
From: climate on 7 Apr 2010 06:36
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
From: climate on 8 Apr 2010 02:22
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
From: Dave Peterson on 8 Apr 2010 08:20
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
From: climate on 8 Apr 2010 15:13
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
> .
>
From: Dave Peterson on 8 Apr 2010 15:20
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
 |  Next  |  Last
Pages: 1 2
Prev: Subtotals for a variable number of rows
Next: Median in Excel 2007 pivot-tables