insert column and autofill based on cell value

A

Ali M

Active Member
Joined
Oct 10, 2021
Messages
316
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
Hello I would insert column after column B and auto fill name until lastrow with ignore TOTAL row based on H1
DD.xlsm
ABCDEFGH
1DATEDETAILSSAFESDEBITCREDITBALANCEALI
220/03/2024SALES INVOICEBANK310003100
320/03/2024SALES INVOICESHOP SAFE480007900
420/03/2024SALES INVOICEHOME SAFE4200012100
520/03/2024RECEIVED CASHBANK3000015100
620/03/2024PAID CASHSHOP SAFE0440010700
720/03/2024PAID CASHHOME SAFE027008000
821/03/2024SALES INVOICESHOP SAFE100001000
921/03/2024SALES INVOICEHOME SAFE120002200
1021/03/2024PAID CASHSHOP SAFE022000
1121/03/2024RECEIVED CASHHOME SAFE1000011008900
1222/03/2024RECEIVED CASHBANK300003000
1323/03/2024RECEIVED CASHHOME SAFE200002000
1423/03/2024RECEIVED CASHBANK500007000
1523/03/2024PAID CASHSHOP SAFE3000010000
16TOTAL0029900
A
Cell Formulas
RangeFormula
D16:E16D16=SUM(L2:L15)



RESULT
DD.xlsm
ABCDEFGHI
1DATEDETAILSNAMESAFESDEBITCREDITBALANCEALI
220/03/2024SALES INVOICEALIBANK310003100
320/03/2024SALES INVOICEALISHOP SAFE480007900
420/03/2024SALES INVOICEALIHOME SAFE4200012100
520/03/2024RECEIVED CASHALIBANK3000015100
620/03/2024PAID CASHALISHOP SAFE0440010700
720/03/2024PAID CASHALIHOME SAFE027008000
821/03/2024SALES INVOICEALISHOP SAFE100001000
921/03/2024SALES INVOICEALIHOME SAFE120002200
1021/03/2024PAID CASHALISHOP SAFE022000
1121/03/2024RECEIVED CASHALIHOME SAFE1000011008900
1222/03/2024RECEIVED CASHALIBANK300003000
1323/03/2024RECEIVED CASHALIHOME SAFE200002000
1423/03/2024RECEIVED CASHALIBANK500007000
1523/03/2024PAID CASHALISHOP SAFE3000010000
16TOTAL0029900
A
Cell Formulas
RangeFormula
E16:F16E16=SUM(M2:M15)

when run the macro I don't repeat insert column every time
thanks
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
Try:
VBA Code: 
Sub InsertColumn()
    Dim lRow As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Columns("C:C").Insert Shift:=xlToRight
    Range("C1") = "NAME"
    Range("C2:C" & lRow - 1) = Range("I1")
End Sub
 
Upvote 0
this is what I got
ورقة عمل Microsoft Excel جديد ‫(2)‬.xlsx
ABCDEFGHIJKLMNO
1DATEDETAILSNAMENAMENAMENAMENAMENAMENAMESAFESDEBITCREDITBALANCEALI
220/03/2024SALES INVOICENAMESAFESDEBITCREDITBALANCEALIBANK310003100
320/03/2024SALES INVOICENAMESAFESDEBITCREDITBALANCEALISHOP SAFE480007900
420/03/2024SALES INVOICENAMESAFESDEBITCREDITBALANCEALIHOME SAFE4200012100
520/03/2024RECEIVED CASHNAMESAFESDEBITCREDITBALANCEALIBANK3000015100
620/03/2024PAID CASHNAMESAFESDEBITCREDITBALANCEALISHOP SAFE0440010700
720/03/2024PAID CASHNAMESAFESDEBITCREDITBALANCEALIHOME SAFE027008000
821/03/2024SALES INVOICENAMESAFESDEBITCREDITBALANCEALISHOP SAFE100001000
921/03/2024SALES INVOICENAMESAFESDEBITCREDITBALANCEALIHOME SAFE120002200
1021/03/2024PAID CASHNAMESAFESDEBITCREDITBALANCEALISHOP SAFE022000
1121/03/2024RECEIVED CASHNAMESAFESDEBITCREDITBALANCEALIHOME SAFE1000011008900
1222/03/2024RECEIVED CASHNAMESAFESDEBITCREDITBALANCEALIBANK300003000
1323/03/2024RECEIVED CASHNAMESAFESDEBITCREDITBALANCEALIHOME SAFE200002000
1423/03/2024RECEIVED CASHNAMESAFESDEBITCREDITBALANCEALIBANK500007000
1523/03/2024PAID CASHNAMESAFESDEBITCREDITBALANCEALISHOP SAFE3000010000
16TOTAL0029900
17
ورقة1
Cell Formulas
RangeFormula
K16:L16K16=SUM(S2:S15)

doesn't seem to read this
when run the macro I don't repeat insert column every time
Click to expand...
 
Upvote 0
The macro was designed to work with the data you posted in your original post. The data in post #3 is entirely different. Please explain in detail what you want to do referring to specific cells, rows and columns using the data in Post #3.
 
