Exporting Data?

  • Thread starter Thread starter Doug
  • Start date Start date
D

Doug

I am exporting data from the web. I export unto a file called Zack's
Export.xlsm. From here I select the data that I want to put into another
..xlsm file table so that I can later sort the data. How can I pick and choose
the data by column headings? For example, it makes no sense to have relative
data that exports from the web in columns A and Z, when I can have them side
by side; A&B. I am guessing the best way to do this is by column heading
recognition? How can I do this? If I am going about it all wrong please tell
me?
 
the macro below copies data from sheet 1 to the correct column in sheet 2
like this

sheet1
Header Data
a 1
b 2
c 3
b 5

sheet2
a b c
1
2
3
5

Sub test()

Set sht1 = Sheets("Sheet1")
Set Sht2 = Sheets("Sheet2")

With Sht2
LastCol = .Rows(1, Columns.Count).End(xlToLeft).Column
NewRow = 2
End With
RowCount = 2
With sht1
Do While .Range("A" & RowCount) <> ""
'data to copy from sheet 1
HeaderData = .Range("A" & RowCount)
Data = .Range("B" & RowCount)
With Sht2
'look for header in row 1 on sht 2
Set c = .Rows(1).Find(what:=HeaderData, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
'put data in new column
.Cells(1, NewCol) = HeaderData
.Cells(NewRow, NewCol) = Data
NewCol = NewCol + 1
Else
'put data under an existing header
.Cells(NewRow, c.Column) = Data
End If
End With
NewRow = NewRow + 1
RowCount = RowCount + 1
Loop
End With

End Sub
 
Back
Top