Copy columns from one sheet to another in diff ord

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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!

Reply | Quote

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.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Sheets(“Map”).Range(Sheets(“Map”).Range(“A1”), Sheets(“Map”).Range(“A1”).End(xlToRight))
End Sub

Reply | Quote

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.

Reply | Quote

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.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Sheets(“Map”).Range(Sheets(“Map”).Range(“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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote