Copy Area (Dynamic) XL2007

[ginsberg300]

Hello All,
My first post - I can't seem to find the answer on the boards so hope you can help

I have several work "rosters" on one sheet and I have a macro which copies the whole roster into another sheet:

Sheet3.Range("B2:P17").Copy Destination:=Sheet1.Range("A1")

This works fine, however I need to be able to copy just selected weeks of the roster via a user input.

The user will select a "roster" and a "start week" and I want to be able to copy that week and all the following weeks for a particular roster

E.g. in the example below, if the user selected Roster 1, Week 4 I would want to copy:

Sheet3.Range("A5:A7").Copy Destination:=Sheet1.Range("A1")
[i.e. Weeks 4,5 & 6]

Could anyone help me do this with "Roster" and "Week Number" as the user input?

Many Thanks
Scott

A B C D E

1 ¦ Week ¦ Mon ¦ Tue ¦ Wed ¦ Thu
2 ¦ 1 ¦ 08:00 ¦ 16:00 ¦ 08:00 ¦ 16:00 ¦
3 ¦ 2 ¦ 08:00 ¦ 16:00 ¦ 08:00 ¦ 16:00 ¦
4 ¦ 3 ¦ 08:00 ¦ 16:00 ¦ 08:00 ¦ 16:00 ¦
5 ¦ 4 ¦ 08:00 ¦ 16:00 ¦ 08:00 ¦ 16:00 ¦
6 ¦ 5 ¦ 08:00 ¦ 16:00 ¦ 08:00 ¦ 16:00 ¦
7 ¦ 6 ¦ 08:00 ¦ 16:00 ¦ 08:00 ¦ 16:00 ¦
8 ¦
9 ¦ Week ¦ Mon ¦ Tue ¦ Wed ¦ Thu
10 ¦ 1 ¦ 09:00 ¦ 16:00 ¦ 08:00 ¦ 17:00 ¦
11 ¦ 2 ¦ 09:00 ¦ 16:00 ¦ 08:00 ¦ 16:00 ¦
12 ¦ 3 ¦ 08:00 ¦ 17:00 ¦ 08:00 ¦ 16:00 ¦
13 ¦ 4 ¦ 08:00 ¦ 16:00 ¦ 08:00 ¦ 16:00 ¦
14 ¦ 5 ¦ 08:00 ¦ 16:00 ¦ 08:00 ¦ 17:00 ¦
15 ¦ 6 ¦ 09:00 ¦ 17:00 ¦ 08:00 ¦ 16:00 ¦
17 ¦ 7 ¦ 08:00 ¦ 16:00 ¦ 08:00 ¦ 16:00 ¦
18 ¦ 8 ¦ 08:00 ¦ 16:00 ¦ 08:00 ¦ 16:00 ¦

 

[doofusboy]

You mean something like this?

Dim intRoster As Integer
Dim intWeek As Integer
 
     If intRoster = 1 Then
          Sheet3.Range("A" & intweek + 1 & ":A7").Copy Destination:=Sheet1.Range("A1") 
     ElseIf intRoster = 2 Then
          Sheet3.Range("A" & intweek + 9 & ":A18").Copy Destination:=Sheet1.Range("A1")
     End If

 

[ginsberg300]

Perfect! Thank you!

I was unsure how to use a variable in a range but this solves that problem!