Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Balances | Excel Discussion (Misc queries) | |||
running balances | Excel Discussion (Misc queries) | |||
Showing zero balances | Excel Discussion (Misc queries) | |||
How to link endings balances to beginning balances on different sh | Excel Worksheet Functions | |||
Help! Please! - - - PLEASEEEEEE! | Excel Programming |