I'm Stumped on how to FillCopy

Lightkeepr

New Member
Joined
Apr 6, 2021
Messages
7
Office Version
  1. 365
Platform
  1. 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:
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
for the new columns for row 1?
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Can you show us an example of what your data looks like originally, and what your expected output should look like afterwards?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 

Lightkeepr

New Member
Joined
Apr 6, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Can you show us an example of what your data looks like originally, and what your expected output should look like afterwards?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Apparently on my work computer plug-ins are blocked by the group policy. When I get home, I'll try from my personal computer! In the mean time (if it helps) I uploaded a screen shot of the sheet.
 

Attachments

  • Screenshot 2021-04-06 135821.png
    Screenshot 2021-04-06 135821.png
    24 KB · Views: 4

Lightkeepr

New Member
Joined
Apr 6, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Can you show us an example of what your data looks like originally, and what your expected output should look like afterwards?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
Here is the Before:
New PM Manpower Board .xlsm
ABCDEFGHIJK
1Projected Man Hours Total for Bodies Shown: 1,000Week Ending4/4/20214/11/20214/18/20214/25/20215/2/2021END RANGE
2Total Man Hours per Week2002002002002000
3Available Hours per Week404040404040
4Total Bodies per Week555550
5School000000
6Vacation000000
7
8NameClassCrewProjectSortColumn1Column2Column3Column4Column5Column12
9Joe SchmoeAP0311619041611111
10John DoeAP0411619041511111
11Jane DoeAP0511619041411111
12Some DudeAP0711619041211111
13Some GalAP0711619041211111
Manpower
Cell Formulas
RangeFormula
A1A1=CONCATENATE("Projected Man Hours Total for Bodies Shown: ",TEXT(sumVisible(F2:K2),"#,##0"))
F2:K2F2=F3*F4
F4F4=SUBTOTAL(109,TBL_EMP_DATA[Column1])
G4G4=SUBTOTAL(109,TBL_EMP_DATA[Column2])
H4H4=SUBTOTAL(109,TBL_EMP_DATA[Column3])
I4I4=SUBTOTAL(109,TBL_EMP_DATA[Column4])
J4J4=SUBTOTAL(109,TBL_EMP_DATA[Column5])
K4K4=SUBTOTAL(109,TBL_EMP_DATA[Column12])
F5F5=SUMPRODUCT(SUBTOTAL(3,OFFSET(F9,ROW(TBL_EMP_DATA[Column1])-ROW(F9),0)),(TBL_EMP_DATA[Column1]="S")+0)
G5G5=SUMPRODUCT(SUBTOTAL(3,OFFSET(G9,ROW(TBL_EMP_DATA[Column2])-ROW(G9),0)),(TBL_EMP_DATA[Column2]="S")+0)
H5H5=SUMPRODUCT(SUBTOTAL(3,OFFSET(H9,ROW(TBL_EMP_DATA[Column3])-ROW(H9),0)),(TBL_EMP_DATA[Column3]="S")+0)
I5I5=SUMPRODUCT(SUBTOTAL(3,OFFSET(I9,ROW(TBL_EMP_DATA[Column4])-ROW(I9),0)),(TBL_EMP_DATA[Column4]="S")+0)
J5J5=SUMPRODUCT(SUBTOTAL(3,OFFSET(J9,ROW(TBL_EMP_DATA[Column5])-ROW(J9),0)),(TBL_EMP_DATA[Column5]="S")+0)
K5K5=SUMPRODUCT(SUBTOTAL(3,OFFSET(K9,ROW(TBL_EMP_DATA[Column12])-ROW(K9),0)),(TBL_EMP_DATA[Column12]="S")+0)
F6F6=SUMPRODUCT(SUBTOTAL(3,OFFSET(F9,ROW(TBL_EMP_DATA[Column1])-ROW(F9),0)),(TBL_EMP_DATA[Column1]="V")+0)
G6G6=SUMPRODUCT(SUBTOTAL(3,OFFSET(G9,ROW(TBL_EMP_DATA[Column2])-ROW(G9),0)),(TBL_EMP_DATA[Column2]="V")+0)
H6H6=SUMPRODUCT(SUBTOTAL(3,OFFSET(H9,ROW(TBL_EMP_DATA[Column3])-ROW(H9),0)),(TBL_EMP_DATA[Column3]="V")+0)
I6I6=SUMPRODUCT(SUBTOTAL(3,OFFSET(I9,ROW(TBL_EMP_DATA[Column4])-ROW(I9),0)),(TBL_EMP_DATA[Column4]="V")+0)
J6J6=SUMPRODUCT(SUBTOTAL(3,OFFSET(J9,ROW(TBL_EMP_DATA[Column5])-ROW(J9),0)),(TBL_EMP_DATA[Column5]="V")+0)
K6K6=SUMPRODUCT(SUBTOTAL(3,OFFSET(K9,ROW(TBL_EMP_DATA[Column12])-ROW(K9),0)),(TBL_EMP_DATA[Column12]="V")+0)
E9:E13E9=IFNA(INDEX(TBL_Codes[SortNumber],MATCH([@Class],TBL_Codes[CraftCode],0)),"")
Named Ranges
NameRefers ToCells
VAR_Class=TBL_Codes[CraftCode]E9:E13
Cells with Data Validation
CellAllowCriteria
B9:B13List=VAR_Class
C9:C13List=VAR_CrewID
D9:D13List=VAR_JobNumbers



