VBA and Excel experts,
Need help on this one...I'm looking for a looping method to simplify the below coding.
Multiple columns in "Input" should be copied to "Data History" columns that match the sheet's name in cell(B5) in "Input"
i.e If a sheet name in "Input" cell (B5) is "T-1", multiple columns in "Input" will be copied to multiple columns in "Data History" associated to "T-1" and according to the category, PI, PII and PIII
And for "T1" data the "Input" data will be pasted in the next column of "T-1" data in "Data History" according to the category PI,PII and PIII as well; please refer to below coding
Basically, the columns range in "Input" are fixed, but the data pasted in columns range in "Data History" are not.
Hopefully the explanation is clear, if not then please let me know.
Thanks in advance for your help.
Need help on this one...I'm looking for a looping method to simplify the below coding.
Multiple columns in "Input" should be copied to "Data History" columns that match the sheet's name in cell(B5) in "Input"
i.e If a sheet name in "Input" cell (B5) is "T-1", multiple columns in "Input" will be copied to multiple columns in "Data History" associated to "T-1" and according to the category, PI, PII and PIII
And for "T1" data the "Input" data will be pasted in the next column of "T-1" data in "Data History" according to the category PI,PII and PIII as well; please refer to below coding
Basically, the columns range in "Input" are fixed, but the data pasted in columns range in "Data History" are not.
Hopefully the explanation is clear, if not then please let me know.
Thanks in advance for your help.
VBA Code:
Sub DailyInput()
Dim ShtName As String
With Sheets("Input")
ShtName = .Range("B5").Value
If Evaluate("isref('" & ShtName & "'!A1)") Then
Sheets(ShtName).Range("A3").Value = .Range("A5").Value
Sheets(ShtName).Range("A5:EY1204").Value = .Range("A7:EY1206").Value
If ShtName = "T-1" Then
Sheets("Data History").Range("CE4:CE203").Value = Range("CK7:CK206").Value 'PI
Sheets("Data History").Range("DK4:DK203").Value = Range("CL7:CL206").Value 'P2
Sheets("Data History").Range("EQ4:EQ203").Value = Range("CM7:CM206").Value 'P3
ElseIf ShtName = "T1" Then
Sheets("Data History").Range("CF4:CF203").Value = Range("CK7:CK206").Value 'PI
Sheets("Data History").Range("DL4:DL203").Value = Range("CL7:CL206").Value 'P2
Sheets("Data History").Range("ER4:ER203").Value = Range("CM7:CM206").Value 'P3
ElseIf ShtName = "T2" Then
Sheets("Data History").Range("CG4:CG203").Value = Range("CK7:CK206").Value 'PI
Sheets("Data History").Range("DM4:DM203").Value = Range("CL7:CL206").Value 'P2
Sheets("Data History").Range("ES4:ES203").Value = Range("CM7:CM206").Value 'P3
End If
Else
MsgBox ShtName & " does not exist"
End If
End With
End Sub