Upvote 0
I don't run one time , sometimes user run and the column has already inserted so if I change name the NAME column is existed then just replace name without insert NAME column again.
 
Upvote 0
You have "NAME" in multiple columns which is very confusing. Will the name column always be in column C? If you change the name, in which column will the change take place?
 
Upvote 0
Will the name column always be in column C
Click to expand...
that's correct.
If you change the name, in which column will the change take place?
Click to expand...
in the column C based on change H1
example
ورقة عمل Microsoft Excel جديد ‫(2)‬.xlsx
ABCDEFGH
1DATEDETAILSNAMESAFESDEBITCREDITBALANCEALI
220/03/2024SALES INVOICEALIBANK310003100
320/03/2024SALES INVOICEALISHOP SAFE480007900
420/03/2024SALES INVOICEALIHOME SAFE4200012100
520/03/2024RECEIVED CASHALIBANK3000015100
620/03/2024PAID CASHALISHOP SAFE0440010700
720/03/2024PAID CASHALIHOME SAFE027008000
821/03/2024SALES INVOICEALISHOP SAFE100001000
921/03/2024SALES INVOICEALIHOME SAFE120002200
1021/03/2024PAID CASHALISHOP SAFE022000
1121/03/2024RECEIVED CASHALIHOME SAFE1000011008900
1222/03/2024RECEIVED CASHALIBANK300003000
1323/03/2024RECEIVED CASHALIHOME SAFE200002000
1423/03/2024RECEIVED CASHALIBANK500007000
1523/03/2024PAID CASHALISHOP SAFE3000010000
16TOTAL0029900
ورقة2
Cell Formulas
RangeFormula
E16:F16E16=SUM(M2:M15)



result
ورقة عمل Microsoft Excel جديد ‫(2)‬.xlsx
ABCDEFGH
1DATEDETAILSNAMESAFESDEBITCREDITBALANCEOMAR
220/03/2024SALES INVOICEOMARBANK310003100
320/03/2024SALES INVOICEOMARSHOP SAFE480007900
420/03/2024SALES INVOICEOMARHOME SAFE4200012100
520/03/2024RECEIVED CASHOMARBANK3000015100
620/03/2024PAID CASHOMARSHOP SAFE0440010700
720/03/2024PAID CASHOMARHOME SAFE027008000
821/03/2024SALES INVOICEOMARSHOP SAFE100001000
921/03/2024SALES INVOICEOMARHOME SAFE120002200
1021/03/2024PAID CASHOMARSHOP SAFE022000
1121/03/2024RECEIVED CASHOMARHOME SAFE1000011008900
1222/03/2024RECEIVED CASHOMARBANK300003000
1323/03/2024RECEIVED CASHOMARHOME SAFE200002000
1423/03/2024RECEIVED CASHOMARBANK500007000
1523/03/2024PAID CASHOMARSHOP SAFE3000010000
16TOTAL0029900
ورقة2
Cell Formulas
RangeFormula
E16:F16E16=SUM(M2:M15)
 
Upvote 0
Try:
VBA Code: 
Sub InsertColumn()
    Dim lRow As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If Range("C1") <> "NAME" Then
        Columns("C:C").Insert Shift:=xlToRight
        Range("C1") = "NAME"
        Range("C2:C" & lRow - 1) = Range("I1")
    Else
        Range("C2:C" & lRow - 1) = Range("H1")
    End If
End Sub
 
Upvote 0
perfect!
may you implement for specific sheets like("sh","main","data"), please?
 
Upvote 0
If you mean that you want the macro to run on those three sheets, then this macro should work. Change the sheet names (in red) to suit your needs.
Rich (BB code): 
Sub InsertColumn()
    Application.ScreenUpdating = False
    Dim lRow As Long, ws As Worksheet
    For Each ws In Sheets(Array("sh", "main", "data"))
        With ws
            lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            If .Range("C1") <> "NAME" Then
                .Columns("C:C").Insert Shift:=xlToRight
                .Range("C1") = "NAME"
                .Range("C2:C" & lRow - 1) = .Range("I1")
            Else
                .Range("C2:C" & lRow - 1) = .Range("H1")
            End If
        End With
    Next ws
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

A
  • Solved
add formulas in last column after sorting for each duplicates ID
Replies
9
Views
122
Peter_SSs
Peter_SSs
A
  • Question Question
Insert new column(DATE) up to finish month and move to next sheet
Replies
0
Views
293
Amer Omar
A
O
  • Solved
Find and match header in different location for the column based on sheet name
Replies
8
Views
116
Omar M
O
A
  • Question Question
Create report by matching part of item and populate for each cell under item across sheets
Replies
2
Views
205
Ali M
A
A
  • Solved
Extract duplicates dates and insert TOTAL row for each duplicates dates
Replies
4
Views
116
Alaa mg
A

Forum statistics

Threads
1,223,623
Messages
6,173,381
Members
452,515
Latest member
alexpecora0

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