Results 1 to 2 of 2

Thread: Update One Spread Sheet accodring to values of another with Macro

  1. #1

    Update One Spread Sheet accodring to values of another with Macro



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!
    Attached Files Attached Files

  2. #2
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    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.

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •