• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 80
  • Last Modified:

create template

button to create the  Week date  am/pm and relative spaces

AQWE2.PNG29076215a--2-.xlsm
0
ADRIANA P
Asked:
ADRIANA P
  • 3
  • 3
1 Solution
 
Ryan ChongCommented:
button to create the  Week date  am/pm and relative spaces
can you explain further how you wish these columns to be generated?

do you only need to create for 1 week (append from last used column)?
OR the whole series from col C to col DU?

any logic to share with?
0
 
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Ryan Chong

append from last used column

need add one week as it needed ( when finish week 1 then goes to create week 2)

is an 31 sequence
0
 
Ryan ChongCommented:
to make it easier, I have created a template to be used, and then using codes below:

Sub CreateTemplate()
    Dim lastCol As Integer, rowNo As Integer, extraRow As Integer
    Dim lastDate As String
    
    rowNo = 5
    extraRow = 3
    lastCol = ActiveSheet.Cells(rowNo, ActiveSheet.Columns.Count).End(xlToLeft).Column
    
    Sheets("Template").Range("A1:AC200").Copy ActiveSheet.Range(ToColletter(lastCol + extraRow) & "1")
    Sheets("Template").Range("A1:AC200").Copy
    
    ActiveSheet.Range(ToColletter(lastCol + extraRow) & "1").PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    'Default to first week
    If lastCol < 9 Then
        'need handle this?
    End If
    
    lastDate = Cells(rowNo, ToColletter(lastCol - 8))
    'Mon
    Cells(rowNo, ToColletter(lastCol + extraRow)) = NextMonday(lastDate)
    'Tue
    Cells(rowNo, ToColletter(lastCol + extraRow + 4)) = NextMonday(lastDate) + 1
    'Wed
    Cells(rowNo, ToColletter(lastCol + extraRow + 8)) = NextMonday(lastDate) + 2
    'Thu
    Cells(rowNo, ToColletter(lastCol + extraRow + 12)) = NextMonday(lastDate) + 3
    'Fri
    Cells(rowNo, ToColletter(lastCol + extraRow + 16)) = NextMonday(lastDate) + 4
    'Sat
    Cells(rowNo, ToColletter(lastCol + extraRow + 20)) = NextMonday(lastDate) + 5
    
    'Week number
    Cells(rowNo - 1, ToColletter(lastCol + extraRow)) = Format(NextMonday(lastDate), "ww") & "w" & Year(NextMonday(lastDate))
    Cells(rowNo - 1, ToColletter(lastCol + extraRow + 24)) = Cells(rowNo, ToColletter(lastCol + extraRow)) & "T"
    
End Sub

Public Function ToColletter(ByVal Collet As Integer) As String
    ToColletter = Split(Cells(1, Collet).Address, "$")(1)
End Function

Public Function NextMonday(ByVal v As Date) As Date
    NextMonday = v + (8 - Weekday(v, vbMonday))
End Function

Open in new window


see if this make sense?

this seems not a complete solution but could be good enough for you to populate the "template" you needed
29076215a--2-_b.xlsm
1
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Ryan Chong Thanks for the great Job !

How i run the code to  create the template ?
0
 
Ryan ChongCommented:
How i run the code to  create the template ?
you can press Alt + F8 to show the Macro window, and then select the CreateTemplate macro and click Run.

Untitled1.png
1
 
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Great Job Expert
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now