How to display data from one worksheet on another?

[bebe1279]

Hi everyone

I'm working on a project and have run into a problem. I'm new to vba and am learning as I go, so please bare with me. O.k, so in this part of my project I'm attempting to get data from one worksheet named "Archive" and displaying that data onto another worksheet named "Summary". Data pulled from Archive will be calculated before being shown in Summary. Here is my code so far.

Sub GenerateSummaryData()
  
'Get data from Archive sheet


With Worksheets("Archive")
'Weekly income
    wds = Range("b12") 'weekly daily sales
    wvs = Range("b1832").Value 'weekly vending sales
    wlc = Range("d1756").Value 'weekly lottery commissions


'Monthly income
    mds = Range("b47").Value 'monthly daily sales
    mvs = Range("b1846").Value 'monthly vending sales
    mlc = Range("d1766").Value ' monthly lottery commissions
    
'Yearly income
    yds = Range("b417").Value 'yearly daily sales
    yvs = Range("b1951").Value 'yearly vending sales
    ylc = Range("d1823").Value 'yearly lottery commissions
    
'Weekly Expenses
    wde = WorksheetFunction.Sum(Range("c12,d12")) 'weekly daily expenses
    wcr = Range("c441").Value 'weekly check register
    wld = Range("e1756").Value 'weekly lottery dues
    
'Monthly Expenses
    mde = WorksheetFunction.Sum(Range("c47,d47")) 'monthly daily expenses
    mcr = Range("c545").Value 'monthly check register
    mld = Range("e1766").Value 'monthly lottery dues
    
'Yearly Expenses
    yde = WorksheetFunction.Sum(Range("c417,d417")) ' yearly daily expenses
    ycr = Range("c1750").Value 'yearly check register
    yld = Range("e1823").Value 'yearly lottery dues
End With


'Display data on summary sheet


With Worksheets("Summary")
'Calculate weekly sales/income
   Range("e12").Value = wds + wvs + wlc
'Calculate weekly expenses
    Range("e14").Value = wde + wcr + wld
'Calculate rought net
    Range("e16") = Range("e12").Value - Range("e14").Value
        
'Calculate monthly sales/income
    
'Calculate monthly expenses


'Calculate rough net
    
End With
End Sub

This code runs without an errors, however, no data shows on the summary sheet. What am I missing or doing wrong? Any and all help is appreciated, thank you.

 

[soulfire21]

When you use WITH...END WITH you need to add periods before your qualifiers. A qualifier is a reference like Workbook, Worksheet, Range, etc.

For example this:

With Worksheets("Summary")
'Calculate weekly sales/income
   Range("e12").Value = wds + wvs + wlc
'Calculate weekly expenses
    Range("e14").Value = wde + wcr + wld
'Calculate rought net
    Range("e16") = Range("e12").Value - Range("e14").Value
        
'Calculate monthly sales/income
    
'Calculate monthly expenses




'Calculate rough net
    
End With

Should be:

With Worksheets("Summary")
'Calculate weekly sales/income
    .Range("e12").Value = wds + wvs + wlc
'Calculate weekly expenses
    .Range("e14").Value = wde + wcr + wld
'Calculate rought net
    .Range("e16") = Range("e12").Value - Range("e14").Value
        
'Calculate monthly sales/income
    
'Calculate monthly expenses




'Calculate rough net
    
End With

 

[mole999]

Does this work

With Worksheets("Summary")
'Calculate weekly sales/income
   .Range("e12") = wds + wvs + wlc
'Calculate weekly expenses
    .Range("e14") = wde + wcr + wld
'Calculate rought net
    .Range("e16") = .Range("e12") - .Range("e14")
        
'Calculate monthly sales/income
    
'Calculate monthly expenses


'Calculate rough net
    
End With

 

[bebe1279]

Thank you to you both. This worked for all except the lines that contains worksheet functions. How would I handle those?

 

[soulfire21]

I believe you need to qualify WorksheetFunction with Application.

For example, to sum the values in cells A1 through A6:

With Worksheets("Sheet1")
    .Application.WorksheetFunction.Sum(.Range("A1:A6").Value)
End With

Notice the period in front of Range("A1:A6") as well.

 

[mole999]

try

wde = WorksheetFunction.Sum(Range("c12,d12")) 'weekly daily expenses

wde = "=SUM(c12:d12)" 'weekly daily expenses

 

[bebe1279]

That worked like a charm, thanks again.