0

Can some one help with the last bit of this code please, I have a range maximum ("A1:A54") when i set to this range and only (A1:A10) have a cell value which is the name of a sheet in another workbook.

This code is working but returns a

runtime 9 error

I really want to add if blank ignore if I change to range I have set to A1:A10 then no error. I think it might be there is no worksheets in the other workbook this is why I get an error on this loop.

Have looked how to ignore blanks but none of the answers i have found have worked.

I really want a if cell = "" then ignore currently I thought exit sub would work Sub Iedextraction()

 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 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

 Next

' On Error GoTo 0
End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
Richard Hannigan
  • 53
  • 6
  • Possible duplicate of [Excel VBA code to select non empty cells](https://stackoverflow.com/questions/39967118/excel-vba-code-to-select-non-empty-cells) – ppijnenburg Jan 24 '18 at 12:39

3 Answers3

1

Add conditional instruction: 

If cl <> "" Then wkb1.Sheets(cell.Value).Copy After:=Workbooks _
("ASE RTU Addressing with Automation.xlsm").Sheets(4)

And remove:

If cell = "" Then Exit Sub
Pierre44
  • 1,711
  • 2
  • 10
  • 32
MarcinSzaleniec
  • 2,246
  • 1
  • 7
  • 22
0

Your error occurs most probably because you try to copy before you do the check. So, at the end, you try to get a sheet with no name. :)

BigFish
  • 1
  • brilliant thank you so much that works a treat i am fairly new to VBA and think i may off bitten off more than i can chew its all a learning curve – Richard Hannigan Jan 24 '18 at 12:57
0

It is better practice to use dynamic ranges since it's likely going to change at some point in the future. There are multiple ways of doing this, but my go to method is something like this:

Dim rn As Range
Set rn = Range(Range("A1"), Range("A1").End(xlDown))

So your issue would be resolved (assuming the blank cells are truly empty) and you will not have to test for blank cells.

Regardless, you should mark one of the above answers are correct if they fixed your issue.

Spencer
  • 107
  • 9