PDA

View Full Version : Combine Multiple Worksheets w/ Different Headers Into One Worksheet



griffinlross
08-02-2017, 11:57 AM
I need help combining 3 worksheets into one and some of the columns have different headers and are in a different order. All of this is in Excel 2016. If there is a VBA to run that can do this that would be much appreciated.

The first worksheet's columns are in the following order:

Date Time
C
Event
Period
Surv(M)
Actual
Prior
Revised
S
Ticker


The second:

DateTime
Name
Country
Volatility
Actual
Previous
Consensus


Third:


Date
Event
Impact
Previous
Consensus
Actual
Currency


I would like to sort the master sheet by date and the third sheets date format is 2017, August 02, 00:00
Meanwhile, the first two sheets date format is 08/02/17 03:30
Event and Name can be merged into one column. C column is the same as Country in the second worksheet but country is included in the event column of the third worksheet in parenthesis. S, Impact, and volatility can be combined into one column. Prior and previous are the same, actuals are the same so those can be combined as well. Is it possible to update the worksheet automatically as well using a calendar online or bloomberg?

offthelip
08-03-2017, 04:39 PM
In answer to you question about updating a spreadsheet automatically it is certainly possible. There is a Bloomberg APi which allows you to use specific bloomberg functions which update the data directly into the worksheet. It is alos possible to update spreadsheet directly form most websites

For combining the worksheets:
try this:

Sub movedat()
Dim ws As Worksheet
Dim rng2 As Range
Dim rng3 As Range
Dim rng1 As Range
Dim lastrow2 As Integer
Dim fulltext As String


Dim map2(1 To 7) As Integer
Dim map3(1 To 7) As Integer
map2(1) = 1
map2(2) = 3
map2(3) = 2
map2(4) = 9
map2(5) = 6
map2(6) = 7
map2(7) = 0
map3(1) = 1
map3(2) = 3
map3(3) = 9
map3(4) = 7
map3(5) = 0
map3(6) = 6
map3(7) = 0


Set ws = Worksheets("Sheet2")
With ws.UsedRange
lastcol2 = .Columns(.Columns.Count).Column
lastrow2 = .Rows(.Rows.Count).Row
End With
Set rng2 = ws.Range(ws.Cells(1, 1), ws.Cells(lastrow2, lastcol2))
Set ws = Worksheets("Sheet3")
With ws.UsedRange
lastcol3 = .Columns(.Columns.Count).Column
lastrow3 = .Rows(.Rows.Count).Row
End With
Set rng3 = ws.Range(ws.Cells(1, 1), ws.Cells(lastrow3, lastcol3))


Set ws = Worksheets("Sheet1")
With ws.UsedRange
lastcol1 = .Columns(.Columns.Count).Column
lastrow1 = .Rows(.Rows.Count).Row
End With
endrow = lastrow1 + lastrow2 + lastrow3 + 1
Set rng1 = ws.Range(ws.Cells(lastrow1 + 1, 1), ws.Cells(endrow, lastcol3))


For i = 1 To 6
For j = 2 To lastrow2
If map2(i) <> 0 Then
rng1(j, map2(i)) = rng2(j, i)
End If
Next j
Next i
For i = 1 To 6
For j = lastrow2 + 1 To (lastrow2 + lastrow3 + 1)
If map3(i) <> 0 Then
If i = 2 Then
' split the county out of colb
fulltext = rng3(j - lastrow2 + 1, i).Value
para1 = InStr(fulltext, "(")
para2 = InStr(fulltext, ")")
If para1 > 0 And para2 > 0 Then
cntry = Mid(fulltext, para1 + 1, para2 - para1 - 1)
rng1(j, 2) = cntry
End If

End If
rng1(j, map3(i)) = rng3(j - lastrow2 + 1, i)
End If
Next j
Next i




End Sub