If Condition is True, Save Data in Array, and Copy Array Data to other Worksheet

[skelly8117]

Hello-

I am new to writing Excel VBA. My goal is to start with a specific worksheet called "Hoist1", evaluate Column I called "Safety Code" beginning on row 7, and compile records with 2s and 3s (exclude the 1s). I need to save the values (all text data type) found in Columns B, A, and J in this order, to an array.

After evaluating all rows, I need to proceed to the next worksheet and do the same. There are a total of 8 consecutive worksheets.

Lastly, I need to copy the data saved in the array to the Summary worksheet into its columns A, B, and C beginning on row 7. Initially, there are two blank rows. Therefore, after the two rows have been filled, I need the VBA procedure to insert a new row after the second row.

By the way, there is a column called "Action Code" in Column J for which I need to copy data from the same eight worksheets to the Summary worksheet beginning 4 rows after the last Safety Code.

Please let me know if you have any questions or need any clarifications.

Thank you for your patience as I am a "newbie!"

Skelly

Option Explicit     
‘The “Add” btn to be clicked after all component worksheets have been completed.
Sub UpdateLogWorksheet()   ‘The “Add” btn
    Dim Compilation As Worksheet  ‘Summary Tab
    Dim wks As Worksheet  ‘The Active Worksheet
    Dim iCounter As Integer
    Dim xRow As Long    
    Dim Safety23array() As Variant
    Dim issueCounter As Integer
    Dim oCol As Long
    
    Set xRow = 7    
    Set wks = Worksheets("Hoist1")   ‘Active worksheet (One of the 8 Worksheets are named Hoist1 through Runway)
    Set Compilation = Worksheets("Summary")
    oCol = 1 'order info is pasted on data sheet, starting in this column
  Set myCopy = Safety23.Range("SafetyCodeCol")   ‘SafetyCodeCol-a Name Range for Column I’s Safety Codes 1, 2 or 3
	
‘***
‘***Compiling Safety Codes information from three columns where the Safety Code equals either 2 or 3.
‘***‘See p.106 looping through worksheets
‘***
iCounter = 1
issueCounter =0
Do While  iCounter <=8  ‘loop once for each of the eight worksheets beginning with Hoist1
	
	If Range (“I” & xRow).Value = “2” or Range (“I” & xRow).Value = “3” Then

			ReDim Preserve Safety23array(issueCounter)
			Safety23array(issueCounter) =   ???   Store as 3 part string in element.  Save cells b, a and j values in this order.
		
			 issueCounter = issueCounter +1
		Next xRow	
		End If
iCounter = iCounter + 1
Loop		‘Do While Loop

‘**** Paste into Summary Tab
issueCounter = 0     
For issueCounter = LBound(Safety23array) To UBound(Safety23array)  ‘The number of Safety Code issues is dynamic.
	With compilation
		If issueCounter > 1 Then  ‘The Summary initially has only two blank rows under Safety Code.
			Rows(xRow).Insert  ‘After adding two Safety Codes valued at 2/3, add a new row in the Summary wks.
			Safety23array(issueCounter).Copy  'copy the Safety Code data and paste onto data sheet
			.Cells(nextRow, oCol).PasteSpecial Paste:=xlPasteValues, Transpose:=True
		Else
			Safety23array(issueCounter).Copy  'copy the Safety Code data and paste onto data sheet
			.Cells(nextRow, oCol).PasteSpecial Paste:=xlPasteValues, Transpose:=True

		Application.CutCopyMode = False
	End With
Next issueCounter