Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post

    Copy columns from one sheet to another in diff ord

    I have a data sheet with column headings. I have a second sheet with column A containing the preferred order of the columns and only the needed columns. This can be changed to just be column headings if it's easier.

    I want to make a third sheet (or use the second one if we just use column headings) with the columns of data taken from the first sheet in the right order, skipping any columns not on the list and somehow letting me know if a required column didnn't exist on the first data sheet. Make sense? Please help me get started on this. I'm still somewhat new to VB as I do about one little task with it every year or two.

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

    Re: Copy columns from one sheet to another in diff ord

    It would help if you could attach a small sample file.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post

    Re: Copy columns from one sheet to another in diff

    Thanks Hans. Here it is. The first sheet comes in from an external system, headings and all. I'm thinking of having a template with the vba and the map on the third sheet.... there's more data from a second sheet that I'm goiing to combine with the first. You'll note that there are a few columns in the first sheet that aren't on the map. Those are examples of what I don't want on the final data sheet. Also the order of the map is different. This is all because the source tends to change over time.

    Also, I really don't care how it's accomplished. We could simply 'fix' the source sheet for the export. We could just add data to the map sheet, saving it with a diff name to save the template. We could create a third sheet using the first two.

    Thanks!

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

    Re: Copy columns from one sheet to another in diff

    Here is the basic idea. It copies the data to the Map sheet; you could modify it to make copy of the map sheet first:

    Sub CopyToMap()
    Sheets("Source").Range("A1").CurrentRegion.Advance dFilter Action:=xlFilterCopy, _
    CopyToRange:=Sheets("Map").Range(Sheets("Map").Ran ge("A1"), Sheets("Map").Range("A1").End(xlToRight))
    End Sub

  5. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post

    Re: Copy columns from one sheet to another in diff

    Hans, thank you. This works for the specific request and is lovely tight code! If there are fields in the source that aren't in the map, or the order is different all is good. However, If there is a column on the map that doesn't exist on the source, I get the error "Run-time error '1004' The extract range has a missing or illegal field name."

    The reason I have additional column names on the map is because I have other sheets from which I'm bringing in data. I'm trying to map them too. Any ideas how to have it ignore fields on the map that are missing in the source.

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

    Re: Copy columns from one sheet to another in diff

    There are no doubt other ways to do this, but here is a possible solution. The 'missing' column names are temporarily added to the Source sheet.

    Sub CopyToMap()
    Dim lngMaxSourceCol As Long
    Dim lngMaxMapCol As Long
    Dim lngSourceCol As Long
    Dim lngMapCol As Long
    Dim rng As Range

    lngMaxSourceCol = Sheets("Source").Range("IV1").End(xlToLeft).Column
    lngMaxMapCol = Sheets("Map").Range("IV1").End(xlToLeft).Column
    Set rng = Sheets("Source").Range(Sheets("Source").Cells(1, 1), _
    Sheets("Source").Cells(1, lngMaxSourceCol))

    lngSourceCol = lngMaxSourceCol
    For lngMapCol = 1 To lngMaxMapCol
    ' Can we find the column name?
    If rng.Find(Sheets("Map").Cells(1, lngMapCol), , , xlPart) Is Nothing Then
    ' No, so add it
    lngSourceCol = lngSourceCol + 1
    Sheets("Source").Cells(1, lngSourceCol) = Sheets("Map").Cells(1, lngMapCol)
    End If
    Next lngMapCol

    ' This is the original code (advanced filter / copy)
    Sheets("Source").Range("A1").CurrentRegion.Advance dFilter Action:=xlFilterCopy, _
    CopyToRange:=Sheets("Map").Range(Sheets("Map").Ran ge("A1"), _
    Sheets("Map").Range("A1").End(xlToRight))

    ' Remove temporary column names
    If lngSourceCol > lngMaxSourceCol Then
    Sheets("Source").Range(Sheets("Source").Cells(1, lngMaxSourceCol + 1), _
    Sheets("Source").Cells(1, lngSourceCol)).ClearContents
    End If

    Set rng = Nothing
    End Sub

  7. #7
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post

    Re: Copy columns from one sheet to another in diff

    Wow! Thanks a bunch. I'll look at this carefully to understand. I was thinking of adding my other data to the 'source' sheet and then letting your elegant little code snippet do the work.

  8. #8
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy columns from one sheet to another in diff

    I need something similar but the attachment is missing--can someone help???
    thanx
    smbs

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy columns from one sheet to another in diff

    Same answer as I just posted to your post in the Excel forum.
    Legare Coleman

Posting Permissions

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