Specify Row Location to Insert Copied Cells in Excel 2010

April 16, 2014 at 09:52:34
Specs: Windows 7
Hi,

I have recorded a macro to copy a range and insert it to a range below. However, the range where I am inserting [code below: Rows("132:132").Select] will constantly changed based on the information above. What I would like to do is have the range copy to the row highlighted prior to running the macro or have the user specify which row to insert the data on?

I then have certain information from the added data linked to another sheet that I would like copied to the last row on that sheet [code below: Rows("15:15").Select]?

Sub AddJob()
    
    ActiveWindow.SmallScroll Down:=-93
    Rows("36:43").Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=93
    Rows("132:132").Select
    Selection.Insert
    Sheets("Upload Template-NEW").Select
    Rows("13:13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Rows("15:15").Select
    Selection.Insert Shift:=xlDown
    Range("A15").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='Work Order'!R[118]C[1]"
    Range("O15").Select
    ActiveCell.FormulaR1C1 = "='Work Order'!R[118]C[-7]"
    Range("Q15").Select
    ActiveCell.FormulaR1C1 = "='Work Order'!R[119]C[-11]"
    Range("Q16").Select
    ActiveWindow.LargeScroll ToRight:=1
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 16
    Range("R15").Select
    ActiveCell.FormulaR1C1 = "='Work Order'!R[123]C[-14]:R[123]C[-12]"
    Range("R15").Select
    ActiveCell.FormulaR1C1 = "='Work Order'!R[123]C[-14]"
    Range("S15").Select
    ActiveCell.FormulaR1C1 = "='Work Order'!R[120]C[-13]"
    Range("W15").Select
    ActiveCell.FormulaR1C1 = "='Work Order'!R[122]C[-20]"
    Range("Z15").Select
    ActiveCell.FormulaR1C1 = "='Work Order'!R[121]C[-23]"
    Range("AK15").Select
    ActiveCell.FormulaR1C1 = "='Work Order'!R[119]C[-34]"
    Range("AL15").Select
    ActiveCell.FormulaR1C1 = "='Work Order'!R[122]C[-31]"
    Range("AM15").Select
    ActiveCell.FormulaR1C1 = "='Work Order'!R[121]C[-31]"
    Range("AN15").Select
    ActiveCell.FormulaR1C1 = "='Work Order'!R[121]C[-34]"
    Range("AO15").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("A15").Select
    Sheets("Work Order").Select
    Range("B133").Select
End Sub

Any help would be greatly apprecitated.

Thank you,
Sandi


See More: Specify Row Location to Insert Copied Cells in Excel 2010

Report •


#1
April 16, 2014 at 12:28:40
This first part this response is not an answer to your question(s)...I'll get to that later.

As you may be aware, recording a macro creates some very bloaded VBA code. It records all of the selection of cells, the scrolling around the worksheets, any mistakes you make along the way, etc.

For example, in most cases there is no need to select a cell within VBA to perform an action on it. You can typically perform the action directly on the cell.

For example, this...

Range("O15").Select
    ActiveCell.FormulaR1C1 = "='Work Order'!R[118]C[-7]"

...can be replaced with this...

Range("O15").FormulaR1C1 = "='Work Order'!R[118]C[-7]"

You should test this code in a backup copy of your workbook, but I believe that it does the same thing as your recorded code, just in a more efficient manner.

Sub AddJob()
'Copy Range on ActiveSheet
   ActiveSheet.Rows("36:43").Copy
      Rows("132:132").Insert
'Put Formulas in Sheet "Upload Template-NEW"
       With Sheets("Upload Template-NEW")
        .Rows("13:13").Copy
        .Rows("15:15").Insert Shift:=xlDown
           .Range("A15").FormulaR1C1 = "='Work Order'!R[118]C[1]"
           .Range("O15").FormulaR1C1 = "='Work Order'!R[118]C[-7]"
           .Range("Q15").FormulaR1C1 = "='Work Order'!R[119]C[-11]"
           .Range("R15").FormulaR1C1 = "='Work Order'!R[123]C[-14]"
           .Range("S15").FormulaR1C1 = "='Work Order'!R[120]C[-13]"
           .Range("W15").FormulaR1C1 = "='Work Order'!R[122]C[-20]"
           .Range("Z15").FormulaR1C1 = "='Work Order'!R[121]C[-23]"
           .Range("AK15").FormulaR1C1 = "='Work Order'!R[119]C[-34]"
           .Range("AL15").FormulaR1C1 = "='Work Order'!R[122]C[-31]"
           .Range("AM15").FormulaR1C1 = "='Work Order'!R[121]C[-31]"
           .Range("AN15").FormulaR1C1 = "='Work Order'!R[121]C[-34]"
           .Range("AO15").FormulaR1C1 = "0"
       End With
End Sub

You can also replace the R1C1 notation with the actual cell reference. That can make the code easier to read later on:

For example, this...

.Range("A15").FormulaR1C1 = "='Work Order'!R[118]C[1]"

...can be replaced with this:

.Range("A15").Formula = "='Work Order'!B133"

OK, as to your questions...


re: What I would like to do is have the range copy to the row highlighted prior to running the macro or have the user specify which row to insert the data on

First, I have to proceed my answer(s) with a warning:

Leaving things up to the user can be extremely dangerous. Since there is no "undo" for a macro, allowing the user to select the location for the paste could result in the data being inserted where it doesn't belong.

Are you sure that there is no way for the code to determine where the data should be be inserted? The code could search for a keyword or a value or determine the next empty row or something like that. Something that the user can't screw up.

That said, this code will insert the copied data at the row selected by the user.

 ActiveSheet.Rows("36:43").Copy
     Rows(Selection.Row).Insert

If more than one row is selected by the user, the insert will take place at the first row of the selected range.

If you want the user to specify a row, you can use an InputBox:

Sub AddJob()
'Get Row number from user
    myRow = Application.InputBox _
        ("Please Enter A Row Number For The Insertion", _
         "Row Number Input", Type:=1)
'Quit if Cancelled
    If myRow = False Then Exit Sub
'Copy Range on ActiveSheet
   ActiveSheet.Rows("36:43").Copy
   'Insert at Row entered by user
     Rows(myRow).Insert

'The rest of your code...

End Sub

re: I then have certain information from the added data linked to another sheet that I would like copied to the last row on that sheet [code below: Rows("15:15").Select]

I'm a little confused by this question. If I'm not mistaken, you want Rows("13:13") copied and pasted into the next empty row on Upload Template-NEW. Does that also mean that you want all of the formulas entered into the next empty row and not into A15, O15, etc.?

If that's the case, then I think you are going to have problems using the R1C1 notation, unless those formulas will also be offset by the RxCx numbers used in your current code. In other words, you may have to recode all of them using the hardcoded cell reference notation I mentioned earlier:

Range("A15").Formula = "='Work Order'!B133"

Please clarify this item so that I can offer a relevant solution.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
April 16, 2014 at 13:09:29
Ok I will try your suggestions above and yes I thought of the risks invovled in letting the user pick where to add as well. I was just stumped and wasn't sure how else to handle. I am thinking now (but not at all sure how to tackle) is if the code could go through and look for the last occurence of "Sub Total:" and add after that. I am just learning VBA (self taught) and it's awesome but a little overwhelming. I tried to add a screen capture but I am not sure if it worked?

As for the second part you are absolutely correct I first would like to copy Row ("13:13") in the 1st blank row and then update the values on that row accordingly (which happens to be Row("15:15")). Sorry for the confusion.

I just recently stumbled on this site and I must say I am impressed so far. You also answered a question for me last week as well!!

Thanks again,
Sandi

message edited by SandiS


Report •

#3
April 16, 2014 at 13:43:52
The standard code for determining the last row with data is as follows:

lastRw = Range("A" & Rows.Count).End(xlUp).Row

You just have to make sure that the Column you use has data in that last cell.

[A bit of VBA trivia...

Prior to Excel 2007, the standard code used to be:

lastRw = Range("A65535").End(xlUp).Row

That is because Excel 2003 and earlier only allowed 65,635 rows in a spreadsheet. When 2007's million plus rows were introduced, the standard became:

Range("A" & Rows.Count)

which is both backward and forward compatible with all versions of Excel.]

The next empty row is:

nxtRw = lastRw + 1

or

nxtRw = Range("A" & Rows.Count).End(xlUp).Row + 1

You can then use that variable in an instruction:

.Range("A" & nxtRw).Formula = "='Work Order'!B133"

or

.Cells(nxtRw, "A").Formula = "='Work Order'!B133"

Range("A15") is the same as Cells(15, "A") is the same as Cells(15, 1)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
April 16, 2014 at 13:52:19
Work Order Screen Shot
I don't believe I can do last row on the Work Order spreadsheet as there is data after where I need to add the copied section. I think I got the screen shot to work this time. As I was trying out your macro which worked I am wondering how it will work if I need to add another "job" section?

I can however do the last row on the Upload Template-New sheet. Just not quite sure how to make it happen.


Report •

#5
April 17, 2014 at 05:01:50
I need a better explanation of where you are inserting the copied section. Your original code doesn't say which sheet is Active when you copy/insert rows 36-43. Is that happening on the Work Order sheet?

As far as the Upload Template sheet, I'm not sure what you are having problems with. I explained how to set a variable equal to the next empty row and how to use that variable in an instruction to put a formula in a cell. What can't you "make happen"?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
April 17, 2014 at 06:05:13
I am adding to the Work Order sheet below the last job section the (rows 125-131 on screen shot are identical to rows 36-43 that I am copying). I copied seciton 36-43 because that range never changes. The ranges in between can changes as items are added per job.

The upload tab works great for one additional job, but I am not sure if it will work if I run the macro with the input message a second time as new jobs are added or if all the information will shift to the next line?

Also, when I add the input box I get a "Compile error: variable not defined" on the this row from your code above

    myRow = Application.InputBox _
        ("Please Enter A Row Number For The Insertion", _
         "Row Number Input", Type:=1)

Hope that helps.


Report •

#7
April 17, 2014 at 07:57:41
re: Compile error: Variable not defined

If you are using Option Explicit then you need to use a Dim statement to declare each variable. It's a good coding practice that I don't always follow when posting example code solutions. For complicated macros it ensures that each variable is known to VBA and does not get used incorrectly.

For example, let's say you are not using Option Explicit. If you don't declare your variables, you might make a typo like this and wonder why your code fails:

Sub Put5()
  myNum0 = 5
    Range("A1") = myNumO '<-- Uppercase O instead of zero
End Sub

You are trying to put the number 5 in A1, but you'll actually get an empty cell because myNumO (ending with an "O" and not a zero) has not been set to anything. If you use Option Explicit and declare myNum0, then the code will throw up a Compile Error when it sees myNumO since it was not declared. Declaring variables also allows you set the "type" to ensure that you use the variable correctly.

This code will not throw up an error:

Option Explicit
Sub Put5()
Dim myNum0 as Integer
  myNum0 = 5
    Range("A1") = myNum0
End Sub

You will note that I also declared the Type of variable that I want myNum0 to be - Integer.

This will put a 5 in A1:

Option Explicit
Sub Put5()
Dim myNum0 as Integer
  myNum0 = 5
    Range("A1") = myNum0
End Sub

However, this will also put a 5 in A1, because only integers can be stored in myNum0:

Option Explicit
Sub Put5()
Dim myNum0 as Integer
  myNum0 = 5.3
    Range("A1") = myNum0
End Sub

This will put 5.3 in A1 since myNum0 is declared as a Type that can hold numbers with a decimal portion:

Option Explicit
Sub Put5()
Dim myNum0 as Single
  myNum0 = 5.3
    Range("A1") = myNum0
End Sub

re: I am adding to the Work Order sheet below the last job section

If you use the following suggestion, you won’t need to get input from the user, assuming you always want to insert the range after the last job.

It appears that you have a “fixed string” after the last job. By “fixed string” I mean a text string that will be on every Work Order sheet.

A146: Special Shipping Instructions

It looks to me like you always want to insert the copied range 5 rows above that string. In the screenshot example that would be row 141. The Find feature in Excel can be used in VBA to find your fixed string. Once we know what row “Special Shipping Instructions” is in, we can subtract 5 from that to find the insertion point:

Sub AddJob()
'Find cell with "Special Shipping Instructions"
   With Sheets("Work Order").Cells
    Set s = .Find("Special Shipping Instructions", lookat:=xlWhole)
   End With
'Use Row from found cell to determine Insert location
    dstRw = s.Row - 5
'Copy Range and Insert
     Sheets("Work Order").Rows("36:43").Copy
        Rows(dstRw).Insert
'The rest of your code using the dstRw variable as 
'the Row number - Range("O" & dstRw), etc.
End Sub

Each time you run the macro, "Special Shipping Instructions" will move down 8 rows from its previous location and the code will simply subtract 5 rows from whatever row is happens to find the string in.

The upload tab works great for one additional job, but I am not sure if it will work if I run the macro with the input message a second time as new jobs are added or if all the information will shift to the next line?

Once again, I’m confused.

In Response #4 you said: “I can however do the last row on the Upload Template-New sheet”

However, you are now talking about using the input message for the Upload tab. Why would you use the Input method if the automatic last row method will work?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#8
April 17, 2014 at 08:35:15
re: I am adding to the Work Order sheet below the last job section

Ok trying your code and I get the same "Compile error: Variable not defined" so if I follow your code above I need to declare "s" and "dstRw" both as integers?

Also, with the last part of the code going to the Upload Tab not quite sure what you mean by using the dstRw on the rest of the code? Would I be changing the .Range("A15").Formula section.

Here is how I updated that part of the code using your advice above

'The rest of your code using the dstRw variable as
'the Row number - Range("O" & dstRw), etc.
      
'Put Formulas in Sheet "Upload Template-NEW"
       With Sheets("Upload Template-NEW")
        .Rows("13:13").Copy
        .Rows("15:15").Insert Shift:=xlDown
           .Range("A15").Formula = "='Work Order'B133" 'Job #
           .Range("O15").Formula = "='Work Order'H133" 'Plant
           .Range("Q15").Formula = "='Work Order'F134" 'Revenue Acct
           .Range("R15").Formula = "='Work Order'D138" 'Scope
           .Range("S15").Formula = "='Work Order'F135" 'GL Acct Prefix
           .Range("W15").Formula = "='Work Order'C137" 'Estimator
           .Range("Z15").Formula = "='Work Order'C136" 'Product Code
           .Range("AA15").Formula = "='Work Order'H134" 'ASME
           .Range("AK15").Formula = "='Work Order'C134" 'Tent Ship Date
           .Range("AL15").Formula = "='Work Order'G137" 'Elecrical Eng
           .Range("AM15").Formula = "='Work Order'H136" 'Prof Service Rate
           .Range("AN15").Formula = "='Work Order'F136" 'Job Category
       End With

In Response #4 you said: “I can however do the last row on the Upload Template-New sheet”

Yes that one I can use the last row on the Upload Tab.

I should have split the part about the input box up with the first half of the message.

I am learning so much thank you again for your time!!!


Report •

#9
April 17, 2014 at 10:55:54
re: Ok trying your code and I get the same "Compile error: Variable not defined" so if I follow your code above I need to declare "s" and "dstRw" both as integers?

First, I should mention that there is no requirement with Option Explicit that the variable Type be declared, just that the variable itself be declared.

You can certainly declare dstRw as an Integer since a Row number will always be an Integer.

As far as "s", that is actually going to be a Range, so you should use Dim s as Range.

Dim s as Range
Dim dstRw as Integer

You could get away with just using this and not declare a Type for either variable. (Yes, you can declare multiple variables on a single line)

Dim dstRw, s

(Of course, you could also turn off Option Explicit and not declare anything.)

re: Here is how I updated that part of the code using your advice above

I'm confused as to why your updated code is still using a hard coded reference to Row 15. I thought you were trying to put the formulas in the next empty row on the Upload sheet.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
April 18, 2014 at 10:14:25
re: Ok trying your code and I get the same "Compile error: Variable not defined" so if I follow your code above I need to declare "s" and "dstRw" both as integers?

Ok I have added the declarations now I get a "Run-time error '91': Object variable or with block variable not set" and it takes me to the following code?

dstRw = s.Row - 5

re: Here is how I updated that part of the code using your advice above

I was focused on the top part of the code and haven't gotten to that part yet. I will work on that next.


Report •

#11
April 18, 2014 at 16:56:09
Typically the error you are getting is caused when the string that ".Find" is looking for isn't found.

Since we are trying to return the Row that the search string is found in via s.Row, if the search string isn't found then s will be set to Nothing and you'll get that error. I did not include any error checking for the ".Find" instruction because I assumed that "Special Shipping Instructions" will always be found on the Work Order sheet as shown in your example.

Is that not the case?

If "Special Shipping Instructions" is always on the Work Order sheet, then it's possible that the cell containing that string has leading or trailing spaces. I used the xlWhole option for ".Find" which will search for the string in quotes and only exactly the string in quotes. If there are leading or trailing spaces, then change xlWhole to xlPart and you should be OK, as long as that string isn't used anywhere else on the sheet.

Perhaps you should read this How To which will explain how to use F8 to Single Step and how to set Watches so that you can troubleshoot the code. It's a great way to learn VBA. I wrote the tutorial because that is basically how I learned the write VBA code. By single stepping through other people's code, I found I could understand what they were doing better than just reading the code itself.

http://www.computing.net/howtos/sho...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#12
April 21, 2014 at 07:32:43
I have updated the code to include the ":" at the end of Special Shipping Instructions and that part is working wonderfully.

I am trying to add the last row function to Upload part and I am stuck. Where and how do I add it? I have the following but it is adding to the Work Order tab instead?

Option Explicit

Sub AddJob()

Dim s As Range
Dim dstRw As Integer
Dim lastRw As Long
lastRw = Range("A" & Rows.Count).End(xlUp).Row

'Find cell with "Special Shipping Instructions"
   With Sheets("Work Order").Cells
    Set s = .Find("Special Shipping Instructions:", lookat:=xlWhole)
   End With
'Use Row from found cell to determine Insert location
    dstRw = s.Row - 5
'Copy Range and Insert
     Sheets("Work Order").Rows("36:43").Copy
        Rows(dstRw).Insert
        
'The rest of your code using the dstRw variable as
'the Row number - Range("O" & dstRw), etc.

'Put Formulas in Sheet "Upload Template-NEW"
       With Sheets("Upload Template-NEW")
        .Rows("13:13").Copy Range("A" & lastRw)
           .Range("A15").Formula = "='Work Order'B133" 'Job #
           .Range("O15").Formula = "='Work Order'H133" 'Plant
           .Range("Q15").Formula = "='Work Order'F134" 'Revenue Acct
           .Range("R15").Formula = "='Work Order'D138" 'Scope
           .Range("S15").Formula = "='Work Order'F135" 'GL Acct Prefix
           .Range("W15").Formula = "='Work Order'C137" 'Estimator
           .Range("Z15").Formula = "='Work Order'C136" 'Product Code
           .Range("AA15").Formula = "='Work Order'H134" 'ASME
           .Range("AK15").Formula = "='Work Order'C134" 'Tent Ship Date
           .Range("AL15").Formula = "='Work Order'G137" 'Elecrical Eng
           .Range("AM15").Formula = "='Work Order'H136" 'Prof Service Rate
           .Range("AN15").Formula = "='Work Order'F136" 'Job Category
       End With
End Sub

I am also not sure how to update the Range values to go in the last row?

Thanks for the "How to" tip that does help explain and walk through the macro alot!

Sandi


Report •

#13
April 21, 2014 at 07:54:46
Another piece I would like to add is to sum each value after "SubTotal:" and put that value in the cell after "SUBTOTAL". Is that possible as well? You can see the area I am refrerring to in the screen shot above.

Report •

#14
April 21, 2014 at 09:40:42
re: I am trying to add the last row function to Upload part and I am stuck. Where and how do I add it? I have the following but it is adding to the Work Order tab instead

I am assuming that this macro is stored in a Standard module, e.g. Module1. Any instructions in a macro stored in a Standard module that reference a Range, e.g. Range("A1"), will be looking at the ActiveSheet only. If you want to reference a range in a specific sheet, you need to include the Sheet Name. It never hurts to explicit reference a sheet name since that will make the code more portable. Referencing a specific sheet can be done in a couple of different ways:

You can add the sheet name directly to each instruction:

lastRw = Sheets("Upload Template-NEW").Range("A" & Rows.Count).End(xlUp).Row

or, if you will be referencing the sheet multiple times it's sometimes easier to use With:

   With Sheets("Upload Template-NEW")
     lastRw =  .Range("A" & Rows.Count).End(xlUp).Row
               .Range("A1") = "Hello"
               .Range("B1").Formula = "=SUM(D1:D5)"
               'etc.
   End With

VBA knows that the dot (.) before the Range reference (or any instruction) will refer to the object referenced in the With instructions. In other words, “Do .this with the Sheet named above.” You just need to make sure you start the section with With and end with End With.

You can also use With to reference objects within a Sheet, such as a specific cell:

 With Sheets("Upload Template-NEW").Range("B1")
   .Font.Bold = True
   .Interior.ColorIndex = 6
   .Formula = "=SUM(D1:D5)"
 End With

re: I am also not sure how to update the Range values to go in the last row?

I believe I explained that in Response #3. Replace the hardcoded Row number with the variable that contains the number of the next empty Row, using the syntax shown in that response.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#15
April 21, 2014 at 11:25:59
re: "Another piece I would like to add is to sum each value after "SubTotal:" "

I added a section to the "AddJob" macro to update the SUBTOTAL: formula after the copied Range has been inserted.

This would be a perfect time to use the Watch feature of the VBA editor to Watch the variables as they get built, specifically sumRng_tmp and sumRng.

What this code does is search Column F for the string "Sub Total:". Each time it finds the string, it uses the Row number to append a cell reference to the string that will eventually be used as the Range in the SUM formula. It also adds a comma reference.

e.g. Each time the "Sub Total:" string is found in Column F, sumRng_tmp will look something like this:

G43,
G43,G51,
G43,G51,G59,
G43,G51,G59,G67,

Once all of the Sub Total: cells have been found, the code strips off the last comma and uses sumRng for the formula.

Sheets("Work Order").Range("H" & s.Row - 4).Formula = "=SUM(" & sumRng & ")"

which Excel will see as:

=SUM(G43,G51,G59,G67)

Sub AddJob()
'*** Code to Insert Copied Rows
'Find cell with "Special Shipping Instructions:"
   With Sheets("Work Order").Cells
    Set s = .Find("Special Shipping Instructions:", lookat:=xlWhole)
   End With
'Use Row from found cell to determine Insert location
    dstRw = s.Row - 5
'Copy Range and Insert
     Sheets("Work Order").Rows("36:43").Copy
        Rows(dstRw).Insert
''***Code to Update Subtotal Formula
'Search Column F for "Sub Total:" string
  With Sheets("Work Order").Range("F1:F" & s.Row)
    Set st = .Find("Sub Total:", lookat:=xlWhole)
'Loop though Column F, finding each occurance of "Sub Total:" string
    If Not st Is Nothing Then
        firstAddress = st.Address
        Do
'Build string to use as Range for formula
            sumRng_tmp = sumRng_tmp & "G" & st.Row & ","
            Set st = .FindNext(st)
        Loop While Not st Is Nothing And st.Address <> firstAddress
    End If
 End With
'Strip off last comma
  sumRng = Left(sumRng_tmp, Len(sumRng_tmp) - 1)
'Place formula in SubTotal: cell in Column H
    Sheets("Work Order").Range("H" & s.Row - 4).Formula = "=SUM(" & sumRng & ")"
'The rest of your code
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#16
April 22, 2014 at 08:54:37
I am getting the following error" Run-Time error 1004: Application-defined or object-defined error" on my formula row. I have the variable declared as follows:
Dim lastRw As Long
Dim nxtRw As Long

'Put Formulas in Sheet "Upload Template-NEW"
       With Sheets("Upload Template-NEW")
        lastRw = Sheets("Upload Template-New").Range("A" & Rows.Count).End(xlUp).Row + 1
        nxtRw = Sheets("Upload Template-New").Range("A" & Rows.Count).End(xlUp).Row
            .Rows("13:13").Copy Sheets("Upload Template-New").Range("A" & lastRw)
<b>            Sheets("Upload Template-New").Range("A" & nxtRw).Formula = "='Work Order'B133" 'Job #</b>    
'           .Range("O15").Formula = "='Work Order'H133" 'Plant
    '           .Range("Q15").Formula = "='Work Order'F134" 'Revenue Acct
    '           .Range("R15").Formula = "='Work Order'D138" 'Scope
    '           .Range("S15").Formula = "='Work Order'F135" 'GL Acct Prefix
    '           .Range("W15").Formula = "='Work Order'C137" 'Estimator
    '           .Range("Z15").Formula = "='Work Order'C136" 'Product Code
    '           .Range("AA15").Formula = "='Work Order'H134" 'ASME
    '           .Range("AK15").Formula = "='Work Order'C134" 'Tent Ship Date
    '           .Range("AL15").Formula = "='Work Order'G137" 'Elecrical Eng
    '           .Range("AM15").Formula = "='Work Order'H136" 'Prof Service Rate
    '           .Range("AN15").Formula = "='Work Order'F136" 'Job Category
       End With
End Sub

Do you have any suggestions or see something I may have missed?


Report •

#17
April 22, 2014 at 16:00:23
When you enter an invalid formula in the Excel formula bar, you'll get some type of error about the formula from the Excel application itself.

When you try to enter an invalid formula via VBA, the Excel application throws up the error but VBA can't display the Excel based error, so it throws up an Application Defined error telling you that the Excel application itself has a problem with what you are trying to do.

Look at the formula that you are trying to put in the cell. Try to enter it manually and see if it works. Hint: It won't.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#18
April 23, 2014 at 07:09:49
Aha...got it, I was missing the "!". Ok I have that fixed and all works wonderfully BUT now if I add a second job I have the cells from the Work Order hard coded so it just copies the data from the first line added. Is there a way to fix that?

'Put Formulas in Sheet "Upload Template-NEW"
       With Sheets("Upload Template-NEW")
        lastRw = Sheets("Upload Template-NEW").Range("A" & Rows.Count).End(xlUp).Row + 1
        nxtRw = Sheets("Upload Template-NEW").Range("A" & Rows.Count).End(xlUp).Row + 1
            .Rows("13:13").Copy Sheets("Upload Template-NEW").Range("A" & lastRw)
               .Range("A" & nxtRw).Formula = "='Work Order'!B133" 'Job #
               .Range("O" & nxtRw).Formula = "='Work Order'!H133" 'Plant
               .Range("Q" & nxtRw).Formula = "='Work Order'!F134" 'Revenue Acct
               .Range("R" & nxtRw).Formula = "='Work Order'!D138" 'Scope
               .Range("S" & nxtRw).Formula = "='Work Order'!F135" 'GL Acct Prefix
               .Range("W" & nxtRw).Formula = "='Work Order'!C137" 'Estimator
               .Range("Z" & nxtRw).Formula = "='Work Order'!C136" 'Product Code
               .Range("AA" & nxtRw).Formula = "=IF('Work Order'!H134=""Yes"",""Y"",""N"")" 'ASME
               .Range("AK" & nxtRw).Formula = "='Work Order'!C134" 'Tent Ship Date
               .Range("AL" & nxtRw).Formula = "='Work Order'!G137" 'Elecrical Eng
               .Range("AM" & nxtRw).Formula = "='Work Order'!H136" 'Prof Service Rate
               .Range("AN" & nxtRw).Formula = "='Work Order'!F136" 'Job Category
       End With
End Sub

I am certainly learning a lot but wow how intense this can be!!

Thanks AGAIN for all of your assistance!


Report •

#19
April 23, 2014 at 11:59:25
I'm not quite sure what you are asking.

If instead of using formulas with hardcoded cell references you want to use "variable" cell references, i.e. a variable Row, then use the techniques outlined in this thread to set a variable equal to the required Row number(s).

You can use .Find to determine the Row number of a given value like we did with the "Sub Total:" and "Shipping..." strings; you can use the "lastRw" technique to find the last Row in a column, etc. The main idea is to use what you know, or should I say what VBA can tell you, and then adjust from there.

Once you know where a sepcific string is stored or the last Row of a Column or the last Column in a Row, you can use that info to set the variable to create a dynamic formula:

In other words, instead of this:

.Range("A" & nxtRw).Formula = "='Work Order'!B133"  'Job #

You would use something like this:

.Range("A" & nxtRw).Formula = "='Work Order'!B" & yourRowVariable  'Job #

You know how to use the nxtRw variable to determine where the Formula should be place, now figure out how to set the yourRowVariable as your Row reference for the formula.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#20
April 23, 2014 at 12:16:15
Upload-NEW Tab
After thinking some more on the upload tab and finalizing the sub total macro above (works great by the way), I am wondering if I could leave the first job along with the 0-300 one as well and have each job added as they are filled in. We may have only (1) job on a Work Order or we have had has many as (75) all with different information in the cells I have formulas for above. I have attached a screen shot hopefully that may help. The grey areas will be the same on all jobs and the cyan areas can change. I have to put it in this format to save to a .csv so it can be uploaded into our accounting software.

I think I understand your explanation above as well. So if this option can not be done, I will give that route a go.


Report •

#21
April 23, 2014 at 12:52:27
Unless I am missing something, you aleady know everything you need to know in order to build the individual formulas.

Based on the screen shot you used in Response #4, it appears that had the Copy/Insert portion of the macro been run, it would have inserted the last job at Row 133. That means that the dstRw variable would have been set to 133.

It seems to me that this:

.Range("A" & nxtRw).Formula = "='Work Order'!B" & dstRw  'Job #

would resolve to:

.Range("A" & nxtRw).Formula = "='Work Order'!B133"   'Job #

and this:

.Range("Q" & nxtRw).Formula = "='Work Order'!F" & dstRw + 1   'Revenue Acct

would resolve to this:

.Range("Q" & nxtRw).Formula = "='Work Order'!F134"   'Revenue Acct

etc.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •


Ask Question