CODEWITHSUNDEEP
vbavariablesexcel
Aubrey
Aubrey

Reputation: 55

Variable Trouble in Worksheets()

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

Upvotes: 0

Views: 77

Answers (2)

Dan Wagner
Dan Wagner

Reputation: 2713

@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

Upvotes: 2

Vikas
Vikas

Reputation: 805

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

Upvotes: 3

Related Questions