Using codename in vba

[ken.e]

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?

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"

[ken.e]

   
    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.

[WizzardOfOz]

add a global variable sSheet that you set when the sheet name changes then change your code to

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.

[ken.e]

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!

[snb]

use

Sub M_snb()
   workbooks("Book1").sheet1.Range("A1:I11")=sheet2.Range("A1:I11").Value
End sub

instead of

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

[WizzardOfOz]

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

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