VBA referring to a different sheet, automatically fill cell based on content of other cell

There are 3 tabs that are used in the worksheet: For Sales, itmof, and itbof

I need a VBA that will be embedded on the itmof tab but will effect the itbof tab.
On the itbof tab I need column C to be automatically filled with a letter A only if there is any kind of value (letters or numbers) in column A, starting with row 2 and below. I also need need column T to be automatically filled with the letter N only if there is any kind of value (letters or numbers) in column A, starting with row 2 and below.
Last thing, I need column I to be automatically filled with (the value in column L multiplied by 1.08) only if there is a value in column L, starting at row 2 and below.

So there's no confusion, this VBA will be going on my itmof tab because I need it linked to a button on that sheet, but all these changes should only effect the itbof tab.

Slowly but surely, learning VBA!

Thanks

Could you post a copy of your file? It would make it easier to help.

Here is a copy of the file.

Thanks

Will the data in the itbof tab be entered manually?

No sir, it will be brought over from the For Sales tab when you hit the Copy Data button on the itmof tab.

Place this code in the worksheet module for Sheet("itbof")... not in Sheet("itmof"). When you click the 'Copy Data' button and then select Sheet("itbof"), your columns should automatically update.

Private Sub Worksheet_Activate()
    Dim bottomA As Integer
    bottomA = Range("A" & Rows.Count).End(xlUp).Row
    Dim bottomL As Integer
    bottomL = Range("L" & Rows.Count).End(xlUp).Row
    Dim rng1 As Range
    Dim rng2 As Range
    For Each rng1 In Sheets("itbof").Range("A2:A" & bottomA)
        If rng1 <> "" Then
            Cells(rng1.Row, 3).Value = "A"
            Cells(rng1.Row, 20).Value = "N"
        End If
    Next rng1
    
    For Each rng2 In Sheets("itbof").Range("L2:L" & bottomL)
        If rng2 <> "" Then
            Cells(rng2.Row, 9).Value = Cells(rng2.Row, 12).Value * 1.08
        End If
    Next rng2
End Sub

OK, I'll rephrase this, I did some more testing, and the only time it errors out is if I switch over to the itbof tab before I hit the copy data button, other than that it works great. To make it "dummy proof" is there any way to just make it run after I hit the itbof tab? This way it doesn't matter if we accidentally switch to that tab first before the copy data button is pressed...

Thanks, I appreciate everything you've done so far!

I would like to make a suggestion. First, remove the code from the worksheet module and put it into a regular module and change the name of the macro so that it's not a Worksheet_Activate event. Then you can run the macro by assigning it to a button on either worksheet or you can call the macro from your "Copy Data" macro. If you call it from the "Copy Data" macro, it will complete the "itbof" sheet automatically when it completes the "itmof" sheet. If you want to keep the two tasks separate, then assign it to a button. I hope that helps.

OK, this is progress... It only works when I press the copy data button first, and then paste this code in the itbof tab. If I leave this code pasted in the itbof tab it will always give me an error before I press the copy data button. Is there anyway to get this to only run after I press the copy data button?

Thanks

I tried both of those suggestions and neither one worked for me. I think it's having a problem calling the sub if it's in a different sheet other than my itmof tab and vice versa, if the sub is in my itmof tab and I assign it to copy data macro, then it errors out a certain line in the sub...

Thanks

Try this code. It should work regardless of where you call it from.

Sub Test()
    Dim bottomA As Integer
    bottomA = Sheets("itbof").Range("A" & Rows.Count).End(xlUp).Row
    Dim bottomL As Integer
    bottomL = Sheets("itbof").Range("L" & Rows.Count).End(xlUp).Row
    Dim rng1 As Range
    Dim rng2 As Range
    For Each rng1 In Sheets("itbof").Range("A2:A" & bottomA)
        If rng1 <> "" Then
            Cells(rng1.Row, 3).Value = "A"
            Cells(rng1.Row, 20).Value = "N"
        End If
    Next rng1
    
    For Each rng2 In Sheets("itbof").Range("L2:L" & bottomL)
        If rng2 <> "" Then
            Sheets("itbof").Cells(rng2.Row, 9).Value = Sheets("itbof").Cells(rng2.Row, 12).Value * 1.08
        End If
    Next rng2
End Sub

Fantastic! That worked beautifully. I hate to be a pest but in this line:

