Solved: referencing specific worksheets in vba

[tkaplan]

Hi,

I need some help with using worksheets in vba.
writing a macro that i want to be able to run from any of the worksheets in my workbook. One of the things I do in the macro is copy a cell from a specific sheet into the sheet I ran the macro from. All of the sheets are within the same workbook.
so I think it should be something like this, i just don't know the proper syntax:

Dim cursheet as Worksheet

' here is where i would need to say that cursheet should be the currently
' active worksheet because that is where i will run the macro from

Sheets("XYZ").Select
'
'copy the cell I want
'

Sheets(cursheet).Select

So if someone can please help me with that red line I would really appreciate it.

TIA

[lucas]

You need to put your code in a standard module if you want it available to all sheets.

No need to select either:

[VBA]Option Explicit
Sub Macro1()
Sheets("Sheet2").Range("E11").Value = Sheets("Sheet1").Range("A1")
End Sub
[/VBA]

see attached example

[lucas]

or like this using the sheet names you listed:

[VBA]Option Explicit
Sub Macro1()
Sheets("cursheet").Range("E11").Value = Sheets("XYZ").Range("A1")
End Sub
[/VBA]

[tkaplan]

ok. let me clarify. the code is in a standard module.
basically i have a workbook that has a main worksheet with a bunch of data. then every month i add another worksheet named with the name of the month - so jan, feb, march, and now i'm adding april. the monthly worksheet needs to have the first row from spreadsheet "xyz" copied into it.
so where you put sheets("cursheet"), that wouldn't work for me, since the name of the spreadsheet that i run the macro from will be different. what i need is a way to select the sheet that i am running the macro from (as in the active spreadsheet) and put that into a variable so that i can refer back to it to paste the data.
that make more sense?

[GTO]

Hi TIA,

I did not find the question totally clear. Are you looking to simply add/copy the row to the active (newly created month) sheet, or would you rather (via code) create the sheet and copy the row to it?

Mark

[Aussiebear]

Go with the second option Mark

[tkaplan]

I am looking to be able to switch back and forth between 2 worksheets. one of the worksheets is named "xyz". the other one will be the worksheet that i run the macro from so that will be a variable value

[GTO]

I agree with Ted, as I suspect that this is what you are going after. If this does not work, please post a wb, even if no code developed, and show a 'before/after'.

See attached, but a simple userform to see if this is what you are after:

Option Explicit
    
Private Sub cmdCancel_Click()
    Unload Me
End Sub
    
Private Sub cmdOK_Click()
Dim wks As Worksheet
    
    If Not ComboBox1.ListIndex = -1 Then
        ThisWorkbook.Worksheets.Add.Name = ComboBox1.Value
        Set wks = ActiveSheet
        ThisWorkbook.Worksheets("xyz").Rows(1).Copy wks.Range("A1")
        Unload Me
    Else
        Unload Me
        MsgBox "Pick a sheet!", 0, vbNullString
    End If
End Sub
    
Private Sub UserForm_Initialize()
Dim aryMonths As Variant
Dim i As Long
    
    aryMonths = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", _
                      "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
    
    For i = LBound(aryMonths) To UBound(aryMonths)
        If Not ShExists(aryMonths(i)) Then
            Me.ComboBox1.AddItem aryMonths(i)
        End If
    Next
End Sub
    
Function ShExists(ByVal ShName As String, _
                  Optional WB As Workbook, _
                  Optional CheckCase As Boolean = False) As Boolean
    
    If WB Is Nothing Then
        Set WB = ThisWorkbook
    End If
    
    If CheckCase Then
        On Error Resume Next
        ShExists = CBool(WB.Worksheets(ShName).Name = ShName)
        On Error GoTo 0
    Else
        On Error Resume Next
        ShExists = CBool(UCase(WB.Worksheets(ShName).Name) = UCase(ShName))
        On Error GoTo 0
    End If
End Function

Hope that helps,

Mark

[tkaplan]

All of these examples hardwire some name or something. I am not looking for a macro to add sheets, change names, or anything like that. I want to be able to run this macro from any worksheet, doenst matter what the name is. if i run the macro from worksheet "abc", I need to be able to switch back and forth from "abc" to "xyz". if i run it from "jkl", I need to be able to switch back and forth from "jkl" and "xyz". "abc" and "jkl" may not yet be in existance. They are worksheets that i import from other workbooks, about once a month. so it happens to be that the name is usually a month, but it could be other names and i need it to work no matter what. i need to have a variable worksheet that references the worksheet where i ran the macro from. i.e. the first thing the macro should do is look at what the active sheet is and assign a reference to that worksheet to some variable.

I came up with a workaround that's working. I just think there is definitely another way to do it.

what i did is:
[VBA]
Dim CurrentSht As String

CurrentSht = ActiveSheet.Name

Sheets("xyz").select
'do whatever I have to do on that sheet
Sheets(CurrentMonth).select

[/VBA]

so what I'm doing here is assigning the name of the worksheet to a variable so then i can use that variable to call the worksheet that i want, by looking at the actual name of the worksheet. I was under the impression that there is a way to assign the actual worksheet to a variable (not the name of the worksheet) and then to call that worksheet on its own. But maybe that's not accurate.

[lucas]

You can assign it to a public variable with no problem.

My question is how will you call it as it will require two macro's to do what you wish. How would it know to stop at xyz while you do things?

On the attached you can go to sheet two and hit the button.

It will take you to sheet xyz and stop.

