Populating cells with a Date across 12 TABS

mtarler

Macro Help Please

Hello Sir, the following Macro is used on a report that gathers information, sorts the information and arranges the information so I can copy and paste it into another workbook. I have had to expand on the information collected, add a column, for Rate Collection. This column did not exist in the workbook for which it was going into. I just need some help adding it to the process so I can copy and paste it with the other data. The workbook this Macro works on has multiple Tabs so the Macro has to reach into an additional Tab to grab the Rates associated with building and place them accordingly in a ROW along side the other collected information for a building number. This Macro works really well and it is called "Data_Prep". I just need it adjusted to add the Rate for the Electricity & Gas Rates. What I am asking for is to add to the existing macro a process to open the "Property PreBill Detail El Tab and grab the EL Rate from column "S" and then open the "Property PreBill Detail GS Tab and grab the GAS Rates from Column "S" and place the Rates in column "S" per the results of the PreBill EL Read Data and in column "P" per the results of the PreBill GS Read Data so I can copy and paste the data into my workbook. I'm not asking for a rewrite of the macro, just an adjustment to the code for the inclusion of the EL Rate and the GS Rate. I have a separate macro for the Water in case the question was to be asked. If someone feels this code could be more efficient I am open to what ever gets the job done.

Thank you and I hope someone is up to the challenge.

Sub Prep_Data()
'
' Prep_Data Macro
' This Macro preps data to be copied and pasted into the Monthly Utility Reads workbook
'
' Keyboard Shortcut: Ctrl+Shift+P
'
Dim wb As Workbook
Dim ws As Worksheet
Dim m As Long
Dim r As Range
Application.ScreenUpdating = False
Set wb = ActiveWorkbook
Set ws = wb.Worksheets("PreBill GS Read Data")
Do
Set r = ws.Range("D:D").Find(What:="104 CC Street")
If r Is Nothing Then Exit Do
r.EntireRow.Delete
Loop
m = ws.Range("F" & ws.Rows.Count).End(xlUp).Row
ws.Columns("F:F").Insert Shift:=xlShiftToRight
With ws.Columns("F:F")
.NumberFormat = "General"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With ws.Range("F2:F" & m)
.Formula = "=LEFT(G2,FIND("" "",G2)-1)"
.Value = .Value
End With
ws.Columns("F:F").Insert Shift:=xlShiftToRight
With ws.Columns("F:F")
.NumberFormat = "General"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ws.Range("F2:F" & m).FormulaR1C1 = "=IF(ISNUMBER(--RC[1]),--RC[1],LEFT(RC[1],LEN(RC[1])-1)+CODE(RIGHT(RC[1]))/1000)"
With ws.AutoFilter.Sort
.SortFields.Clear
.SortFields.Add Key:=ws.Range("F1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ws.Columns("H:J").Delete Shift:=xlShiftToLeft
ws.Columns("F:F").Delete Shift:=xlShiftToLeft
With ws.Range("N2:N" & m)
.Formula = "=IF(E2=""Vacant"",""Vacant"","""")"
.Value = .Value
End With
ws.Range("I:I").Insert
Set ws = Sheets("PreBill EL Read Data")
Do
Set r = ws.Range("D:D").Find(What:="104 CC Street")
If r Is Nothing Then Exit Do
r.EntireRow.Delete
Loop
m = ws.Range("I" & ws.Rows.Count).End(xlUp).Row
ws.Columns("I:I").Insert Shift:=xlShiftToRight
With ws.Columns("I:I")
.NumberFormat = "General"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ws.Range("I2:I" & m).FormulaR1C1 = "=VLOOKUP(RC[-5],'PreBill GS Read Data'!R2C4:R500C7,3,FALSE)"
ws.Columns("I:I").Insert Shift:=xlShiftToRight
With ws.Columns("I:I")
.NumberFormat = "General"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ws.Range("I2:I" & m).FormulaR1C1 = "=IF(ISNUMBER(--RC[1]),--RC[1],LEFT(RC[1],LEN(RC[1])-1)+CODE(RIGHT(RC[1]))/1000)"
With ws.AutoFilter.Sort
.SortFields.Clear
.SortFields.Add Key:=ws.Range("I1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ws.Columns("I:I").Delete Shift:=xlShiftToLeft
With ws.Range("Q2:Q" & m)
.Formula = "=IF(E2=""Vacant"",""Vacant"","""")"
.Value = .Value
End With
ws.Range("L:L").Insert
Application.ScreenUpdating = True
End Sub