Combine Multiple Worksheets w/ Different Headers Into One Worksheet

[griffinlross]

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]

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