Range problem

[kapfrank]

I've made a workbook with 13 Worksheets in it.
The first 12 are the months (Jan, Feb, Mar......)
The last one is a daily report (Sport).

The code starts in Worksheet(Sport)
Depending which day it's now (18-2-2008) the code has to go to the right month (Feb) and the right day (18) and copying a specific range.

The problem is that he goes to the right month but he copies the range from Worksheet(Sport).

Private Sub Sportrooster()
    Dim Day As Long
    Dim Month As Long
    
    Day = Range("J3") + 8
    Month = Range("J4")
    If Month = 1 Then
        Worksheets("Jan").Select
        Range(Cells(17, Day), Cells(130, Day)).Copy
        Sheets("Sport").Select
        Range("G11").Select
        ActiveSheet.Paste
        Else
        If Month = 2 Then
            Worksheets("Feb").Select
            Range(Cells(17, Day), Cells(130, Day)).Copy
            Sheets("Sport").Select
            Range("G11").Select
            ActiveSheet.Paste
            Else
            If Month = 3 Then
               Worksheets("Mar").Select
               Range(Cells(17, Day), Cells(130, Day)).Copy
               Sheets("Sport").Select
               Range("G11").Select
               ActiveSheet.Paste
               Else
               Exit Sub
            End If
        End If
    End If
End Sub

 

[VoG]

You would probably be better off not selecting worksheets or ranges. Try something like

If Month = 1 Then
    Worksheets("Jan").Range(Cells(17, Day), Cells(130, Day)).Copy Destination:=Sheets("Sport").Range("G11")
Else

 

[Norie]

I would seriously recommend you don't use Day/Month as variables, they are VBA functions.

I don't know if that's what's causing the problem but it's not a good idea.

I would also suggest you fully qualify your range references.

 

[VoG]

In fact, why not

Option Base 1

Private Sub Sportrooster()
Dim iDay As Integer, iMonth As Integer, ws As Worksheet, AllMonths
AllMonths = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
iDay = Sheets("Sport").Range("J3").Value + 8
iMonth = Sheets("Sport").Range("J4").Value
Set ws = Sheets(AllMonths(iMonth))
ws.Range(Cells(17, iDay), Cells(130, iDay)).Copy Destination:=Sheets("Sport").Range("G11")
End Sub

 

[kapfrank]

Option Base 1

Private Sub Sportrooster()
Dim iDay As Integer, iMonth As Integer, ws As Worksheet, AllMonths
AllMonths = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
iDay = Sheets("Sport").Range("J3").Value + 8
iMonth = Sheets("Sport").Range("J4").Value
Set ws = Sheets(AllMonths(iMonth))
ws.Range(Cells(17, iDay), Cells(130, iDay)).Copy Destination:=Sheets("Sport").Range("G11")
End Sub

I get an error 1004 on the last command "ws.Range(Cells(17...........Range("G11")"
And why dosn't have AllMonths no variable?

 

[VoG]

I apologise - try this. AllMonths didn't have a data type since it is implicitly a Variant if no data type is specified. I've typed it explicitly in the code below:

Option Base 1

Sub Sportrooster()
Dim iDay As Integer, iMonth As Integer, ws As Worksheet, AllMonths As Variant
AllMonths = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
iDay = Sheets("Sport").Range("J3").Value + 8
iMonth = Sheets("Sport").Range("J4").Value
Set ws = Sheets(AllMonths(iMonth))
Range(ws.Cells(17, iDay), ws.Cells(130, iDay)).Copy Destination:=Sheets("Sport").Range("G11")
End Sub

 

[kapfrank]

VoG II
It's still not working.
What am i doing wrong?
Hereby the testfile:
http://www.kapfrank.nl/temp/Kalender1.xls
Thank you for your help so far.

 

[VoG]

The problem is that you had the code in the Sport sheet code module. This code should go in a regular module - in the Visual Basic Editor, Insert > Module.

Sub Sportrooster()
    Dim iDay As Integer, iMonth As Integer, ws As Worksheet, AllMonths As Variant
    
    AllMonths = Array("Jan", "Feb", "Mrt")
    
    iDay = Sheets("Sport").Range("D1").Value + 2
    iMonth = Sheets("Sport").Range("D2").Value
    
    Set ws = Sheets(AllMonths(iMonth))
    
    Range(ws.Cells(10, iDay), ws.Cells(18, iDay)).Copy Destination:=Sheets("Sport").Range("B10")

End Sub

I've tested this with your sample file and it works fine for me.

 

[kapfrank]

You are absolute right.
It’s working fine.

Do you know why this code doesn’t work in a worksheet?

 

[Norie]

When you use a combination of Range and Cells, without a worksheet reference for Range then VBA will assume Range is on the worksheet the code is in.

Now in this case when you reference Cells you are referring to different worksheets.

That's just not going to work.