Code has stopped working

J

jmk15315

Board Regular
Joined
Nov 7, 2021
Messages
73
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
I recently was given the task of maintaining (and correcting) a spreadsheet used by multiple individuals in the company.
I was hoping someone could help this rookie understand the commands.

What this is intended to do, is import data from a "Source File" into specific tabs on the worksheet containing this code.
What I don't understand is where the code spells out what cells to copy and where to paste them to.

Been trying to get my employer to send me for "Offical" training, but until then hoping someone here can educate me.

See below:

VBA Code: 
Public Sub UpdateSelections()
  Dim tmpRow As Integer
  Dim tmpLastRow As Integer
  Dim tmpIntVar As Integer, tmpLoop2 As Integer

    ActiveWorkbook.Sheets("UPDATE").Unprotect (CAT_PROTECT)
     
      'find the last row used
      tmpRow = ActiveWorkbook.Sheets("UPDATE").UsedRange.Rows.Count
      tmpRow = tmpRow + 1

'Platform Items
       tmpLastRow = ActiveWorkbook.Sheets("Platform").Range("Total_Platform").Row
      
       For tmpIntVar = 3 To tmpLastRow
         If ActiveWorkbook.Sheets("Platform").Cells(tmpIntVar, 2) <> "" Then
            'copy most of the row
            For tmpLoop2 = 1 To 11
               ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, tmpLoop2) = _
               ActiveWorkbook.Sheets("Platform").Cells(tmpIntVar, tmpLoop2)
            Next tmpLoop2
'            'replace the quantity with the formula
'            ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, 2).Formula = _
'            ActiveWorkbook.Sheets("Platform").Cells(tmpIntVar, 2).Formula
'removed the formulas - they were creating circular references
            'added information - used later for comparison
            ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, 12) = "Platform"
            ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, 13) = ActiveWorkbook.Sheets("Summary").Cells(4, 2)
            tmpRow = tmpRow + 1
         End If
       Next tmpIntVar

'Robot Items
' this sheet contains a formula in some of the quantity cells
       tmpLastRow = ActiveWorkbook.Sheets("Robot").Range("Total_Robot").Row
      
       For tmpIntVar = 3 To tmpLastRow
         If ActiveWorkbook.Sheets("Robot").Cells(tmpIntVar, 2) <> "" And _
            ActiveWorkbook.Sheets("Robot").Cells(tmpIntVar, 2) <> "0" Then
            'copy most of the row
            For tmpLoop2 = 1 To 11
               ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, tmpLoop2) = _
               ActiveWorkbook.Sheets("Robot").Cells(tmpIntVar, tmpLoop2)
            Next tmpLoop2
'            'replace the quantity with the formula
'            ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, 2).Formula = _
'            ActiveWorkbook.Sheets("Robot").Cells(tmpIntVar, 2).Formula
'removed the formulas - they were creating circular references
            'added information - used later for comparison
            ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, 12) = "Robot"
            ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, 13) = ActiveWorkbook.Sheets("Summary").Cells(4, 2)
            tmpRow = tmpRow + 1
         End If
       Next tmpIntVar

'process & torch
' this sheet contains a formula in some of the quantity cells
       tmpLastRow = ActiveWorkbook.Sheets("Process & Torch").Range("Total_process").Row
      
       For tmpIntVar = 3 To tmpLastRow
         If ActiveWorkbook.Sheets("Process & Torch").Cells(tmpIntVar, 2) <> "" And _
            ActiveWorkbook.Sheets("Process & Torch").Cells(tmpIntVar, 2) <> "0" Then
            'copy most of the row
            For tmpLoop2 = 1 To 11
               ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, tmpLoop2) = _
               ActiveWorkbook.Sheets("Process & Torch").Cells(tmpIntVar, tmpLoop2)
            Next tmpLoop2
'            'replace the quantity with the formula
'            ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, 2).Formula = _
'            ActiveWorkbook.Sheets("Process & Torch").Cells(tmpIntVar, 2).Formula
'removed the formulas - they were creating circular references
            'added information - used later for comparison
            ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, 12) = "Process & Torch"
            ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, 13) = ActiveWorkbook.Sheets("Summary").Cells(4, 2)
            tmpRow = tmpRow + 1
         End If
       Next tmpIntVar

'Controls
' this sheet contains a formula in some of the quantity cells
       tmpLastRow = ActiveWorkbook.Sheets("Controls").Range("Total_controls").Row
      
       For tmpIntVar = 3 To tmpLastRow
         If ActiveWorkbook.Sheets("Controls").Cells(tmpIntVar, 2) <> "" And _
            ActiveWorkbook.Sheets("Controls").Cells(tmpIntVar, 2) <> "0" Then
            'copy most of the row
            For tmpLoop2 = 1 To 11
               ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, tmpLoop2) = _
               ActiveWorkbook.Sheets("Controls").Cells(tmpIntVar, tmpLoop2)
            Next tmpLoop2
'            'replace the quantity with the formula
'            ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, 2).Formula = _
'            ActiveWorkbook.Sheets("Controls").Cells(tmpIntVar, 2).Formula
'removed the formulas - they were creating circular references
            'added information - used later for comparison
            ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, 12) = "Controls"
            ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, 13) = ActiveWorkbook.Sheets("Summary").Cells(4, 2)
            tmpRow = tmpRow + 1
         End If
       Next tmpIntVar

'material handling items
       tmpLastRow = ActiveWorkbook.Sheets("Mat. Flow").Range("Total_Material_Flow").Row
      
       For tmpIntVar = 3 To tmpLastRow
         If ActiveWorkbook.Sheets("Mat. Flow").Cells(tmpIntVar, 2) <> "" Then
            'copy most of the row
            For tmpLoop2 = 1 To 11
               ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, tmpLoop2) = _
               ActiveWorkbook.Sheets("Mat. Flow").Cells(tmpIntVar, tmpLoop2)
            Next tmpLoop2
'            'replace the quantity with the formula
'            ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, 2).Formula = _
'            ActiveWorkbook.Sheets("Mat. Flow").Cells(tmpIntVar, 2).Formula
'removed the formulas - they were creating circular references
            'added information - used later for comparison
            ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, 12) = "Mat. Flow"
            ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, 13) = ActiveWorkbook.Sheets("Summary").Cells(4, 2)
            tmpRow = tmpRow + 1
         End If
       Next tmpIntVar

'Custom
' this sheet starts on the 4th row instead of the 3rd row (buttons in row 1)
       tmpLastRow = ActiveWorkbook.Sheets("Custom").Range("Total_Custom").Row
      
       For tmpIntVar = 4 To tmpLastRow
         If ActiveWorkbook.Sheets("Custom").Cells(tmpIntVar, 2) <> "" Then
            'copy most of the row
            For tmpLoop2 = 1 To 11
               ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, tmpLoop2) = _
               ActiveWorkbook.Sheets("Custom").Cells(tmpIntVar, tmpLoop2)
            Next tmpLoop2
'            'replace the quantity with the formula
'            ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, 2).Formula = _
'            ActiveWorkbook.Sheets("Custom").Cells(tmpIntVar, 2).Formula
'removed the formulas - they were creating circular references
            'added information - used later for comparison
            ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, 12) = "Custom"
            ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, 13) = ActiveWorkbook.Sheets("Summary").Cells(4, 2)
            tmpRow = tmpRow + 1
         End If
       Next tmpIntVar

'Modular
       tmpLastRow = ActiveWorkbook.Sheets("Modular").Range("Total_Modular").Row
      
       For tmpIntVar = 3 To tmpLastRow
         If ActiveWorkbook.Sheets("Modular").Cells(tmpIntVar, 2) <> "" And _
            ActiveWorkbook.Sheets("Modular").Cells(tmpIntVar, 2).Value <> 0 Then
            'copy most of the row
            For tmpLoop2 = 1 To 11
               ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, tmpLoop2) = _
               ActiveWorkbook.Sheets("Modular").Cells(tmpIntVar, tmpLoop2)
            Next tmpLoop2
'            'replace the quantity with the formula
'            ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, 2).Formula = _
'            ActiveWorkbook.Sheets("Modular").Cells(tmpIntVar, 2).Formula
'removed the formulas - they were creating circular references
            'added information - used later for comparison
            ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, 12) = "Modular"
            ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, 13) = ActiveWorkbook.Sheets("Summary").Cells(4, 2)
            tmpRow = tmpRow + 1
         End If
       Next tmpIntVar

'Spot Weld
       tmpLastRow = ActiveWorkbook.Sheets("spot_weld").Range("Total_spot_weld").Row
      
       For tmpIntVar = 3 To tmpLastRow
         If ActiveWorkbook.Sheets("spot_weld").Cells(tmpIntVar, 2) <> "" Then
            'copy most of the row
            For tmpLoop2 = 1 To 11
               ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, tmpLoop2) = _
               ActiveWorkbook.Sheets("spot_weld").Cells(tmpIntVar, tmpLoop2)
            Next tmpLoop2
'            'replace the quantity with the formula
'            ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, 2).Formula = _
'            ActiveWorkbook.Sheets("spot weld").Cells(tmpIntVar, 2).Formula
'removed the formulas - they were creating circular references
            'added information - used later for comparison
            ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, 12) = "spot_weld"
            ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, 13) = ActiveWorkbook.Sheets("Summary").Cells(4, 2)
            tmpRow = tmpRow + 1
         End If
       Next tmpIntVar

   
    ActiveWorkbook.Sheets("UPDATE").Protect (CAT_PROTECT)

End Sub
 
Last edited by a moderator:

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
There's way too much there for me to read because you didn't use code tags (vba button on posting toolbar) to maintain indentation and readability. It's a thing I have where my eyes and brain quickly disconnect from each other. What I did manage to get from that is there's no copying/pasting going on. It's done by making one thing equal to another (which is more efficient IMO).
VBA Code: 
ActiveWorkbook.Sheets("UPDATE").Cells(tmpRow, tmpLoop2) = _
      ActiveWorkbook.Sheets("Platform").Cells(tmpIntVar, tmpLoop2)
You can step through this code (F8) and mouse over variables and references to check them. You can also test them in the immediate window as in
?tmpLoop2 and hit enter. It's value should be put on the next line. Note that a line that instantiates a variable or reference has to have been executed at least once before you can test it. These approaches are basic troubleshooting.

EDIT - your thread title isn't very revealing. It could mean that it raises an error, or doesn't even run, or does but produces the wrong results.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Creating a list of items from multiple tabs, into a single sheet.
Replies
0
Views
278
jmk15315
J
S
  • Question Question
Accumulate Data from 10k+ Files. Very Slow. Issue with ActiveWorkbook.Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
2
Replies
10
Views
987
NdNoviceHlp
NdNoviceHlp
S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
365
STARL0RD
S
J
  • Question Question
Macro for copying data from multiple sheets to a common sheet based on a cell value
Replies
0
Views
413
jmk15315
J
J
  • Question Question
Code Issue
Replies
1
Views
188
RoryA
RoryA

Forum statistics

Threads
1,223,911
Messages
6,175,329
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top