button to show week Pattern

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
ADRIANA PACCOUNTING ASSISTANTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
I need more information about what you need.
0
Martin LissOlder than dirtCommented:
Nevermind, I think I understand.
0
ADRIANA PACCOUNTING ASSISTANTAuthor 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
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ADRIANA PACCOUNTING ASSISTANTAuthor 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
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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

Open in new window

In the attached, click the button called "Week Pattern" to run the code.
date_follow_to.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
Try this.
29120762.xlsm
0
Martin LissOlder than dirtCommented:
In my workbook you don't need to choose the year.
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Superior work.

Great  Expert !
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Glad it worked as desired. Thanks for the feedback.
0
Martin LissOlder than dirtCommented:
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.
0
Martin LissOlder than dirtCommented:
The workbook that I attached had a problem if you already had a full year of data and this corrects that.
29120762a.xlsm
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Martin

Great JOb

i explain here

c1111.PNG
0
Martin LissOlder than dirtCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.