on sheet xyz there is a button to return you to the sheet you came from, in this case sheet 2.

It works the same way for all sheets but they have to have a button or some way to run the macro.

Here's the code:
[VBA]Option Explicit
Public CurrentSht As String
Sub gotoXYZ()
CurrentSht = ActiveSheet.Name
Sheets("xyz").Select
'do whatever I have to do on that sheet
End Sub
Sub GObackToCurrentsheet()
Sheets(CurrentSht).Select
End Sub
[/VBA]

see attached example

[mdmackillop]

Rather than use the name, set a WorkSheet variable to represent the sheet. Neater and less typing!

[VBA]
Sub Test()
Dim CurSht As Worksheet
Dim OtherSht As Worksheet

Set CurSht = ActiveSheet
Set OtherSht = Sheets("Sheet3")

CurSht.Cells(1, 1) = OtherSht.Cells(1, 1) * 2
End Sub

[/VBA]

[tkaplan]

lucas, your example is the workaround that i came up with by using .name and assigning that to the variable.

mdmackillop, this is what i am looking to do - to assign the active sheet to a variable. so that's your first like. so now using the 2 variables CurSht and OtherSht, what code would I use to select one of them, do some stuff in it, and then go back and forth - I don't just want to be able to copy values and stuff, I need to be able to actually switch back and forth between the sheets. so I know that if I use the name of the sheets i would put
[VBA]Sheets("name of sheet").select[/VBA] and can switch back and forth that way. How do I do that same thing using just the variables?

Thanks much for all of your assistance!

[mdmackillop]

You should not really need to switch between sheets, other than at the end of your code. Just prefix ranges by the sheet variable and you can operate on any sheet in any open workbook.

[vba]OtherSht.Activate[/vba]

[tkaplan]

thank you. I know that there are definitely ways to do this without as much typing as I do. Would it be okay if I post the code for the macro that I have and when you have a minute would you be able to fix it up in the "proper" way to do it? this is currently working for me so it's not urgent, but I would love to learn cleaner language.

Thanks
t

[mdmackillop]

Happy to assist.

[tkaplan]

Thanks!

here's what I have:
[VBA]
Sub MonthlyHeadcountRun()
'
' delete and autoformat rows
'determine number of records in the bill
Dim NumEmployees As Integer

Dim CurSht As Worksheet
Dim OtherSht As Worksheet

Set CurSht = ActiveSheet
Set OtherSht = Sheets("Revised 0110")


Rows("1:3").Select
Range("A3").Activate
Selection.Delete Shift:=xlUp
Cells.Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Rows("1:1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Cells.Select
Cells.EntireColumn.AutoFit
Range("H1").Select
Selection.End(xlDown).Select
NumEmployees = ActiveCell.Row - 1

'copy formula and formatting from january
OtherSht.Activate
Range("I1:M2").Select
Selection.Copy
CurSht.Activate
Range("I1").Select
ActiveSheet.Paste
OtherSht.Activate
Rows("2:2").Select
Application.CutCopyMode = False
Selection.Copy
CurSht.Activate
Rows("2:2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

' copy formula down based on number of employees
Range("I2:M2").Select
Selection.Copy
Range("I2").Select
Range(ActiveCell, ActiveCell.Offset(NumEmployees - 1, 0)).Select
ActiveSheet.Paste

Rows("2:2").Select
Selection.Copy
Selection.Resize(NumEmployees).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

'copy employee count formulas
OtherSht.Activate
Range("F1081:K1102").Select
Selection.Copy
CurSht.Activate
Range("F1").Select
ActiveCell.Offset(NumEmployees + 1, 0).Select
ActiveSheet.Paste

'set print area
ActiveSheet.PageSetup.PrintArea = Selection.Address
With ActiveSheet.PageSetup
.CenterHeader = "&A"
.RightFooter = "&D &T"
End With

End Sub

[/VBA]

[mdmackillop]

I've done the first part. See how you get on with the rest

[vba]
Option Explicit
Sub MonthlyHeadcountRun()
'
' delete and autoformat rows
'determine number of records in the bill
Dim NumEmployees As Long
Dim CurSht As Worksheet
Dim OtherSht As Worksheet
Set CurSht = ActiveSheet
Set OtherSht = Sheets("Revised 0110")
With CurSht
.Rows("1:3").Delete
With .Cells
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With .Rows("1:1")
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
.Cells.EntireColumn.AutoFit
NumEmployees = Application.CountA(.Columns(8)) - 1
'copy formula and formatting from january
OtherSht.Range("I1:M2").Copy .Range("I1")
OtherSht.Rows("2:2").Copy
.Range("A2").PasteSpecial Paste:=xlPasteFormats
' copy formula down based on number of employees
OtherSht.Range("I2:M2").Copy .Range("I2")
.Range("I2:M2").Resize(NumEmployees).FillDown
End With

'@@@@@@ The rest for you to try
OtherSht.Activate
Rows("2:2").Select
Selection.Copy
Selection.Resize(NumEmployees).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
'copy employee count formulas
OtherSht.Activate
Range("F1081:K1102").Select
Selection.Copy
CurSht.Activate
Range("F1").Select
ActiveCell.Offset(NumEmployees + 1, 0).Select
ActiveSheet.Paste
'set print area
ActiveSheet.PageSetup.PrintArea = Selection.Address
With ActiveSheet.PageSetup
.CenterHeader = "&A"
.RightFooter = "&D &T"
End With
End Sub


[/vba]

[tkaplan]

I got it!!! thanks so much!