And the After
New PM Manpower Board .xlsm
ABCDEFGHIJKLM
1Projected Man Hours Total for Bodies Shown: 1,000Week Ending4/4/20214/11/20214/18/20214/25/20215/2/20215/9/20215/16/2021END RANGE
2Total Man Hours per Week200200200200200000
3Available Hours per Week4040404040404040
4Total Bodies per Week55555000
5School00000000
6Vacation00000000
7
8NameClassCrewProjectSortColumn1Column2Column3Column4Column5Column52Column6Column12
9Joe SchmoeAP0311619041611111
10John DoeAP0411619041511111
11Jane DoeAP0511619041411111
12Some DudeAP0711619041211111
13Some GalAP0711619041211111
Manpower
Cell Formulas
RangeFormula
A1A1=CONCATENATE("Projected Man Hours Total for Bodies Shown: ",TEXT(sumVisible(F2:M2),"#,##0"))
F2:M2F2=F3*F4
F4F4=SUBTOTAL(109,TBL_EMP_DATA[Column1])
G4G4=SUBTOTAL(109,TBL_EMP_DATA[Column2])
H4H4=SUBTOTAL(109,TBL_EMP_DATA[Column3])
I4I4=SUBTOTAL(109,TBL_EMP_DATA[Column4])
J4J4=SUBTOTAL(109,TBL_EMP_DATA[Column5])
K4K4=SUBTOTAL(109,TBL_EMP_DATA[Column52])
L4L4=SUBTOTAL(109,TBL_EMP_DATA[Column6])
M4M4=SUBTOTAL(109,TBL_EMP_DATA[Column12])
F5F5=SUMPRODUCT(SUBTOTAL(3,OFFSET(F9,ROW(TBL_EMP_DATA[Column1])-ROW(F9),0)),(TBL_EMP_DATA[Column1]="S")+0)
G5G5=SUMPRODUCT(SUBTOTAL(3,OFFSET(G9,ROW(TBL_EMP_DATA[Column2])-ROW(G9),0)),(TBL_EMP_DATA[Column2]="S")+0)
H5H5=SUMPRODUCT(SUBTOTAL(3,OFFSET(H9,ROW(TBL_EMP_DATA[Column3])-ROW(H9),0)),(TBL_EMP_DATA[Column3]="S")+0)
I5I5=SUMPRODUCT(SUBTOTAL(3,OFFSET(I9,ROW(TBL_EMP_DATA[Column4])-ROW(I9),0)),(TBL_EMP_DATA[Column4]="S")+0)
J5J5=SUMPRODUCT(SUBTOTAL(3,OFFSET(J9,ROW(TBL_EMP_DATA[Column5])-ROW(J9),0)),(TBL_EMP_DATA[Column5]="S")+0)
K5K5=SUMPRODUCT(SUBTOTAL(3,OFFSET(K9,ROW(TBL_EMP_DATA[Column52])-ROW(K9),0)),(TBL_EMP_DATA[Column52]="S")+0)
L5L5=SUMPRODUCT(SUBTOTAL(3,OFFSET(L9,ROW(TBL_EMP_DATA[Column6])-ROW(L9),0)),(TBL_EMP_DATA[Column6]="S")+0)
M5M5=SUMPRODUCT(SUBTOTAL(3,OFFSET(M9,ROW(TBL_EMP_DATA[Column12])-ROW(M9),0)),(TBL_EMP_DATA[Column12]="S")+0)
F6F6=SUMPRODUCT(SUBTOTAL(3,OFFSET(F9,ROW(TBL_EMP_DATA[Column1])-ROW(F9),0)),(TBL_EMP_DATA[Column1]="V")+0)
G6G6=SUMPRODUCT(SUBTOTAL(3,OFFSET(G9,ROW(TBL_EMP_DATA[Column2])-ROW(G9),0)),(TBL_EMP_DATA[Column2]="V")+0)
H6H6=SUMPRODUCT(SUBTOTAL(3,OFFSET(H9,ROW(TBL_EMP_DATA[Column3])-ROW(H9),0)),(TBL_EMP_DATA[Column3]="V")+0)
I6I6=SUMPRODUCT(SUBTOTAL(3,OFFSET(I9,ROW(TBL_EMP_DATA[Column4])-ROW(I9),0)),(TBL_EMP_DATA[Column4]="V")+0)
J6J6=SUMPRODUCT(SUBTOTAL(3,OFFSET(J9,ROW(TBL_EMP_DATA[Column5])-ROW(J9),0)),(TBL_EMP_DATA[Column5]="V")+0)
K6K6=SUMPRODUCT(SUBTOTAL(3,OFFSET(K9,ROW(TBL_EMP_DATA[Column52])-ROW(K9),0)),(TBL_EMP_DATA[Column52]="V")+0)
L6L6=SUMPRODUCT(SUBTOTAL(3,OFFSET(L9,ROW(TBL_EMP_DATA[Column6])-ROW(L9),0)),(TBL_EMP_DATA[Column6]="V")+0)
M6M6=SUMPRODUCT(SUBTOTAL(3,OFFSET(M9,ROW(TBL_EMP_DATA[Column12])-ROW(M9),0)),(TBL_EMP_DATA[Column12]="V")+0)
E9:E13E9=IFNA(INDEX(TBL_Codes[SortNumber],MATCH([@Class],TBL_Codes[CraftCode],0)),"")
Named Ranges
NameRefers ToCells
VAR_Class=TBL_Codes[CraftCode]E9:E13
Cells with Data Validation
CellAllowCriteria
B9:B13List=VAR_Class
C9:C13List=VAR_CrewID
D9:D13List=VAR_JobNumbers
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hmmm...

