Calculation Help and balances pleaseeeeee
 

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

Calculation Help and balances pleaseeeeee
 

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

"Yossy" wrote:

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

Calculation Help and balances pleaseeeeee
 

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" wrote:

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

"Yossy" wrote:

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

Calculation Help and balances pleaseeeeee
 

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

"Yossy" wrote:

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" wrote:

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

"Yossy" wrote:

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

Calculation Help and balances pleaseeeeee
 

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


--


Gary

"Yossy" wrote in message
...
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" wrote:

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

"Yossy" wrote:

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

Calculation Help and balances pleaseeeeee
 

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" wrote:

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


--


Gary

"Yossy" wrote in message
...
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" wrote:

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

"Yossy" wrote:

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

Calculation Help and balances pleaseeeeee
 

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

--

Gary
Excel 2003


"Yossy" wrote in message
...
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" wrote:

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


--


Gary

"Yossy" wrote in message
...
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" wrote:

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

"Yossy" wrote:

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

Calculation Help and balances pleaseeeeee
 

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

"Gary Keramidas" wrote:

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

--

Gary
Excel 2003


"Yossy" wrote in message
...
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" wrote:

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


--


Gary

"Yossy" wrote in message
...
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" wrote:

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

"Yossy" wrote:

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