Prev: Subtotals for a variable number of rows
Next: Median in Excel 2007 pivot-tables
From: climate on 9 Apr 2010 01:57
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
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Subtotals for a variable number of rows
Next: Median in Excel 2007 pivot-tables