# button to show  week Pattern

on
in the DDWEEK sheet

button to show  all weeks up to TODAY'S
week would be added. as  the pattern shown

example

34W2017      B
35W2017      A
35W2017      B

follows
36W2017       A
36W2017      B
date_follow_to.xlsx
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Nevermind, I think I understand.
ACCOUNTING ASSISTANT

Commented:
Martin
When  click the button  the weeks 1 to 52 should be added.
by years

a great way could be  enter or select the year
then it create the pattern
ACCOUNTING ASSISTANT

Commented:
when i say the pattern i  mean

in this case this way

34W2017      B
35W2017      A
35W2017      B

follows
36W2017       A
36W2017      B

this file have some sample data but
the data should be create with the button itself
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
You may try something like this...

``````Sub DDWEEK()
Dim ws As Worksheet
Dim num As Long, i As Long, j As Long, n As Long, lr As Long
Dim numYear As Long
Dim x()

numYear = Application.InputBox("Enter the Year", "Year!", 2018, Type:=1)
If Len(numYear) <> 4 Then
MsgBox "Enter the Year in four digits.", vbExclamation
Exit Sub
End If

Set ws = Sheets("DDWEEK")
num = 52
ReDim x(1 To num * 2, 1 To 2)

For i = 1 To num * 2 Step 2
n = n + 1
For j = 1 To 1
x(i, 1) = n & "W" & numYear
x(i, 2) = "A"
x(i + j, 1) = n & "W" & numYear
x(i + j, 2) = "B"
Next j
Next i
lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
If lr > 2 Then ws.Range("A3:B" & lr).Clear
ws.Range("A3").Resize(UBound(x, 1), 2).Value = x
End Sub
``````
In the attached, click the button called "Week Pattern" to run the code.
date_follow_to.xlsm
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Try this.
29120762.xlsm
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
In my workbook you don't need to choose the year.
ACCOUNTING ASSISTANT

Commented:
Superior work.

Great  Expert !
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Glad it worked as desired. Thanks for the feedback.
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I'm sorry but I don't understand. In your question you said "button to show  all weeks up to TODAY'S" which since today's week is 40 that you wanted to add rows to the existing data up to 40 and then stopping there. Nerraj's code doesn't do that. It always replaces the data with a completely new years worth of data.

Lo siento pero no entiendo En su pregunta, dijo "botón para mostrar todas las semanas hasta HOY", que desde la semana de hoy es 40, usted quiso agregar filas a los datos existentes hasta 40 y luego detenerse allí. El código de Nerraj no hace eso. Siempre reemplaza los datos con un valor de años completamente nuevo.
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
The workbook that I attached had a problem if you already had a full year of data and this corrects that.
29120762a.xlsm
ACCOUNTING ASSISTANT

Commented:
Martin

Great JOb

i explain here

Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Well I didn't notice that change from your original question, but please be aware that in Neeraj's code if you already have a full year of 2018 weeks and you say the year you want is 2019, the existing weeks will be replaced by 2019 weeks rather than adding 2019 weeks to the existing data.

No noté el cambio en su pregunta original, pero tenga en cuenta que en el código de Neeraj, si ya tiene un año completo de 2018 semanas y dice que el año que quiere es 2019, las semanas existentes se reemplazarán por 2019 semanas en lugar de que agregar 2019 semanas a los datos existentes.

Do more with