none
VBA CODE FOR COPYING THE VALUES. RRS feed

  • Question

  • Hi All,

    I am looking code for copy the value from certain range like A1 from source sheet to target sheet but by time of pasting value to target sheet automatically name of the sheet should be changed to like "default", this name of the sheet is only for target sheet not for source sheet.

    If u give me the vba it will be help me a lot on this.

    Thanks & Regards

    Vinay

    Wednesday, July 1, 2015 5:58 AM

Answers

  • Hi,

    You can write VBA code like this:
      Worksheets(1).Range("A1")
      instead of Worksheets("Sheet1").Range("A1")
    Worksheets(1) means the first worksheet(leftmost).
    You can specify a sheet by index(start from 1), Worksheet.Name instead.

    cf. Worksheet Object (Excel)

    # If there exist possibilities that the order of sheets would be changed, you can make Name to be unchanged.
    Regards,
    • Edited by Ashidacchi Thursday, July 2, 2015 3:38 AM
    • Marked as answer by L.HlModerator Thursday, July 9, 2015 2:43 AM
    Wednesday, July 1, 2015 6:54 AM
  • Hi,

    To copy cell value from one sheet to another cell in another sheet, you may try the following code

     
    ActiveWorkbook.Sheets(2).Range("A1").Value = ActiveWorkbook.Sheets(1).Range("B1").Value

    As Ashidacchi suggestedm, you could also use sheet name to get the sheet object. 

    Hope this could help you.

    Best Regards,

    lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by L.HlModerator Thursday, July 9, 2015 2:43 AM
    Thursday, July 2, 2015 3:34 AM
    Moderator

All replies

  • Hi,

    You can write VBA code like this:
      Worksheets(1).Range("A1")
      instead of Worksheets("Sheet1").Range("A1")
    Worksheets(1) means the first worksheet(leftmost).
    You can specify a sheet by index(start from 1), Worksheet.Name instead.

    cf. Worksheet Object (Excel)

    # If there exist possibilities that the order of sheets would be changed, you can make Name to be unchanged.
    Regards,
    • Edited by Ashidacchi Thursday, July 2, 2015 3:38 AM
    • Marked as answer by L.HlModerator Thursday, July 9, 2015 2:43 AM
    Wednesday, July 1, 2015 6:54 AM
  • Hi,

    To copy cell value from one sheet to another cell in another sheet, you may try the following code

     
    ActiveWorkbook.Sheets(2).Range("A1").Value = ActiveWorkbook.Sheets(1).Range("B1").Value

    As Ashidacchi suggestedm, you could also use sheet name to get the sheet object. 

    Hope this could help you.

    Best Regards,

    lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by L.HlModerator Thursday, July 9, 2015 2:43 AM
    Thursday, July 2, 2015 3:34 AM
    Moderator
  • Hello:

    The previous answers to your question should take care of part of your question.  It seems you want to rename a sheet.  Here are the two ways you can do that:

    Public Sub ChangeSheetName()
    ' **********************************************
    ' Reference Sheet To Rename By Number
    ' **********************************************
    ThisWorkbook.Sheets(1).Name = "Default"
    
    ' **********************************************
    ' Reference Sheet To Rename By Name
    ' **********************************************
    ThisWorkbook.Sheets("Sheet2").Name = "Default1"
    End Sub
    
    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Thursday, July 2, 2015 4:38 AM