One formula activated by where you are

[daniels012]

I copy and paste data from Estimating(sheet) to WhatToOrder(sheet).
I set up a button to do this action as I wish. I have 7 buttons. Do I need to re-write the code 6 more times or can I execute the code based upon where I am on the sheet?

Sub JobData1()
'
' JobData1 Macro
' Enter Data from Estimating to the job one column
'

'
    Application.ScreenUpdating = False
    Range("E2").Select
    Sheets("ESTIMATING").Select
    Range("B3").Select
    Selection.Copy
    Sheets("What to Order").Select
    Range("E2").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    Sheets("ESTIMATING").Select
    Range("B8").Select
    Selection.Copy
    Sheets("What to Order").Select
    Range("E3").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Sheets("ESTIMATING").Select
    Range("H3:H27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("What to Order").Select
    Range("E4").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "0.00"
    Range("E2").Select
    Application.ScreenUpdating = True
End Sub

Works great! I know I can copy and paste each sets of code, change all the numbers but it would be a lot easier to have it copy from Estimating and paste on current cell, then down 1 place, then down 1 more cell, etc. etc.

 

[Travis]

Like this?

Sub JobData1()

' JobData1 Macro
' Enter Data from Estimating to the job one column

    Application.ScreenUpdating = False
    Sheets("ESTIMATING").Activate
    Range("B3").Copy
    Sheets("What to Order").Activate
    ActiveCell.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveCell.Offset(1, 0).Select
    Sheets("ESTIMATING").Activate
    Range("B8").Copy
    Sheets("What to Order").Activate
    ActiveCell.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveCell.Offset(1, 0).Select
    Sheets("ESTIMATING").Activate
    Range("H3:H27").Copy
    Sheets("What to Order").Activate
    With ActiveCell
        .PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        .NumberFormat = "0.00"
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Or does the range in "Estimating" need to be dynamic also?

 

[daniels012]

THAT IS PERFECT!!!

Thank You, That is so much easier. Now when I add a row of new data it will be much easier also. I just have to add a set of code and offset(1,0)

Thanks again,
Michael

 

[daniels012]

By the way,
Is there an easier way to combine all the cells to copy. Then paste all in a row on the next sheet. It seems there would be a lot less code written that way?


New to this coding stuff... But I LOVE IT!

Michael ray:

 

[daniels012]

By combine I mean list them in code or something like that??

Michael

 

[Travis]

if they are all in the same column /row you can do it, but I you are copying stuff that is all over the place and trying to copy to a single column I think you will end up with more code. Or at least code that above my skill level.

you can combine consistant rows/columns (like B1 nd B8) like this, but I dont know how to also include your H range in there too and be able to paste to a single column.

Sub JobData1()

'JobData1 Macro
' Enter Data from Estimating to the job one column

    Application.ScreenUpdating = False
    Sheets("ESTIMATING").Activate
    Range("B3,B8").Copy
    Sheets("What to Order").Activate
    ActiveCell.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveCell.Offset(1, 0).Select
    Sheets("ESTIMATING").Activate
    Range("H3:H27").Copy
    Sheets("What to Order").Activate
    With ActiveCell
        .PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        .NumberFormat = "0.00"
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub