insert three columns with empty cells and keeping formulas and borders

A

abdo meghari

Well-known Member
Joined
Aug 3, 2021
Messages
651
Office Version
  1. 2019
Hi experts

I want inserting new month contains three columns with the same borders and formula , but the cells should be empty for two columns arrived & sales . the formula for new inserted month for column stock for the last month depends on previous month .
so should insert new month even if enter new year without any problem .
last thing if there is way to deal merged cells for the header will be great , otherwise I will cancel it .
orginal

Bridgestone Stock.xls
ABCDEFGHI
1septemberoctober
2SizePatternOriginArrivedSalesStock Arrived SalesStock
3BS 175/70R13B25A32INDO-16184-5179
4BS 175/70R14MY02THI--296-5291
5BS 185/65R14TECTHI------
6BS 185/65R14EP150INDO------
7BS 195/60R15150EZTHI------
8BS 195/65R15MY02THI-50361--361
9BS 205/65R15MY02THI-8668--668
10BS 205/65R15T001JAP------
11BS 205/65R15T005THI------
12FS 205/65R15TZ700JAP------
13BS 205/70R15T694JAP--16--16
14BS 215/65R15T005INDO------
15BS 265/70R15D697INDO------
16BS 225/75R15CT697INDO------
17BS 235/95R15CD618JAP--56--56
18BS 255/70R15CD840THI-661329--1329
19FS 31*10.50R15 DEST ATJAP------
20BS 205R16CD840THI------
21BS 205/R16CD697THI-4293--293
22BS 205/55R16AR20INDO-1----
23BS 205/55R16G3JAP-4----
24BS 205/55R16T001------
25BS 205/55R16ER300POL------
26BS 205/55R16ER30JAP------
27TTL-1493203-103193
28BS 225/45R17RE050AJAP--26--26
29BS 225/45R17EA03JAP------
30BS 225/60R17D-SPORTJAP------
31BS 225/65R17T697INDO--24--24
32BS 225/70R17D697THI--50--50
33BS 225/70R17D697JAP------
34BS 235/45R17EA03THI------
35BS 235/55R17ER30JAP--40--40
36BS 235/55R17EP300JAP------
37BS 235/55R17T005THI------
38BS 235/55R17D400JAP--12--12
39BS 235/60R17D400JAP------
40BS 235/60R17AL01JAP------
41BS 235/65R17T697INDO--8--8
42BS 245/65R17D697INDO--24--24
43BS 245/65R17XLD697INDO------
44BS 245/70R17D684JAP------
45BS 245/75R17693AJAP--981--981
46BS 255/55R17EP300THI------
47BS 225/40R18GR90JAP--32--32
48BS 225/40R18GR90INDO--28--28
49BS 225/40R18RE050AJAP------
50BS 225/40R18RE050ATHI------
51BS 225/45R18AR20INDO--22--22
52TTL--1247--1247
53BS 650R16R230JAP-8219-8211
54BS 700R16R230JAP-3161193-1351058
55BS 750R16R230JAP-12219105-1408965
56BS 155R12CR624INDO------
57TTL-154510517-28310234
58BS 750R16VSJJAP-444939-1204819
59BS 825R16R180JAP------
60TTL-444939-1204819
61BS 1200R20G580JAP-35702641-1653988
62BS 1200R20R187JAP-607481-41863
63BS 445/65R22.5R164JAP------
64BS 1200R24G580JAP-135166-1642
65BS 1200R24G582JAP------
66DT 1200R24DA53THI------
67FS 1200R24FSR4000JAP------
68BS 325/95R24G582JAP------
69BS 325/95R24M840JAP------
70TTL-43123288-22351053
missed
Cell Formulas
RangeFormula
I64:I65,I61:I62,I58,I53:I55,I51,I45:I48,I41:I42,I38,I34:I35,I31:I32,I28,I21:I24,I17:I18,I15,I13,I3:I9I3=F3+G3-H3
D52:I52,D27:I27D27=SUM(D3:D26)
E54E54=310+6
D57:I57D57=SUM(D53:D56)
D60:I60D60=SUM(D58:D59)
E62E62=586+21
F65F65=D65-E65
D70:I70D70=SUM(D61:D69)


result
Bridgestone Stock Sales.xls
ABCDEFGHIJKL
1septemberoctobernovember
2SizePatternOriginArrivedSalesStock Arrived SalesStock Arrived SalesStock
3BS 175/70R13B25A32INDO-16184-5179179
4BS 175/70R14MY02THI--296-5291291
5BS 185/65R14TECTHI-------
6BS 185/65R14EP150INDO-------
7BS 195/60R15150EZTHI-------
8BS 195/65R15MY02THI-50361--361361
9BS 205/65R15MY02THI-8668--668668
10BS 205/65R15T001JAP-------
11BS 205/65R15T005THI-------
12FS 205/65R15TZ700JAP-------
13BS 205/70R15T694JAP--16--1616
14BS 215/65R15T005INDO-------
15BS 265/70R15D697INDO-------
16BS 225/75R15CT697INDO-------
17BS 235/95R15CD618JAP--56--5656
18BS 255/70R15CD840THI-661329--13291329
19FS 31*10.50R15 DEST ATJAP-------
20BS 205R16CD840THI-------
21BS 205/R16CD697THI-4293--293293
22BS 205/55R16AR20INDO-1-----
23BS 205/55R16G3JAP-4-----
24BS 205/55R16T001-------
25BS 205/55R16ER300POL-------
26BS 205/55R16ER30JAP-------
27TTL-1493203-103193--3193
28BS 225/45R17RE050AJAP--26--2626
29BS 225/45R17EA03JAP-------
30BS 225/60R17D-SPORTJAP-------
31BS 225/65R17T697INDO--24--2424
32BS 225/70R17D697THI--50--5050
33BS 225/70R17D697JAP-------
34BS 235/45R17EA03THI-------
35BS 235/55R17ER30JAP--40--4040
36BS 235/55R17EP300JAP-------
37BS 235/55R17T005THI-------
38BS 235/55R17D400JAP--12--1212
39BS 235/60R17D400JAP-------
40BS 235/60R17AL01JAP-------
41BS 235/65R17T697INDO--8--88
42BS 245/65R17D697INDO--24--2424
43BS 245/65R17XLD697INDO-------
44BS 245/70R17D684JAP-------
45BS 245/75R17693AJAP--981--981981
46BS 255/55R17EP300THI-------
47BS 225/40R18GR90JAP--32--3232
48BS 225/40R18GR90INDO--28--2828
49BS 225/40R18RE050AJAP-------
50BS 225/40R18RE050ATHI-------
51BS 225/45R18AR20INDO--22--2222
52TTL--1247--1247--1247
53BS 650R16R230JAP-8219-8211211
54BS 700R16R230JAP-3161193-13510581058
55BS 750R16R230JAP-12219105-14089658965
56BS 155R12CR624INDO-------
57TTL-154510517-28310234--10234
58BS 750R16VSJJAP-444939-12048194819
59BS 825R16R180JAP-------
60TTL-444939-1204819--4819
61BS 1200R20G580JAP-35702641-1653988988
62BS 1200R20R187JAP-607481-4186363
63BS 445/65R22.5R164JAP-------
64BS 1200R24G580JAP-135166-16422
65BS 1200R24G582JAP-------
66DT 1200R24DA53THI-------
67FS 1200R24FSR4000JAP-------
68BS 325/95R24G582JAP-------
69BS 325/95R24M840JAP-------
70TTL-43123288-22351053--1053
missed
Cell Formulas
RangeFormula
L64:L65,I64:I65,L61:L62,I61:I62,L58,I58,L53:L55,L51,I53:I55,I51,L45:L48,I45:I48,L41:L42,I41:I42,L38,I38,L34:L35,I34:I35,L31:L32,I31:I32,L28,I28,L21:L24,I21:I24,L17:L18,I17:I18,L15,I15,L13,I13,L8:L9,I3:I9,L3:L4L3=I3+J3-K3
D52:L52,D27:L27D27=SUM(D3:D26)
E54E54=310+6
D57:L57D57=SUM(D53:D56)
D60:L60D60=SUM(D58:D59)
E62E62=586+21
F65F65=D65-E65
D70:L70D70=SUM(D61:D69)

thanks
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Try this....
VBA Code: 
Sub Insert_Columns()

Dim sht As Worksheet
Dim LastColumn As Long
Dim LastColumnLetter As String
Dim FirstColumnLetter As String
Dim NewLastColumnLetter As String
Dim NewFirstColumnLetter As String
Dim dte As Date

ScreenUpdating = False

Set sht = ThisWorkbook.ActiveSheet

    ' Get the last populated column number
    LastColumn = sht.Cells(2, sht.Columns.Count).End(xlToLeft).Column
    ' Convert this to the column letter i.e. last column to copy
    LastColumnLetter = Split(Cells(2, LastColumn).Address, "$")(1)
    ' Get the column letter of the first column to copy
    FirstColumnLetter = Split(Cells(2, LastColumn - 2).Address, "$")(1)
    ' Get the column letter of the first new column
    NewFirstColumnLetter = Split(Cells(2, LastColumn + 1).Address, "$")(1)
    ' Get the column letter of the last new column
    NewLastColumnLetter = Split(Cells(2, LastColumn + 3).Address, "$")(1)
    ' Select the range to copy
    Columns(FirstColumnLetter & ":" & LastColumnLetter).Select
    ' Copy the range
    Selection.Copy
    ' Copy the selected columns to the right of their current location
    sht.Range(NewFirstColumnLetter & 1).Select
    sht.Paste
    ' Select the newly copied columns
    Columns(NewFirstColumnLetter & ":" & NewLastColumnLetter).Select
    ' Clear the contents of the newly copied columns except for formulas
    Selection.SpecialCells(xlConstants).ClearContents
    ' Repopulate the column headers
    Cells(2, NewFirstColumnLetter).Value = "Arrived"
    Cells(2, NewLastColumnLetter).Value = "Stock"
    Cells(2, Split(Cells(1, LastColumn + 2).Address, "$")(1)).Value = "Sales"
    Cells(3, NewFirstColumnLetter).Select
    'Populate the Month column header
    dte = DateValue("01-" & Cells(1, LastColumn - 2).Value)
    Cells(1, NewFirstColumnLetter).Value = Format(DateSerial(Year(dte), Month(dte) + 1, 1), "mmmm")

ScreenUpdating = True

End Sub
 
Upvote 0
thanks but it gives mismatch error in this line
VBA Code: 
 dte = DateValue("01-" & Cells(1, LastColumn - 2).Value)
 
Upvote 0
That is an issue with the date format.
What is your local date format?
What is the format of cells in row 1?
What is the value in cells in row 1?
i.e. is D1 = September or does it contain a date like 09/01/22 (representing 01st Sep 22) with a custom format of "mmm"?
 
Upvote 0
sparky2205 said:
dte = DateValue("01-" & Cells(1, LastColumn - 2).Value)
Click to expand...
Try replacing the above with:
Excel Formula: 
dte = DateValue(Cells(1, LastColumn - 2).Value & "-01")
Unfortunately, I don't have the option to change the local date settings on my pc, so it makes it very difficult for me to check this.
 
Upvote 0
sparky2205 said:
dte = DateValue("01-" & Cells(1, LastColumn - 2).Value) Cells(1, NewFirstColumnLetter).Value = Format(DateSerial(Year(dte), Month(dte) + 1, 1), "mmmm")
Click to expand...
Alas, I'm out of ideas on how to fix this without being able to test it. One of the other guys may see your post and be able to fix this piece.
Until then, your other option is to comment out those 2 lines and fill in the header manually.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
populate & update invoice information across sheets based on invoice number
Replies
0
Views
343
abdo meghari
A
A
  • Question Question
Highlight and delete duplicates items in the different spaces for multiple columns
Replies
1
Views
441
abdo meghari
A
A
  • Question Question
Match between two sheets and bring missed elements based on column and arrange again
Replies
0
Views
361
abdo meghari
A
A
  • Solved
add message & disable the code based on condition
2
Replies
16
Views
945
abdo meghari
A
A
  • Question Question
Data Selection Based on Closest Value [20% Tolerance]
Replies
1
Views
211
StephenCrump
S

Forum statistics

Threads
1,224,828
Messages
6,181,217
Members
453,024
Latest member
Wingit77

Share this page

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
Back
Top