Sub Test()
    Dim bottomA As Integer
    bottomA = Sheets("itbof").Range("A" & Rows.Count).End(xlUp).Row
    Dim bottomL As Integer
    bottomL = Sheets("itbof").Range("L" & Rows.Count).End(xlUp).Row
    Dim rng1 As Range
    Dim rng2 As Range
    For Each rng1 In Sheets("itbof").Range("A2:A" & bottomA)
        If rng1 <> "" Then
            Cells(rng1.Row, 3).Value = "A"
            Cells(rng1.Row, 20).Value = "N"
        End If
    Next rng1
    
    For Each rng2 In Sheets("itbof").Range("L2:L" & bottomL)
        If rng2 <> "" Then
            Sheets("itbof").Cells(rng2.Row, 9).Value = Sheets("itbof").Cells(rng2.Row, 12).Value * 1.08
        End If
    Next rng2
End Sub

Is there any way you could write something in there to Round to 5 decimal places if the number goes to 6 decimal places or more?

Thanks

I'm glad that it worked out for you! I think that you can more easily do the rounding off by formating the column as a number with 5 decimal places instead of using VBA.

I did try that and it doesn't work for what we need, it will still show up as 6 or however many decimal places it is if you click on the cell. It will then error out when we try to upload the spreadsheet to our database because of the extra decimal places. That's kind of why we need the VBA to take care of that before it's entered in the cell...

Try adding these lines:

Range("I2:I" & bottomA).Select
Selection.NumberFormat = "0.00000

"
below this line:

Next rng1

OK, I tried this and it did not work, every time I ran it, excel tried to use the I column in itmof and not itbof. It still didn't do anything in the I column of itmof because there is no data there but every time I ran it the entire I column in itmof was highlighted. I think if you can get it to go to the itbof tab, then column I will successfully be rounded to 5 decimal places!

OK. Try replacing this line:

Range("I2:I" & bottomA).Select

with this line:

Sheets("itbof").Range("I2:I" & bottomA).Select

Almost, I replaced that line and it is pointing at the I column in the itbof tab now but it kicked back a Microsoft Visual Basic for Application.... error window with a 400 in the middle of the error window and nothing else...

My apologies. I don't know what's going on because in my tests using the file you sent me it works properly. I can't reproduce any error.

Shoot! lol

Did you assign this sub to the Copy Data macro on itmof and it still worked for you? Can you send me the code that is working for you so I can test with it and see if I get the same results?

Thanks

Here is the code. Just put it into a regular module and run it form there.

Sub Test()
    Dim bottomA As Integer
    bottomA = Sheets("itbof").Range("A" & Rows.Count).End(xlUp).Row
    Dim bottomL As Integer
    bottomL = Sheets("itbof").Range("L" & Rows.Count).End(xlUp).Row
    Dim rng1 As Range
    Dim rng2 As Range
    For Each rng1 In Sheets("itbof").Range("A2:A" & bottomA)
        If rng1 <> "" Then
            Cells(rng1.Row, 3).Value = "A"
            Cells(rng1.Row, 20).Value = "N"
        End If
    Next rng1
    
    Sheets("itbof").Range("I2:I" & bottomA).Select
    Selection.NumberFormat = "0.00000"
    
    For Each rng2 In Sheets("itbof").Range("L2:L" & bottomL)
        If rng2 <> "" Then
            Sheets("itbof").Cells(rng2.Row, 9).Value = Sheets("itbof").Cells(rng2.Row, 12).Value * 1.08
        End If
    Next rng2
End Sub

OK, thanks for supplying this code I thought's what it was. I tried it again and got the same 400 error pop up. Everything else works but column I on itbof doesn't get populated anymore because of the error. I have attached a copy of my workbook with the updated code in it that I'm using so you can open it and see what I'm talking about.

Thanks!

The problem was that you had the macros in the worksheet module instead of a regular module. I moved the macros into Module3 and changed the 'Copy Data' button link to the new location of the macro. Your 'copydata' macro now runs and then calls the 'FillColumnsitbof' so both operations are run one after the other. If you want to run them separately, delete the line:

Call FillColumnsitbof

in the 'copydata' macro and assign the 'FillColumnsitbof' macro to a separate button. The attached file has all the changes. Hopefully, everything is OK now.

I don't think your attached file made it's way over, I don't see it anywhere. I'm anxious to test it though and see what your talking about in the VBA! It's easier for to visual see it and learn it. If you could please send the updated file again that would be great!

Thanks!

When I open the thread, the file is there. Have another look. If you still can't find it, let me know and I'll re-send it.

Worked like a charm. I love you!

Thanks again for all your help Mumps1!

Worked like a charm. I love you!

Thanks again for all your help Mumps1!

It was my pleasure. I'm glad we finally got it to work for you!