Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy To New Sheet
I would like to create a new worksheet and create values based on an
active sheet (example sheet at http://spreadsheets.google.com/ccc?k...5oUSDoqTgI6sRA ) The macro would look at the active sheet and subtract the value of B9 from B2 and put the result into a new sheet at B2. It would continue through each column and row of the 1st sheet until the range is complete. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy To New Sheet
Your description leaves a lot of holes.
'continue through each column and row of the 1st sheet until the range is complete.' Which range? Column B? Row 2? UsedRange? Describe the range. If it is no all numeric, there is no need to subtract strings. The assumption would be the the seventh cell below another should be subtracted on each iteration. How do you want the results entered into the new worksheet? Continue down column B? Continue across Row 2? "caveman.savant" wrote in message ... I would like to create a new worksheet and create values based on an active sheet (example sheet at http://spreadsheets.google.com/ccc?k...5oUSDoqTgI6sRA ) The macro would look at the active sheet and subtract the value of B9 from B2 and put the result into a new sheet at B2. It would continue through each column and row of the 1st sheet until the range is complete. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy To New Sheet
My bad.
The sheet has a set rows and columns with numbers or false as values. the last row and column has no values in them. This would be B2:G3 in this example. B9:G10 also have values in them, but just numbers. I want to subtract B9 from B2, copy that result to a new sheet at B2. The new sheet would look just as B2:G3 in the orginal sheet. Then C9 minus C2, continuing to do the same until G9 - G2. Next go back to B10 and subtract from B3. Sometimes the number of columns and rows are more that the example I've show here. Maybe sometime 5 row & 5 Columns. always with a blanks cell following or in this case in H3 the total of the cells. Sorry for not being clear. I'm just a Caveman after all. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy To New Sheet
I should have checked you link first. I assumed that you want the results on
the new sheet to be in the same order as the data on the source sheet. Sub tract() Dim sh As Worksheet, ns As Worksheet, lc As Long Dim lcns As Long Set sh = ActiveSheet Worksheets.Add After:=Sheets(Sheets.Count) Set ns = ActiveSheet lc = sh.Cells(2, Columns.Count).End(xlToLeft).Column n = 2 For i = 2 To lc ns.Cells(2, n) = sh.Cells(2, i) - sh.Cells(9, i) n = n + 1 Next End Sub "caveman.savant" wrote: I would like to create a new worksheet and create values based on an active sheet (example sheet at http://spreadsheets.google.com/ccc?k...5oUSDoqTgI6sRA ) The macro would look at the active sheet and subtract the value of B9 from B2 and put the result into a new sheet at B2. It would continue through each column and row of the 1st sheet until the range is complete. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy To New Sheet
Just to clean up the Dim statements. By the way, this goes in the public
module1. Also, I would suggest that you substitute the actual sheet name for the first Set statement: Set sh = Sheets("use actual sheet name"), so that if you are not on that sheet when you run the macro, it will still work correctly. The new sheet automatically becomes the active sheet when it is created. Sub tract() Dim sh As Worksheet, ns As Worksheet, lc As Long Dim n As Long Set sh = ActiveSheet Worksheets.Add After:=Sheets(Sheets.Count) Set ns = ActiveSheet lc = sh.Cells(2, Columns.Count).End(xlToLeft).Column n = 2 For i = 2 To lc ns.Cells(2, n) = sh.Cells(2, i) - sh.Cells(9, i) n = n + 1 Next End Sub "JLGWhiz" wrote in message ... I should have checked you link first. I assumed that you want the results on the new sheet to be in the same order as the data on the source sheet. Sub tract() Dim sh As Worksheet, ns As Worksheet, lc As Long Dim lcns As Long Set sh = ActiveSheet Worksheets.Add After:=Sheets(Sheets.Count) Set ns = ActiveSheet lc = sh.Cells(2, Columns.Count).End(xlToLeft).Column n = 2 For i = 2 To lc ns.Cells(2, n) = sh.Cells(2, i) - sh.Cells(9, i) n = n + 1 Next End Sub "caveman.savant" wrote: I would like to create a new worksheet and create values based on an active sheet (example sheet at http://spreadsheets.google.com/ccc?k...5oUSDoqTgI6sRA ) The macro would look at the active sheet and subtract the value of B9 from B2 and put the result into a new sheet at B2. It would continue through each column and row of the 1st sheet until the range is complete. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy To New Sheet
Works really good.
On the 1st row. Can we get it to go back to the 1st column and down a row, until the rows with values stop? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy rows from one Data sheet to another sheet based on cell conte | Excel Discussion (Misc queries) | |||
Auto Copy/autofill Text from sheet to sheet if meets criteria | Excel Discussion (Misc queries) | |||
Copy Paste from Class Sheet to Filtered List on Combined Sheet | Excel Programming | |||
Help: auto-copy entire rows from 1 sheet (based on cell criteria) to another sheet. | Excel Programming | |||
relative sheet references ala sheet(-1)!B11 so I can copy a sheet. | Excel Discussion (Misc queries) |