Calculation Help and balances pleaseeeeee

[Yossy]

I have a Workbook that contains multiple sheet. I want to calculate on
specific sheets named _Balances.

Copy G4 and paste value to G2.
Then I need to add G20 to G3.
Finally Add G21 to G10

Please all help totally appreciated. I have multiple sheets that these need
to be done on.

Thanks

 

[Sheeloo]

You need to do this for ALL sheets or only for _Balances?

 

[Yossy]

I want to do this for Sheets that have e.g Book_Balances, Paper_Balances,
Pen_Balances. I have multiple sheets but this should only affect those sheet
with named _Balances added to their sheet name.

Thanks a big bunch

 

[Sheeloo]

Try
(Insert this in a module of the workbook you want to process.. This needs
the workbook to be active...)

Sub CopyValues()
Dim ws As Worksheet

For Each ws In Worksheets
If (Right(ws.Name, 9) = "_Balances") Then
ws.Activate
Range("G4").Select
Selection.Copy
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("G20").Select
Application.CutCopyMode = False
Selection.Copy
Range("G3").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:= _
False, Transpose:=False
Range("G21").Select
Application.CutCopyMode = False
Selection.Copy
Range("G10").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:= _
False, Transpose:=False
End If
Next
End Sub

 

[Gary Keramidas]

you probably could get by with something like this:

Sub test()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
If InStr(1, ws.Name, "_Balances") Then
With ws
.Range("G2").Value = .Range("G4").Value
With .Range("G3")
.Value = .Value + ws.Range("G20").Value
End With
With .Range("G10")
.Value = .Value + ws.Range("G21").Value
End With
End With
End If
Next
Application.ScreenUpdating = True
End Sub

 

[Yossy]

Works great. Thanks. I forgot to note that the G21 cell should turn 0.00 once
added to G10. Can you please help me adjust.

The code works well except for the adjustment. Thanks a big bunch, I really
appreciate it.

 

[Gary Keramidas]

see if this is what you want:

Sub test()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
If InStr(1, ws.Name, "_Balances") Then
With ws
.Range("G2").Value = .Range("G4").Value
With .Range("G3")
.Value = .Value + ws.Range("G20").Value
End With
With .Range("G10")
.Value = .Value + ws.Range("G21").Value
End With
.Range("G21").Value = 0
End With

End If
Next
Application.ScreenUpdating = True
End Sub

 

[Yossy]

Gary thanks so much it works!!

When adding G20 to G3: In cell (G3) I have something like this =E26+200+500.
The code added all and cleared all the nos. Is it possible to leave my values
and even show the new value added. Let say cell G20 = 100.

Is it possible to have =E26+200+500+100 in G3 once code is executed. This
way I see the values added not the whole added nos.

Thanks a big bunch. I really appreciate it