beartooth91
Board Regular
- Joined
- Dec 15, 2024
- Messages
- 63
- Office Version
- 365
- 2019
- 2016
- Platform
- Windows
I have lots of worksheets which routinely get copied into a master sheet. The VBA for one example is below. Everything works but it does get a bit slow as each sheet gets copied from. I'm wondering if using an array - or arrays - would speed it up? The main problem is the copy from / paste to data is in non-contiguous ranges.
VBA Code:
Sub Import_109C_IOList_New()
'
' NOTE: This macro is for 109 column instrument templates!
' It copies data from a 109-Column IO/Instrument List in the Non-Standard IO Lists folder to a new worksheet in the Master IO List workbook.
'
'------------------------------Copy all the below into each non-std IO List import sub
'Declare Variables-------------------------------------------------------------------------------
Dim sheetExists As Boolean
Dim StartRow As Long, LastRow As Long
Dim wsname As String
Dim nme() As String
Dim wbMaster As Workbook, wbOpened As Workbook '<-----use this instead of wbname?
Dim wS As Worksheet
Set wbMaster = Workbooks("NIC Master IO List.xlsm")
Set wbOpened = ActiveWorkbook
With wbOpened.Sheets(1)
nme = Split(.Cells(11, "M").Value, "-", -1) '<-----These two lines need moved into the subs for non-standard IO import
wsname = nme(1) '<-------------------------------
End With
'wbMaster.Activate '<----Is this needed?
With wbMaster
For Each wS In .Worksheets
If wS.Name = wsname Then
sheetExists = True
'------------------------------------r1
'GoTo ThisLine 'Or use Exit For (next line)
Exit For
Else '<---------------Is this needed?
sheetExists = False
End If
Next
'------------------------------------------r1
' Adjust the below for current import sub Copy/Destination code
If Not sheetExists Then
wbMaster.Sheets(1).Copy After:=wbMaster.Sheets(wbMaster.Worksheets.Count)
ActiveSheet.Name = wsname
'.Sheets("NewSheet").Name = wsname
End If
'-------------------------------------------
StartRow = .Sheets(wsname).Cells(Rows.Count, "B").End(xlUp).Row + 1
End With
'ThisLine:
'wbOpened.Activate--------------------??
With wbOpened.Sheets(1)
LastRow = .Cells(Rows.Count, "B").End(xlUp).Row
'<-------------------------------------------r1
'Deleted the If Then Else, kept the copy/paste
'
'Copy Component Number
wbOpened.Worksheets(1).Range("M11:M" & LastRow).Copy
Workbooks("NIC Master IO List.xlsm").Worksheets(wsname).Range("B" & StartRow).PasteSpecial Paste:=xlPasteValues
'Copy Unit Number
wbOpened.Worksheets(1).Range("B11:B" & LastRow).Copy
Workbooks("NIC Master IO List.xlsm").Worksheets(wsname).Range("C" & StartRow).PasteSpecial Paste:=xlPasteValues
'Copy System Desig, CC, Seq Number, and Parallel Desig
wbOpened.Worksheets(1).Range("D11:G" & LastRow).Copy
Workbooks("NIC Master IO List.xlsm").Worksheets(wsname).Range("D" & StartRow).PasteSpecial Paste:=xlPasteValues
'Copy Channel/Div
wbOpened.Worksheets(1).Range("K11:K" & LastRow).Copy
Workbooks("NIC Master IO List.xlsm").Worksheets(wsname).Range("H" & StartRow).PasteSpecial Paste:=xlPasteValues
'Copy Desc, Noun Name, Design Phase, Bldg, Location, Elevation, Doc/Dwg, Datasheet, Parent, Safety Class, Safety Function
wbOpened.Worksheets(1).Range("N11:Y" & LastRow).Copy
Workbooks("NIC Master IO List.xlsm").Worksheets(wsname).Range("I" & StartRow).PasteSpecial Paste:=xlPasteValues
'Copy Class 1E
wbOpened.Worksheets(1).Range("AC11:AC" & LastRow).Copy
Workbooks("NIC Master IO List.xlsm").Worksheets(wsname).Range("X" & StartRow).PasteSpecial Paste:=xlPasteValues
'Copy Voltage, SDG, and Off-Site Power Feed
wbOpened.Worksheets(1).Range("AF11:AH" & LastRow).Copy
Workbooks("NIC Master IO List.xlsm").Worksheets(wsname).Range("Y" & StartRow).PasteSpecial Paste:=xlPasteValues
'Copy Weight, MR/PR, Vendor Supplied, Fab Cat, Dest, Vendor Ins, Manufacturer, Model, Equip No.
wbOpened.Worksheets(1).Range("AL11:AT" & LastRow).Copy
Workbooks("NIC Master IO List.xlsm").Worksheets(wsname).Range("AC" & StartRow).PasteSpecial Paste:=xlPasteValues
'Copy Resp Design Org and Remarks
wbOpened.Worksheets(1).Range("BA11:BB" & LastRow).Copy
Workbooks("NIC Master IO List.xlsm").Worksheets(wsname).Range("AM" & StartRow).PasteSpecial Paste:=xlPasteValues
'Copy Instrument Data Fields
wbOpened.Worksheets(1).Range("BF11:CF" & LastRow).Copy
Workbooks("NIC Master IO List.xlsm").Worksheets(wsname).Range("AO" & StartRow).PasteSpecial Paste:=xlPasteValues
wbOpened.Close
End With
'Formatting
Workbooks("NIC Master IO List.xlsm").Activate '<----
fr = Cells(1, 1).SpecialCells(xlLastCell).Row
Workbooks("NIC Master IO List.xlsm").Worksheets("Instrument List Template").Range("B11:BO11").Copy
Workbooks("NIC Master IO List.xlsm").Worksheets(wsname).Range("B11:BO" & fr).PasteSpecial Paste:=xlPasteFormats
'
Application.ScreenUpdating = True
Application.CutCopyMode = False
Workbooks("NIC Master IO List.xlsm").Save
'
End Sub
