Greetings.
**New to VBA**
I have created a financial document that contains a hidden worksheet (template to be called upon from a UserForm based on the month selected...the name is changed as it is copied). When the user hits "submit" on the form, the information entered gets transferred to a summary page (which updates charts and graphs). The problem is that the information sent to the summary page is duplicated only on certain cells. I'm not sure if I'm entering the If statements incorrectly or not. Please help. A portion of the code is below. Thank you for your time.
-Also, a function of this button is to send information the 'Data' sheet which I would like to hide and unhide
Sub SummaryCells()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name Like "*January*" Then
Sheets("Summary YTD").Range("B3:B7").Value = Sheets("January").Range("B3:B7").Value
Sheets("Summary YTD").Range("B11:B17").Value = Sheets("January").Range("B11:B17").Value
Sheets("Summary YTD").Range("B23:B64").Value = Sheets("January").Range("B23:B64").Value
Sheets("Summary YTD").Range("B71:B76").Value = Sheets("January").Range("B71:B76").Value
Sheets("Summary YTD").Range("B85:B90").Value = Sheets("January").Range("B85:B90").Value
Sheets("Summary YTD").Range("B98").Value = Application.WorksheetFunction.Sum(Range("B98:B102"))
Sheets("Summary YTD").Range("B100").Value = Application.WorksheetFunction.Sum(Range("B104:B107"))
Sheets("Summary YTD").Range("B102").Value = Application.WorksheetFunction.Sum(Range("B109:B113"))
Sheets("Summary YTD").Range("B104").Value = Application.WorksheetFunction.Sum(Range("B116:B119"))
Sheets("Summary YTD").Range("B106").Value = Application.WorksheetFunction.Sum(Range("B121:B126"))
Sheets("Summary YTD").Range("B108").Value = Application.WorksheetFunction.Sum(Range("B128:B135"))
Sheets("Data").Visible = True
Sheets("Data").Range("B140").Value = Sheets("January").Range("B138").Value
Sheets("Data").Range("B141").Value = Sheets("January").Range("B140").Value
Sheets("Data").Visible = False
End If
If ws.Name Like "*Febuary*" Then
Sheets("Summary YTD").Range("C3:C7").Value = Sheets("Febuary").Range("B3:B7").Value ' run this macro
Sheets("Summary YTD").Range("C11:C17").Value = Sheets("Febuary").Range("B11:B17").Value
Sheets("Summary YTD").Range("C23:C64").Value = Sheets("Febuary").Range("B23:B64").Value
Sheets("Summary YTD").Range("C71:C76").Value = Sheets("Febuary").Range("B71:B76").Value
Sheets("Summary YTD").Range("C85:C90").Value = Sheets("Febuary").Range("B85:B90").Value
Sheets("Summary YTD").Range("C98").Value = Application.WorksheetFunction.Sum(Range("B98:B102"))
Sheets("Summary YTD").Range("C100").Value = Application.WorksheetFunction.Sum(Range("B104:B107"))
Sheets("Summary YTD").Range("C102").Value = Application.WorksheetFunction.Sum(Range("B109:B113"))
Sheets("Summary YTD").Range("C104").Value = Application.WorksheetFunction.Sum(Range("B116:B119"))
Sheets("Summary YTD").Range("C106").Value = Application.WorksheetFunction.Sum(Range("B121:B126"))
Sheets("Summary YTD").Range("C108").Value = Application.WorksheetFunction.Sum(Range("B128:B135"))
Sheets("Data").Visible = True
Sheets("Data").Range("C140").Value = Sheets("Febuary").Range("B138").Value
Sheets("Data").Range("C141").Value = Sheets("Febuary").Range("B140").Value
Sheets("Data").Visible = False
End If
End Sub
Is it because the "Sheet" is not named with the Application.WorksheetFunction? If so, how do I correct that?
**New to VBA**
I have created a financial document that contains a hidden worksheet (template to be called upon from a UserForm based on the month selected...the name is changed as it is copied). When the user hits "submit" on the form, the information entered gets transferred to a summary page (which updates charts and graphs). The problem is that the information sent to the summary page is duplicated only on certain cells. I'm not sure if I'm entering the If statements incorrectly or not. Please help. A portion of the code is below. Thank you for your time.
-Also, a function of this button is to send information the 'Data' sheet which I would like to hide and unhide
Sub SummaryCells()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name Like "*January*" Then
Sheets("Summary YTD").Range("B3:B7").Value = Sheets("January").Range("B3:B7").Value
Sheets("Summary YTD").Range("B11:B17").Value = Sheets("January").Range("B11:B17").Value
Sheets("Summary YTD").Range("B23:B64").Value = Sheets("January").Range("B23:B64").Value
Sheets("Summary YTD").Range("B71:B76").Value = Sheets("January").Range("B71:B76").Value
Sheets("Summary YTD").Range("B85:B90").Value = Sheets("January").Range("B85:B90").Value
Sheets("Summary YTD").Range("B98").Value = Application.WorksheetFunction.Sum(Range("B98:B102"))
Sheets("Summary YTD").Range("B100").Value = Application.WorksheetFunction.Sum(Range("B104:B107"))
Sheets("Summary YTD").Range("B102").Value = Application.WorksheetFunction.Sum(Range("B109:B113"))
Sheets("Summary YTD").Range("B104").Value = Application.WorksheetFunction.Sum(Range("B116:B119"))
Sheets("Summary YTD").Range("B106").Value = Application.WorksheetFunction.Sum(Range("B121:B126"))
Sheets("Summary YTD").Range("B108").Value = Application.WorksheetFunction.Sum(Range("B128:B135"))
Sheets("Data").Visible = True
Sheets("Data").Range("B140").Value = Sheets("January").Range("B138").Value
Sheets("Data").Range("B141").Value = Sheets("January").Range("B140").Value
Sheets("Data").Visible = False
End If
If ws.Name Like "*Febuary*" Then
Sheets("Summary YTD").Range("C3:C7").Value = Sheets("Febuary").Range("B3:B7").Value ' run this macro
Sheets("Summary YTD").Range("C11:C17").Value = Sheets("Febuary").Range("B11:B17").Value
Sheets("Summary YTD").Range("C23:C64").Value = Sheets("Febuary").Range("B23:B64").Value
Sheets("Summary YTD").Range("C71:C76").Value = Sheets("Febuary").Range("B71:B76").Value
Sheets("Summary YTD").Range("C85:C90").Value = Sheets("Febuary").Range("B85:B90").Value
Sheets("Summary YTD").Range("C98").Value = Application.WorksheetFunction.Sum(Range("B98:B102"))
Sheets("Summary YTD").Range("C100").Value = Application.WorksheetFunction.Sum(Range("B104:B107"))
Sheets("Summary YTD").Range("C102").Value = Application.WorksheetFunction.Sum(Range("B109:B113"))
Sheets("Summary YTD").Range("C104").Value = Application.WorksheetFunction.Sum(Range("B116:B119"))
Sheets("Summary YTD").Range("C106").Value = Application.WorksheetFunction.Sum(Range("B121:B126"))
Sheets("Summary YTD").Range("C108").Value = Application.WorksheetFunction.Sum(Range("B128:B135"))
Sheets("Data").Visible = True
Sheets("Data").Range("C140").Value = Sheets("Febuary").Range("B138").Value
Sheets("Data").Range("C141").Value = Sheets("Febuary").Range("B140").Value
Sheets("Data").Visible = False
End If
End Sub
Is it because the "Sheet" is not named with the Application.WorksheetFunction? If so, how do I correct that?
Last edited: