A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Try this:
For Each cell In Rng
If cell.Value <> "" Then
wkb1.Sheets(cell.Value).Copy After:=wkb.Sheets(4)
End If
Next cell
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Hi,
I am fairly new to VBA and am having issues with this code i have constructed, The point of this code is to copy worksheets from another workbook when i don't know the name of the worksheet which will change depending on the data.
I have achieved the extraction of the worksheet names in to my active work book which are in column A of the and the maximum number of sheet names will be 54 but generally will only be 8 - 12. This information will be A1:A12 When i run this code it works and i can copy the worksheets but i get a runtime 9 error whilst it loops through the range with blanks i just want to remove this error
I thought the using an if statement in red at bottom of code to exit would work i have looked at other examples and tried to incorporate them in but seems to mess up the rest of the code any help appreciated
Sub Iedextraction()
'Dim i As Integer
Dim wkb As Excel.Workbook, wkb1 As Excel.Workbook
Dim wks As Excel.Worksheet, wks1 As Excel.Worksheet
Dim cell As Range
Dim rng As Range
Workbooks.Open Filename:= _
"D:\Projects\ASE Templates\ASE Template White Book.xlsx"
Set wkb = Excel.Workbooks("ASE RTU Addressing with Automation.xlsm")
Set wks = wkb.Worksheets("Tab Names from White book")
Set wkb1 = Excel.Workbooks("ASE Template White Book.xlsx")
'Set wks1 = wkb1.Worksheets(cell.Value)
Set rng = wks.Range("A1:A54")
For Each cell In rng
wkb1.Sheets(cell.Value).Copy After:=Workbooks("ASE RTU Addressing with Automation.xlsm").Sheets(4)
If cell = "" Then Exit Sub
'wkb1.Sheets(cell.Value).Copy After:=wkb.wks
'On Error GoTo 0
Next
' On Error GoTo 0
End Sub
Thanks in advance for any help
Moved from: Office /Excel /Other/unknown /Office 2016
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Try this:
For Each cell In Rng
If cell.Value <> "" Then
wkb1.Sheets(cell.Value).Copy After:=wkb.Sheets(4)
End If
Next cell