insert three columns with empty cells and keeping formulas and borders

A

abdo meghari

Active Member
Joined
Aug 3, 2021
Messages
465
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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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
populate & update invoice information across sheets based on invoice number
Replies
0
Views
218
abdo meghari
A
A
  • Question
Highlight and delete duplicates items in the different spaces for multiple columns
Replies
1
Views
316
abdo meghari
A
A
  • Question
Match between two sheets and bring missed elements based on column and arrange again
Replies
0
Views
293
abdo meghari
A
A
  • Solved
add message & disable the code based on condition
2
Replies
16
Views
762
abdo meghari
A
A
  • Question
getting values from sheet to next based on empty cells
Replies
0
Views
265
abdo meghari
A

Forum statistics

Threads
1,215,501
Messages
6,125,169
Members
449,212
Latest member
kenmaldonado

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