Share via

Ingnore blank cells in vba code to prevent a runtime error

Anonymous
2018-01-24T12:53:23+00:00

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

Microsoft 365 and Office | Excel | For home | Windows

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.

0 comments No comments

1 answer

Sort by: Most helpful
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2018-01-24T13:28:05+00:00

    Try this:

        For Each cell In Rng

            If cell.Value <> "" Then

                wkb1.Sheets(cell.Value).Copy After:=wkb.Sheets(4)

            End If

        Next cell

    0 comments No comments