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

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

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

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

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" <(E-Mail Removed)> wrote in message
news:BC83CF55-63A8-4E41-AE6C-(E-Mail Removed)...
>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

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" <(E-Mail Removed)> wrote in message
> news:BC83CF55-63A8-4E41-AE6C-(E-Mail Removed)...
> >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
>
>
>

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" <(E-Mail Removed)> wrote in message
news:77259125-3A43-4DA1-8035-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:BC83CF55-63A8-4E41-AE6C-(E-Mail Removed)...
>> >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
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
> news:77259125-3A43-4DA1-8035-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:BC83CF55-63A8-4E41-AE6C-(E-Mail Removed)...
> >> >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
> >>
> >>
> >>
>
>