New to VBA. Stop duplication when submitting data from one sheet to a summay sheet (containing If statements).

esneaker

New Member
Joined
Feb 1, 2015
Messages
11
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?
 
Last edited:

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top