Sep 02 2022 12:27 AM - edited Sep 02 2022 12:29 AM
I have 3 spreadsheets labeled 'lesson 1', 'lesson 2', and 'lesson 3'. I made a 4th spreadsheet labeled 'planner sheet'.
In the first cell in the 'planner sheet' I put the formula:
={FILTER('lesson 1'!B:Z, 'lesson 1'!A:A = WEEKNUM(TODAY())); FILTER('lesson 2'!B:Z, 'lesson 2'!A:A = WEEKNUM(TODAY())); FILTER('lesson 3'!B:Z, 'lesson 3'!A:A = WEEKNUM(TODAY()))}
What this is supposed to do is grab all the information in the lesson sheets cells B through Z based on the A column that has a numerical value of the week number and put them into the 'planner sheet'.
What I end up getting is just a red box in the 'planner sheet' after entering the formula.
Any help is much appreciated. Thank you!
Sep 02 2022 01:07 AM
Sep 02 2022 01:51 AM - edited Sep 02 2022 01:56 AM
@jenjers If you have access to VSTACK() function then could try.
=VSTACK(FILTER('Lesson 1'!B:Z,'Lesson 1'!A:A=WEEKNUM(TODAY())),FILTER('Lesson 2'!B:Z,'Lesson 2'!A:A=WEEKNUM(TODAY())),FILTER('Lesson 3'!B:Z,'Lesson 3'!A:A=WEEKNUM(TODAY())))
See the attached file.
Sep 02 2022 02:21 AM
Sub CurrentWeekData()
Dim WBK As Workbook
Dim WKS As Worksheet
Dim i As Long
Dim wn As Long
Dim j As Long
Dim k As Long
Dim m As Integer
Worksheets("planner sheet").Range("A2:Z1048576").Clear
k = 2
wn = Format(Date, "ww")
For Each WKS In ThisWorkbook.Worksheets
If WKS.Name <> "planner sheet" Then
i = WKS.Range("A" & Rows.Count).End(xlUp).Row
For j = 2 To i
If WKS.Cells(j, 1).Value = wn Then
For m = 1 To 26
Worksheets("planner sheet").Cells(k, m).Value = WKS.Cells(j, m).Value
Next m
k = k + 1
Else
End If
Next j
End If
Next WKS
End Sub
Maybe with these lines of code. In the attached file you can click the button in cell AB2 to run the macro.