Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    copy information from one sheet to another (Excel 2000)

    Good Evening All who know,
    I am in need of coping a column from one sheet to another at then end of given time period and do this repeatedly. The piece I need help with is when I copy period 2 then 3 and so on I need to make sure that it does not overwrite the previous columns. In other words, if column A is filled the macro needs to recognize that and fill in column B and so on. I s there a way this can be done?
    Thanks in advance for all the help

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: copy information from one sheet to another (Excel 2000)

    Let's say the cell in row 2 will always be filled. Your macro can determine the first "free" column as follows:

    Dim lngCol As Long
    lngCol = Range("IV2").End(xlToLeft).Column + 1

    Column IV is the last column in all versions of Excel up to and including Excel 2003 (it'll change in the next version).
    Range("IV2")End(xlToLeft) is the last populated cell in row 2.
    By taking the column number of this cell and adding 1, you obtain the column number of the first "free" column. Use this as destination for your copy/paste.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: copy information from one sheet to another (Excel 2000)

    Thanks Hans for the super fast response. Just a bit of a problem as you can see on the sheet. I need to keep the copies going from column to column w/o skipping and I need it to keep the copied material on the same row. Any ideas??
    I appreciate it.

  4. #4
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: copy information from one sheet to another (Excel 2000)

    Hans,

    Here is the file.
    Thanks again

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: copy information from one sheet to another (Excel 2000)

    I'm not quite sure what you want. Does the following macro do it?

    Sub copycolumn()
    Dim lngRow As Long
    Dim lngCol As Long
    lngRow = Sheets("Sheet1").Range("B65536").End(xlUp).Row
    Sheets("Sheet1").Range("B1:B" & lngRow).Copy
    lngCol = Sheets("Sheet2").Cells.Find(What:="*", _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByColumns).Column + 1
    Sheets("Sheet2").Cells(1, lngCol).PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •