How can i add a sheet in another workbook that not active

Question

can someone help me, i try to write a code with 2 file open ABC.xlsm and KM.xlsm .Thisworkbook is ABC.xlsm

sub copy()
Dim sh As Workbook
Dim tensheet As String
Set sh = Workbooks("KM.xlsm")
tensheet = Range("O2").Value
sh.Sheets.Add(After:=Sheets(Sheets.Count)).Name = tensheet
end sub

i get this error "runtime error 1004 application-defined or object-defined error" this code will run if i add "sh.active" before add a new sheet but i dont want to. Any help?

https://stackoverflow.com/questions/11456157/adding-sheets-to-end-of-workbook-in-excel-normal-method-not-working/11456178#11456178 — Siddharth Rout, Mar 21 '21 at 18:29

norie · Accepted Answer · 2021-03-21T16:58:23.317

0

Try fully qualifying Sheets and Sheets.Count.

Sub AddSheet()
Dim sh As Workbook
Dim tensheet As String

    Set sh = Workbooks("KM.xlsm")
    tensheet = Range("O2").Value
    sh.Sheets.Add(After:=sh.Sheets(sh.Sheets.Count)).Name = tensheet

End Sub

edited Mar 21 '21 at 16:58

answered Mar 21 '21 at 16:45

norie

9,609
2
11
18

OMG, thank you so much for that, it really helps me alot. – Lê Văn Giới Mar 21 '21 at 16:53

How can i add a sheet in another workbook that not active

1 Answers1