Results 1 to 6 of 6

Thread: Using codename in vba

  1. #1

    Using codename in vba



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hey Guys

    I want to use the codename for the macro because the sheet name will need to be regularly changed, but it comes up with "Run time error '424'...object required" Can someone please help?

    Code:
    Sub transfer_data()'
    ' transfer_data Macro
    '
    
    
    '
        Windows("CH Normalisation.xlsx").Activate
        Sheet49.Select
        ActiveWindow.SmallScroll Down:=-9
        ActiveSheet.Range("$A$1:$AK$39693").AutoFilter Field:=1, Criteria1:= _
            "Flight Centre Limited"

  2. #2

    Post

    Code:
       
        Sheet2.Select
        Range("A1:I11").Select
        Selection.Copy
        Windows("Book1").Activate
        Sheet1.Select
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("B14").Select
    End Sub
    This code illustrates the problem I'm having more clearly.
    The first codename (Sheet2) works fine, but it gets stuck at "Sheet1.Select". Most likely because Book1 is activated.
    What I am trying to do is create a macro to copy some data onto another spreadsheet with the ability of changing sheet names.

  3. #3
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    add a global variable sSheet that you set when the sheet name changes then change your code to

    Code:
    with Workbooks("CH Normalisation.xlsx").worksheets(sSheet)
       .range(......
    
    end with
    Do you really need to select the sheet? and scroll down 9 line?

    This is in reply to your first post not revised post.
    Last edited by WizzardOfOz; 2014-09-18 at 07:15 AM.

  4. #4
    Thanks but I still don't get how I incorporate that into the code. Sorry I'm new to this. Could you show me how to do this, especially in regards to the second post? Thanks!

  5. #5
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    370
    Articles
    0
    Excel Version
    2020
    use

    Code:
    Sub M_snb()
       workbooks("Book1").sheet1.Range("A1:I11")=sheet2.Range("A1:I11").Value
    End sub
    instead of
    Code:
    Sheet2.Select     Range("A1:I11").Select     Selection.Copy     Windows("Book1").Activate     Sheet1.Select     Range("A1").Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False     Range("B14").Select
    Avoid any 'select' or 'activate' in VBA

  6. #6
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Assuming macro's are totally unknown to you, here is a black box program that will do this for you.

    Attached is an Excel addIn. It will appear to do very little. On loading it just adds a new tab to the ribbon (menu). The last Item "WizzardOfOz" has a new function Copy Paste. which does what you want.

    Open the attached (can add it as an addin)
    Then make sure both the source and destination workbook are already opened.
    Click the "CopyPaste" button.
    You will be prompted for "What you want to copy"
    Then you will be prompted on where it must go. I ignore the range so only the top cell is required.

    Code below included for the paranoid
    Code:
    Option Explicit
    
    
    Public Sub CopyPaste_onAction(control As IRibbonControl)
    ' Code for onAction callback. Ribbon control button
    Call CutCopyRange
    
    
    End Sub
    
    
    Sub CutCopyRange()
    Dim rRange As Range, oRange
    
    
        On Error Resume Next
        Application.DisplayAlerts = False
        
        'get the cells to be copied
        Set rRange = Application.InputBox(Prompt:= _
            "Please select a range with your Mouse to be copied." & vbCrLf & _
            "You may select any open sheet in any opened work book", _
            Title:="SPECIFY RANGE", Type:=8)
        On Error GoTo 0
        Application.DisplayAlerts = True
        
        If rRange Is Nothing Then
            Exit Sub
        Else
            oRange = rRange.Value2
            
            On Error Resume Next
            Application.DisplayAlerts = False
            'get where to paste
            Set rRange = Application.InputBox(Prompt:= _
                "Please select the Top left cell with your Mouse to paste." & vbCrLf & _
                "You may select any open sheet in any opened work book", _
                Title:="SPECIFY RANGE", Type:=8)
            On Error GoTo 0
            Application.DisplayAlerts = True
            If rRange Is Nothing Then
                Exit Sub
            Else
                'go and paste
                rRange.Cells(1, 1).Resize(UBound(oRange, 1), UBound(oRange, 2)).Value2 = oRange
            End If
    
    
        End If
    
    
    End Sub
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •