none
assign macro selection to cell name RRS feed

  • Question

  • Hello,

    in the macro below it selects Sheet "TEST".

    In the input sheet, cell C6 is the cell where the Name "TEST" is inserted. So, if we change the name to "ACTIVE" in C6 and then run the macro, the line would change to Sheets "ACTIVE" select.

    Sub CopyToDatabase()

    Dim DataRng As Range, UsedCell As Range
    Set DataRng = ActiveSheet.UsedRange

        Range("c7:c17").Select
        Selection.Copy
            Sheets("TEST").Select

      Application.GoTo Reference:="R1048576C1"
        Selection.End(xlUp).Select
        ActiveCell.Offset(1, 0).Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=True

    Thursday, January 24, 2019 2:29 PM

Answers

  • You don't seem to use those lines, so you can omit them. Here is a new version that should work if you repeat the code.

        Dim wshSource As Worksheet
        Dim wshTarget As Worksheet
        Set wshSource = ActiveSheet
        Set wshTarget = Worksheets(wshSource.Range("C6").Value)
        wshSource.Range("C7:C17").Copy
        wshTarget.Range("A" & wshTarget.Rows.Count).End(xlUp).Offset(1).PasteSpecial _
            Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=True


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by czap1 Thursday, January 24, 2019 9:40 PM
    Thursday, January 24, 2019 8:13 PM

All replies

  • You could use this:

        Dim wsh As Worksheet
        Set wsh = Worksheets(Range("C6").Value)
        Range("C7:C17").Copy
        wsh.Range("A" & wsh.Rows.Count).End(xlUp).Offset(1).PasteSpecial _
            Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=True


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, January 24, 2019 3:31 PM
  • Hello Hans,it worked once but now I am getting a "Subscript out of range" error

    The input sheet is called "Blotter". The workbook contains multiple sheets where the macro should run the same code which is to copy cells "Blotter"c7:c17 into the last row +1 of the named sheet that corresponds to the input cell c6. Each time c6 is changed with a new name, the macro runs the code on that specific sheet.

    Do I still have to use?:

    Dim DataRng As Range, UsedCell As Range
    Set DataRng = ActiveSheet.UsedRange

    Thursday, January 24, 2019 5:19 PM
  • You don't seem to use those lines, so you can omit them. Here is a new version that should work if you repeat the code.

        Dim wshSource As Worksheet
        Dim wshTarget As Worksheet
        Set wshSource = ActiveSheet
        Set wshTarget = Worksheets(wshSource.Range("C6").Value)
        wshSource.Range("C7:C17").Copy
        wshTarget.Range("A" & wshTarget.Rows.Count).End(xlUp).Offset(1).PasteSpecial _
            Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=True


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by czap1 Thursday, January 24, 2019 9:40 PM
    Thursday, January 24, 2019 8:13 PM

  • All fine. Thank you again for your help
    Thursday, January 24, 2019 9:44 PM