Sub GetTB()
' Runs off of InTacct Consolidated Trial Balance - Cut and Paste starting at 2nd Title Row
' Convert Acccount Text to Numeric
Columns(1).Select
With Selection
.TextToColumns Destination:=ActiveCell.Range("A1")
.ColumnWidth = 16.86
.EntireColumn.AutoFit
End With
' Get Table Dimensions
FINALROW = Cells(Rows.Count, 1).End(xlUp).Row
FinalColumn = Cells(5, Columns.Count).End(xlToLeft).Column
' Validate that any new accounts
For i = 6 To FINALROW - 1
Cells(i, 9).FormulaR1C1 = "=vlookup(RC[-8],Validation_Table,1,0)"
Next i
' Test for errors to ID missing accounts on Validation Table
Set r = Cells(6, 9).Resize(FINALROW - 6).SpecialCells(xlErrors)
If Not r Is Nothing Then
Cells(2, 2).Interior.Color = vbRed
Set r = Nothing
End If
' Name Table Range for Vlookup
Range("A5:I" & FINALROW - 1).Name = "CurrentTB"
' Assign which column to update
mySelection = InputBox("Enter Column Quarter to Post to 1-2-3-4 (5 for Today): ")
' Assign Column on MyData Tab for Vlookup
Select Case mySelection
Case "1"
MyColumn = 4
' Post to MyData Column from Get TB 1st Qtr
With Sheets("MyData")
MyFinalRow = .Cells(Rows.Count, 2).End(xlUp).Row
For i = 2 To MyFinalRow - 1
.Cells(i, MyColumn).FormulaR1C1 = "=IFERROR(vlookup(RC[-2],CurrentTB,8,False),""0"")"
.Cells(i, MyColumn) = .Cells(i, MyColumn) * 1
Next i
.Range(.Cells(2, MyColumn), .Cells(MyFinalRow - 1, MyColumn)).Copy
.Range(.Cells(2, MyColumn), .Cells(MyFinalRow - 1, MyColumn)).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End With
Case "2"
MyColumn = 5
' Post to MyData Column from Get TB 2nd Qtr YTD
With Sheets("MyData")
MyFinalRow = .Cells(Rows.Count, 2).End(xlUp).Row
For i = 2 To MyFinalRow - 1
.Cells(i, MyColumn).FormulaR1C1 = "=IFERROR(vlookup(RC[-3],CurrentTB,8,False),""0"")"
.Cells(i, MyColumn) = .Cells(i, MyColumn) * 1
Next i
.Range(.Cells(2, MyColumn), .Cells(MyFinalRow - 1, MyColumn)).Copy
.Range(.Cells(2, MyColumn), .Cells(MyFinalRow - 1, MyColumn)).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End With
Case "3"
MyColumn = 6
' Post to MyData Column from Get TB 3rd Qtr YTD
With Sheets("MyData")
MyFinalRow = .Cells(Rows.Count, 2).End(xlUp).Row
For i = 2 To MyFinalRow - 1
.Cells(i, MyColumn) = "=IFERROR(vlookup(RC[-4],CurrentTB,8,False),""0"")"
.Cells(i, MyColumn) = .Cells(i, MyColumn) * 1
Next i
.Range(.Cells(2, MyColumn), .Cells(MyFinalRow - 1, MyColumn)).Copy
.Range(.Cells(2, MyColumn), .Cells(MyFinalRow - 1, MyColumn)).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End With
Case "4"
MyColumn = 7
' Post to MyData Column from Get TB 4th Qtr YTD
With Sheets("MyData")
MyFinalRow = .Cells(Rows.Count, 2).End(xlUp).Row
For i = 2 To MyFinalRow - 1
.Cells(i, MyColumn).FormulaR1C1 = "=IFERROR(vlookup(RC[-5],CurrentTB,8,False),""0"")"
.Cells(i, MyColumn) = .Cells(i, MyColumn) * 1
Next i
.Range(.Cells(2, MyColumn), .Cells(MyFinalRow - 1, MyColumn)).Copy
.Range(.Cells(2, MyColumn), .Cells(MyFinalRow - 1, MyColumn)).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End With
Case "5"
MyColumn = 13
' Post to MyData Column from Get TB Today
With Sheets("MyData")
MyFinalRow = .Cells(Rows.Count, 2).End(xlUp).Row
For i = 2 To MyFinalRow - 1
.Cells(i, MyColumn).FormulaR1C1 = "=IFERROR(vlookup(RC[-11],CurrentTB,8,False),""0"")"
.Cells(i, MyColumn) = .Cells(i, MyColumn) * 1
Next i
.Range(.Cells(2, MyColumn), .Cells(MyFinalRow - 1, MyColumn)).Copy
.Range(.Cells(2, MyColumn), .Cells(MyFinalRow - 1, MyColumn)).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End With
End Select
' Post to MyData Column from Get TB
' With Sheets("MyData")
' MyFinalRow = .Cells(Rows.Count, 2).End(xlUp).Row
'
' For i = 2 To MyFinalRow - 1
'
' .Cells(i, MyColumn).FormulaR1C1 = "=vlookup(RC[-8],CurrentTB,8,False)"
'
' Next i
'
' End With
End Sub