Changing a Private variable OR defining a Range variable. Excel VBA

Question

OK, so I'm working with VBA in excel, and I've come across an issue. Basically, someone has already programmed this workbook to do what I need to do, however, I've made some changes and can't figure out how to change the one line of code to work.

There is a 'Create New Month' macro that eventually calls upon a "SetupEachDay" sub. It copies sheets A, B, C, and D in a particular order to create the new file. I've modified the 4 sheets to contain the data I want as an appropriate template. When copying the sheets, the code copies Range("B13") which apparently only copies columns A-L from A/B/C/D to the new sheets. I need the function to copy from A2:Q82.

I can't find where B13 is defined, and I don't know how to change it to copy the entire sheet. I tried just putting Range("A2:Q82") but it didn't work.

Help?

Code:

Counter = 1 'initialize counter
While (Counter < 32)
    If ((DatePart("w", Worksheets(Counter).Range("A1"))) <> "7" And (DatePart("w", Worksheets(Counter).Range("A1"))) <> "1") Then
        Worksheets("E").Range(Worksheets("Setup").Range("B13")).Copy Destination:=Worksheets(Counter).Range("A2")
    End If
Counter = Counter + 1
Wend

Welcome to SO. Either try the psychic hotline or show us the code in question :). — Doug Glancy
– Doug Glancy, Commented Jan 5, 2014 at 1:54
Worksheets("E").Range(Worksheets("Setup").Range("B13")).Copy is the source range! — Matt Weller
– Matt Weller, Commented Jan 5, 2014 at 2:13
What's in Worksheets("Setup).range("B13")? I'm sure it will be $A$2:$L$47 — Mark Fitzgerald
– Mark Fitzgerald, Commented Jan 5, 2014 at 2:35
It looks like the range to be copied is stored in cell B13 in the "Setup" worksheet. For example cell B13 might contain something like "A:L", which is then used to define the range in the "E" worksheet that is copied to the sheet specified in the loop. That's my best guess. Please tell us what's in cell B13 of the sheet named "Setup". — Doug Glancy
– Doug Glancy, Commented Jan 5, 2014 at 2:35

Guy F-W · Accepted Answer · 2014-01-07 14:46:50Z

0

Worksheets("E").Range("A2:Q82").Copy

Should do the trick if you're happy to hard code the range.

answered Jan 7, 2014 at 14:46

Guy F-W

1231 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Changing a Private variable OR defining a Range variable. Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related