Activate Worksheet Using Concatenated Strings for Name

[Gos-C]

Hi all,

I am trying to create a macro to cycle through four worksheets and, if a date in a column is within a specified range, copy various cells in that record and paste them in another sheet in different workbook. For example, if column K >= 01-Jan-2012 and <= 31-Mar-2012, copy columns A, F, G, P, T and W and paste them in columns A, F, D, B, E and C of the other specified sheet.

The four sheets are named in a ABC2011, XYZ2011, ABC2012 and XYZ2012. Each year, two the names end with the previous year and the other two end with the current year.

I have not been able to activate the sheet (using strings to identify it).

Here is my code, so far:

Option Explicit
Sub Activate_Sheet()
'Check that the ABC Packages workbook is opened.
Dim wBook As Workbook
Dim wks1 As String, wks2 As Worksheet, wks3 As Worksheet, wks4 As Worksheet
Dim sh1 As String, sh2 As String, x As Worksheet

On Error Resume Next
Set wBook = Application.Workbooks("ABC PACKAGES.xlsb")

sh1 = Chr(34) & "ABC"
sh2 = (DatePart("yyyy", Date)) & Chr(34)
wks1 = "Worksheets(" & sh1 & sh2 & ")"

x = wks1
C:\Documents and Settings\My Name\Desktop\ABC PACKAGES.xlsm
If wBook Is Nothing Then 'Not open
Application.Workbooks.Open ("C:\Documents and Settings\My Name\Desktop\ABC PACKAGES.xlsm"), Password:="Password"

Set wBook = Nothing

On Error GoTo 0

Else 'It is open

Application.Workbooks(wBook).Activate
Application.Worksheets(sh1 & sh2).Activate

    
Set wBook = Nothing

On Error GoTo 0

End If
End Sub

Can someone help me fix it, please?

Thank you,
Gos-C

[111StepsAhead]

edit: Read your post wrong on what you wanted.

[Gos-C]

Hi 111StepsAhead,

Thank you very much for your quick response.

I want to use variables for the sheets names so that I won't have to change them in the macro each year.

[JBeaucaire]

I would think you could set the wks1 variable name like so:

Set wks1 = Sheets("ABC" & Year(Date))

You can get last year with Year(Date)-1.

[Gos-C]

I tried that but it didn't work.

[Gos-C]

Hi all,

I got it to work!

I used:

wks1 = "ABC" & Year(Date)

Thank you,
Gos-C

[JBeaucaire]

If that takes care of your need, please click Thread Tools above your first post and mark this thread as SOLVED.

[Gos-C]

See new thread:

http://www.excelforum.com/excel-prog...html?p=2761298

Gos-C