Lightkeepr
New Member
- Joined
- Apr 6, 2021
- Messages
- 27
- Office Version
- 365
- Platform
- Windows
I am stumped on how to have the VBA code FillCopy the values for rows 4,5 and 6 to the new inserted columns. I have been able to get it to essentially do a copy/paste of those rows in the new columns but I have dynamic values that I need to use FillCopy so it changes the formulas.
Here is where I have gotten to so far:
For an added bonus, how can I get the new cells to also do something like
for the new columns for row 1?
Here is where I have gotten to so far:
VBA Code:
Sub AddNewWeek()
'
' AddNewWeek Macro
'
Dim LastCols As Long
Dim Qty As Variant
Qty = InputBox("How many columns")
If Qty = "" Then Exit Sub
'Find last non-Blank cell in row 1)
LastCols = Cells(1, Columns.Count).End(xlToLeft).Column
'Insert new columns before last column on page
Columns(LastCols).Resize(, Qty).Insert
Range(Cells(2, LastCols - 1), Cells(3, LastCols - 1)).Resize(, Qty + 1).FillRight
End Sub
For an added bonus, how can I get the new cells to also do something like
Code:
Selection.DataSeries Rowcol:=xlRows, Type:=xlChronological, Date:=xlDay, _
Step:=7, Trend:=False