A little VBA help please: collect row value not column

[Preschool Mike]

I need to change this code so it collects the information from the row and
place it in the appropriate row. As it is written now it is doing the
opposite - collecting the information from the column down and placing it in
another column down.

Specifically it needs to: Collect infor from Sheets(Lunch and Attendance,
cells AD7 thru BH7) and place it in Sheets (Attendance1, cells H30 thru AL
30) if the current month in Sheets (Lunch and Attendance, cell AF4) is
August. If the month would be September then it will do the same except
collect and store the information in the rows below August and so fourth.

Here's my current code:
Sub EnterYearlyAttendanceRecord()
Select Case LCase(Range("'Lunch and Attendance'!AF4"))
Case "august" 'type month in lower case
Case "september"
Case "october"
Case Else
End Select
'August
Sheets("Attendance1").Cells(30, "h").Resize(31).Value = _
Sheets("Lunch and Attendance").Cells(7, "ad").Resize(31).Value
End Sub

 

[Jacob Skaria]

Try the below

Sheets("Attendance1").Cells(30, "h").Resize(1, 31).Value = _
WorksheetFunction.Transpose(Sheets("Lunch and Attendance").Cells(7, _
"ad").Resize(31))

If this post helps click Yes

 

[Don Guillett]

A cursory look suggests that all you need to do is put a , in front of the
31
Sheets("Attendance1").Cells(30, "h").Resize(,31).Value = _
Sheets("Lunch and Attendance").Cells(7, "ad").Resize(,31).Value

 

[Bernie Deitrick]

Mike,

From the looks of it, you want to store August in row 7, September in row 8, etc. If so, then try:

Sub EnterYearlyAttendanceRecord2()
Dim monOffset As Integer

monOffset = Month(DateValue(Range("'Lunch and Attendance'!AF4") & " 1, " & Year(Now())))
If monOffset > 7 Then
monOffset = monOffset - 8
Else
monOffset = monOffset + 4
End If

Sheets("Attendance1").Cells(30, "h").Resize(31).Value = _
Sheets("Lunch and Attendance").Cells(7 + monOffset, "ad").Resize(31).Value
End Sub

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Oops - I missed what Don saw - so make sure you change each 31 to ,31

HTH,
Bernie
MS Excel MVP

Mike,

From the looks of it, you want to store August in row 7, September in row 8, etc. If so, then
try:

Sub EnterYearlyAttendanceRecord2()
Dim monOffset As Integer

monOffset = Month(DateValue(Range("'Lunch and Attendance'!AF4") & " 1, " & Year(Now())))
If monOffset > 7 Then
monOffset = monOffset - 8
Else
monOffset = monOffset + 4
End If

Sheets("Attendance1").Cells(30, "h").Resize(31).Value = _
Sheets("Lunch and Attendance").Cells(7 + monOffset, "ad").Resize(31).Value
End Sub

HTH,
Bernie
MS Excel MVP

 

[Preschool Mike]

Sorry but this code did not work, but Don's suggestion did. Thanks for your
time.