1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'Ask an Excel Question' started by uday, Apr 14, 2018.

  1. uday

    uday Member

    Messages:
    152
    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.

    Attached Files:

  2. Belleke

    Belleke Active Member

    Messages:
    310
    Hi,
    If the workbook does not need to stay closed, this would work (modify for path, workbook name, and ThisWorkbook sheet name):
    Code (vb):
    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
    uday likes this.
  3. uday

    uday Member

    Messages:
    152
    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

    Attached Files:

    Last edited: Apr 14, 2018
  4. Bharat M

    Bharat M New Member

    Messages:
    1
    Workbooks("Book1").Close SaveChanges:=False
  5. uday

    uday Member

    Messages:
    152
    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

Share This Page