Variable Trouble in Worksheets()

Question

I am writing an excel macro that is designed to cycle through worksheets copying sections and pasting them in a different workbook with the same worksheet names. Each actual sheet name stands for a friday in the quarter (13 sheets), so tomorrow is 071114 for July 11th, 2014.

I have set a vba variables week1 - week13 to those tab names. I want the For loop below to cycle through week1 to week13, but it isn't working the way I want it to because the computer is searching for a tab named "week1" instead of the variable week1. I think I am going to just change the tab names, but I was wondering for future reference if there is a way to do this.

For x = 1 To 13
    wbD.Activate
    Worksheets("week" & x).Activate
    Range(carange).Select
    Selection.Copy
    wbC.Activate
    Worksheets("week" & x).Activate
    Range(carange).Select
    ActiveSheet.Paste
Next x

score 3 · Accepted Answer · answered Jul 10 '14 at 23:16

I would use arrays for that. Just like below. Have not tested it yet, but should give you an idea what I am talking about.

Public Sub TestArray()

    Dim wkSheetNames(13) As String

    wkSheetNames(1) = "Some Name"
    wkSheetNames(2) = "Some Name"
    wkSheetNames(3) = "Some Name"
    '.................
    '.................
    wkSheetNames(12) = "Some Name"
    wkSheetNames(13) = "Some Name"


    For x = 1 To 13
        wbD.Activate
        Worksheets(wkSheetNames(x)).Activate
        Range(carange).Select
        Selection.Copy
        wbC.Activate
        Worksheets(wkSheetNames(x)).Activate
        Range(carange).Select
        ActiveSheet.Paste
    Next x


End Sub

Thanks, V

+1 for storing the names in an array, could be stored in a global variable if the worksheet names are used in several scripts — Dan Wagner, Jul 10 '14 at 23:59

score 2 · Answer 2 · edited May 23 '17 at 11:57

2

@Vikas' solution, which uses an array to store the worksheet names as String variables, is a great recommendation. Since your Workbook and Range variables also appear to be defined, you could further refine your macro by avoiding the .Activate and .Select methods, two common sources of run-time errors, and instead using the Range.Copy method:

'note: if carange is a named range defined in the worksheets, this code would
'need to be adjusted to ...Range("carange").Copy...
For x = 1 To 13
    wbD.Worksheets(wkSheetNames(x)).Range(carange).Copy _
        Destination:=wbC.Worksheets(wkSheetNames(x)).Range(carange)
Next x

Here's an MSDN link to the Range.Copy method with more examples: http://msdn.microsoft.com/en-us/library/office/ff837760(v=office.15).aspx

Here's a super-informative post with recommendations on how to avoid using .Select and .Activate: How to avoid using Select in Excel VBA macros

edited May 23 '17 at 11:57

Community

1
1

answered Jul 10 '14 at 23:57

Dan Wagner

2,693
2
13
18

Thanks Dan. I will definitely check those out. Typing Activate a lot is annoying.... – Aubrey Jul 11 '14 at 00:08
Truly -- and it's even MORE annoying when it causes on error! The linked S.O. post in my response is worth bookmarking, trust me :) – Dan Wagner Jul 11 '14 at 00:10
Hey @Dan, I tried your code on a different part of my process and it doesn't seem to be working for me. I was wondering if you could help out. `For y = 1 to 13 For x = 1 To 50 wbC.Worksheets(week(y)).Range(rangepl(x)).Copy _ Destination:=wbA.Worksheets(week(y)).Range(rangefc(x)) Next x Next y` The rangepl and rangefc are arrays where `Dim rangepl(50) As Range` `Set rangepl(1) = Range("B4:AS4")` – Aubrey Jul 17 '14 at 19:36
Hey @Aubrey I think the community can definitely help out here, but it looks like this is a new situation warranting a new question. Would you please add your code and a short write-up on the new task and where you are getting the error? – Dan Wagner Jul 17 '14 at 19:40

Variable Trouble in Worksheets()

2 Answers2