• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need to get sheet name from one workbook to another

uday

Member
Hi,

I need to take the sheet name from Book1 to Book2. Sheet name should appear in cell reference. Cells are highlighted with yellow color.
 

Attachments

  • Book1.xlsx
    8 KB · Views: 2
  • Book2.xlsx
    7.6 KB · Views: 3
Hi,
If the workbook does not need to stay closed, this would work (modify for path, workbook name, and ThisWorkbook sheet name):
Code:
Sub Test1()
Workbooks.Open Filename:="C:\Your\File\Path\Book1.xlsx"
Dim ws As Worksheet
Dim i As Integer
With ThisWorkbook.Worksheets("Sheet1")
.Range("A:A").ClearContents
For Each ws In ActiveWorkbook.Worksheets
i = i + 1
.Range("A" & i) = ws.Name
Next ws
End With
Workbooks("Book1").Close False
End Sub
 
Thanks for your help.

I am getting out of range error @ "Workbooks("Book1").CloseFalse"

I dont want to clear the text from A column. Whatever text is in column A should be remain intact, only need to add same data after last row. If any new name sheet added with new name in Book1. Then it should also included.

Regards,

Roy
 

Attachments

  • Out of range error.JPG
    Out of range error.JPG
    16.8 KB · Views: 1
Last edited:
Still the error persists.

I dont want to clear the text from A column. Whatever text is in column A should be remain intact, only need to add same data after last row. If any new name sheet added with new name in Book1. Then it should also included.

Regards,
Roy
 
Back
Top