| Hello again, Ive come to a point in my current excel project where ive realized that some of the formulas im useing and the ways im useing them is slowing my spreadsheet down and allowing for too may user mistakes so once again i have come to you guys again for more help Heres what i have, Theres two separate spreadsheets and a pivot table, the pivot table organizes he data from spreadsheet 2 and spreadsheet 2 compiles and calculates the data from spreadsheet one. Now, the data on spreadsheet one is by way of a chart, i have premade this chart to be a little over 500 rows long as we will never use more rows than this and that is all fine and dandy. But heres the thing Spreadsheet 2 is also 500 rows long because each row (with calculations) in spreadsheet 2 corresponds to 1 row in spreadsheet 1. This makes things difficult for users because users cannot add new rows or anything to spreadsheet 1 cause if they do calculations in spreadsheet 2 wont be there for the new row in spreadsheet one? Sorry if this is confusing, if youthink it is just open up the spreadsheet ive attached and youll see exactly what im talking about. Heres what i want, i wantto be able to use a macro or something so that when i hit the macro, it scans for all the full rows in spreadsheet one and for each full row in spreadsheet one it makes a calculation row in spreadsheet 2. If you have questions please ask! |
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Update One Spread Sheet accodring to values of another with Macro
- Thread starter TomToms
- Start date
Simi
New member
- Joined
- Feb 10, 2012
- Messages
- 190
- Reaction score
- 0
- Points
- 0
- Location
- Utah, USA
- Excel Version(s)
- Version 2002 Build 12527.20194
I wrote this code before reading the rest of your code so you could condense this even further if you wish.
This code will copy all data from the Cut List page to the FormIV page, and should be run after all data is input on the CutList.
It simply starts on row5 of the cut list and copies data to the formIV page if there are values in A(row#) of the cutlist.
You could also add this kind of code to the cmdAdd2 button on your form to add material. that way every time you hit the add button, it would simply update that single line on the FormIV page.
anyway, I hope this helps.
This code will copy all data from the Cut List page to the FormIV page, and should be run after all data is input on the CutList.
It simply starts on row5 of the cut list and copies data to the formIV page if there are values in A(row#) of the cutlist.
You could also add this kind of code to the cmdAdd2 button on your form to add material. that way every time you hit the add button, it would simply update that single line on the FormIV page.
anyway, I hope this helps.
Code:
Sub copy_to_FormIV()
'declare variable
Dim rowCounter As Integer
rowCounter = 5 'start on row 5, the first 4 are header rows.
Do While IsEmpty(Worksheets("CutList").Range("A" & rowCounter)) = False
Worksheets("FormIV").Range("A" & rowCounter) = Worksheets("CutList").Range("C" & rowCounter) * Worksheets("CutList").Range("H" & rowCounter)
Worksheets("FormIV").Range("B" & rowCounter) = Worksheets("CutList").Range("I" & rowCounter)
If Trim(UCase(Worksheets("FormIV").Range("B" & rowCounter))) = "MM" Then
Worksheets("FormIV").Range("C" & rowCounter) = "TRUE"
Worksheets("FormIV").Range("D" & rowCounter) = Worksheets("FormIV").Range("A" & rowCounter) / 304.8
Else
Worksheets("FormIV").Range("C" & rowCounter) = "FALSE"
Worksheets("FormIV").Range("D" & rowCounter) = Worksheets("FormIV").Range("A" & rowCounter) / 12
End If
Worksheets("FormIV").Range("E" & rowCounter) = Replace(Worksheets("CutList").Range("D" & rowCounter), " ", "")
'increment the row counter and continue.
rowCounter = rowCounter + 1
Loop
End Sub