I see what you mean. Unfortunately, I do not use tables very much, so I am not sure why it is behaving in that way, or to tell you how to avoid that from happening.
Hopefully someone who uses tables a bit more will be able to provide some guidance.
 

Lightkeepr

New Member
Joined
Apr 6, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hmmm...

I see what you mean. Unfortunately, I do not use tables very much, so I am not sure why it is behaving in that way, or to tell you how to avoid that from happening.
Hopefully someone who uses tables a bit more will be able to provide some guidance.

It might be my lack of knowledge in this as I am still learning and fairly new at this, but I don't understand what the table really has to do with this. The random column names are fine as they don't really matter. What I am looking for a better understanding on is, using VBA, how do I FillCopy rows 4, 5 and 6 from an existing column to the new columns that are being created? If FillCopy is used, the table header names that are created with the new columns are reflected in the formulas in rows 4-6. If I use the VBA version of copy/paste on those rows, the new table headers are not reflected in the formulas on 4-6 and use the exact formula from the cell being copied which is where I am stuck at currently.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

OK, after doing a bunch of testing, it looks like the issue is that ".FillRight" does a "copy", and therefore does NOT increment the table column references.
It looks like we can fix those formulas in rows 4-6 afterwards by doing an AutoFill, using the "FillValues" option.

So this seemed to work for me:
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(6, LastCols - 1)).Resize(, Qty + 1).FillRight
'  Fix formulas in rows 4-6 that use table references
   Range(Cells(4, LastCols - 1), Cells(6, LastCols - 1)).AutoFill Destination:=Range(Cells(4, LastCols - 1), Cells(6, LastCols + Qty)), Type:=xlFillValues

End Sub
 
Solution

Lightkeepr

New Member
Joined
Apr 6, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
OK, after doing a bunch of testing, it looks like the issue is that ".FillRight" does a "copy", and therefore does NOT increment the table column references.
It looks like we can fix those formulas in rows 4-6 afterwards by doing an AutoFill, using the "FillValues" option.

So this seemed to work for me:
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(6, LastCols - 1)).Resize(, Qty + 1).FillRight
'  Fix formulas in rows 4-6 that use table references
   Range(Cells(4, LastCols - 1), Cells(6, LastCols - 1)).AutoFill Destination:=Range(Cells(4, LastCols - 1), Cells(6, LastCols + Qty)), Type:=xlFillValues

End Sub
IT WORKS!!! THANK YOU FOR THE HELP!!

One more request from the original post, how do I get the dates at the top (row 1) to also increment automatically by 7 days for each column that was added?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
IT WORKS!!! THANK YOU FOR THE HELP!!

One more request from the original post, how do I get the dates at the top (row 1) to also increment automatically by 7 days for each column that was added?
You are welcome.

That is really a new question, and should be posted to a new thread, so it appears as a new unanswered question, and others will also see it.

The general rule of thumb is this:
1. If is a directly-related follow-up to the original question (meaning that it is imperative to know the answer/history of the previous question), then post it to the same thread.
2. If it is a different question (even if it is on the same project), where knowledge of the previous question is not necessary, then post it to a new thread.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,156
Messages
5,640,453
Members
417,143
Latest member
boukadidanizar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top