PDA

View Full Version : Solved: referencing specific worksheets in vba



tkaplan
04-22-2010, 11:19 AM
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
04-22-2010, 11:24 AM
You need to put your code in a standard module if you want it available to all sheets.

No need to select either:

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


see attached example

lucas
04-22-2010, 11:26 AM
or like this using the sheet names you listed:

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

tkaplan
04-22-2010, 01:14 PM
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
04-22-2010, 01:39 PM
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
04-22-2010, 01:59 PM
Go with the second option Mark

tkaplan
04-22-2010, 02:00 PM
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
04-22-2010, 03:07 PM
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
04-23-2010, 07:49 AM
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:

Dim CurrentSht As String

CurrentSht = ActiveSheet.Name

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



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
04-23-2010, 08:14 AM
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:
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


see attached example

mdmackillop
04-23-2010, 08:27 AM
Rather than use the name, set a WorkSheet variable to represent the sheet. Neater and less typing!


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

tkaplan
04-23-2010, 09:24 AM
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
Sheets("name of sheet").select 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
04-23-2010, 09:27 AM
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.

OtherSht.Activate

tkaplan
04-23-2010, 09:31 AM
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
04-23-2010, 09:34 AM
Happy to assist.

tkaplan
04-23-2010, 09:37 AM
Thanks!

here's what I have:

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

mdmackillop
04-23-2010, 09:58 AM
I've done the first part. See how you get on with the rest


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

tkaplan
04-26-2010, 09:35 AM
I got it!!! thanks so much! :)