copy the firs four column & the last three columns

A

abdo meghari

Active Member
Joined
Aug 3, 2021
Messages
466
Office Version
  1. 2019
hi
can I copy specific columns ? I would copy columns (A,B,C,D) and the last three columns based on headers (ARRIVES,SALES,STOCK) which locates under the month
so the last three columns every month I issued three columns so when copy it depends on headers (ARRIVES,SALES,STOCK) which locates under the month
orginal data in first sheet
ST1.xlsm
ABCDEFGHIJKLM
1OctNovDec
2CategorySizePatternOriginArrivedSalesStock Arrived SalesStock Arrived SalesStock
3PSR (LRD)175/70R13B25INDO161845179179
4175/70R13EP150THI---------
5185/70R13EP150INDO---------
6175/65R14EP150INDO---------
7175/70R14EP150THI - - - ------
8175/70R14MY02THI--296-5291--291
9185/65R14TECTHI--- - - - - - -
10185/65R14EP150INDO--- - - - - - -
11 185/65R14150EZINDO
12185/65R15TC10INDO10--
13185/65R15T005INDO--- - - - - - -
14185/65R15T01JAP
15185/65R15B250JAP
16195/60R15AR20INDO--- - - - - - -
17195/60R15EP150THI
18195/60R15T001JAP
19 195/60R15150EZTHI
20195/65R15MY02THI50361361361
21195/65R15EP150THI--- - - - - - -
22195/65R15EP150JAP
23195/65R15T001JAP
24195/55R16EP300THI----- - -- -
25205/55R16 RE003THI--- - - - - - -
26205/65R15EP150INDO--- - - - - - -
27205/65R15MY02THI8668668668
28205/65R15T01JAP
29FS 205/65R15TZ700JAP---------
30215/65R15T005 INDO--- - - - - - -
31205/70R15694JAP-161616
32225/75R15CT697INDO - - - - - - - - -
33235/95R15CD618JAP - 565656
34255/70R15CD840THI661,3291,3292851,614
35205R16C D840THI
36205/R16CD697THI4293293293
37205/55R16AR20INDO10--
38205/55R16G3JAP40--
39205/55R16T001--0----
40205/55R16ER300POL
41205/60R16AR20INDO20--
42205/60R16T005THI
43205/60R16T001JAP
44205/65R16EP300INDO--------
45 205/65R16EP30ZINDO
46265/70R16D840THI----- - - - -
47275/70R16H005THI----- - - - -
48275/70R16D694JAP----- - - - -
49LT285/75R16AT001JAP----- - - - -
50TTL-1523,203-103,193285-3,478
51PSR (HRD)215/45R17T001JAP---------
52215/50R17EP300THI---------
53215/55R17GR90INDO--- - - - - - -
54215/55R17T001JAP--- - - - - - -
55215/55R17T005JAP
56275/55R20ALENZAJAP
57285/50R20DSPORT
58275/65R18116HTHI0--
59275/65R18AL01JAP
60285/60R18T01JAP
61285/60R18D850JAP-666666
62245/40R20RE050JAP-131313
63265/50R20SPORTJAP - 888
64275/30R20RE050JAP-222
65275/40R20DHPJAP
66275/55R20D680JAP - 161616
67285/30R20RE0050AJAP-141414
68315/35R20SPORTJAP - 333
69TTL--122--122--122
In & Out Balance
Cell Formulas
RangeFormula
M66:M68,J66:J68,M61:M64,J61:J64,M58,J58,M44,M41,J41,M36:M39,J36:J39,M33:M34,J33:J34,M31,J31,M27,J27,M20,J20,M12,J12,M3:M8,J8,J3:J6J3=G3+H3-I3
G44G44=E44-F44
E50:M50E50=SUM(E3:E49)
E69:M69E69=SUM(E51:E68)




result in sheet output
ST1.xlsm
ABCDEFG
1Dec
2CategorySizePatternOrigin Arrived SalesStock
3PSR (LRD)175/70R13B25INDO179
4175/70R13EP150THI---
5185/70R13EP150INDO---
6175/65R14EP150INDO---
7175/70R14EP150THI---
8175/70R14MY02THI--291
9185/65R14TECTHI - - -
10185/65R14EP150INDO - - -
11 185/65R14150EZINDO-
12185/65R15TC10INDO-
13185/65R15T005INDO - - -
14185/65R15T01JAP-
15185/65R15B250JAP-
16195/60R15AR20INDO - - -
17195/60R15EP150THI-
18195/60R15T001JAP-
19 195/60R15150EZTHI-
20195/65R15MY02THI361
21195/65R15EP150THI - --
22195/65R15EP150JAP-
23195/65R15T001JAP-
24195/55R16EP300THI---
25205/55R16 RE003THI - - -
26205/65R15EP150INDO - - -
27205/65R15MY02THI668
28205/65R15T01JAP-
29FS 205/65R15TZ700JAP---
30215/65R15T005 INDO - - -
31205/70R15694JAP16
32225/75R15CT697INDO - - -
33235/95R15CD618JAP56
34255/70R15CD840THI2851,614
35205R16C D840THI
36205/R16CD697THI293
37205/55R16AR20INDO-
38205/55R16G3JAP-
39205/55R16T001--
40205/55R16ER300POL-
41205/60R16AR20INDO-
42205/60R16T005THI-
43205/60R16T001JAP-
44205/65R16EP300INDO--
45 205/65R16EP30ZINDO-
46265/70R16D840THI - --
47275/70R16H005THI - --
48275/70R16D694JAP - --
49LT285/75R16AT001JAP - --
50TTL285-3,478
51PSR (HRD)215/45R17T001JAP---
52215/50R17EP300THI---
53215/55R17GR90INDO - - -
54215/55R17T001JAP - - -
55215/55R17T005JAP-
56275/55R20ALENZAJAP-
57285/50R20DSPORT-
58275/65R18116HTHI-
59275/65R18AL01JAP-
60285/60R18T01JAP-
61285/60R18D850JAP66
62245/40R20RE050JAP14
63265/50R20SPORTJAP8
64275/30R20RE050JAP2
65275/40R20DHPJAP-
66275/55R20D680JAP16
67285/30R20RE0050AJAP14
68315/35R20SPORTJAP3
69TTL--122
output
 
Sort by date Sort by votes

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
OK, thanks for the confirmation. You're welcome. :)
 
Upvote 0
Hope this will meet your wish
VBA Code: 
Sub test()
Dim a As Variant
a = Sheets("In & Out Balance").Cells(1).CurrentRegion.Value2
With Sheets("Output").Cells(1, 1).Resize(UBound(a), 7)
.Value = Application.Index(a, Evaluate _
("row(1:" & UBound(a) & ")"), Array(1, 2, 3, 4, UBound(a, 2) - 2, UBound(a, 2) - 1, UBound(a, 2)))
.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
.Offset(, 4).Resize(1, 3).Merge
.Offset(, 4).HorizontalAlignment = xlCenter
        .Resize(2).Interior.Color = 65535
        .Resize(2).Font.Bold = True
        .Offset(UBound(a) - 1, 1).Resize(1).Interior.ThemeColor = xlThemeColorDark1
        .Offset(UBound(a) - 1, 1).Resize(1).Interior.TintAndShade = -0.14996795556505
        .Offset(UBound(a) - 1).Resize(1).Font.Bold = True
        .Borders(xlEdgeLeft).Weight = xlThin
        .Borders(xlEdgeTop).Weight = xlThin
        .Borders(xlEdgeBottom).Weight = xlThin
        .Borders(xlEdgeRight).Weight = xlThin
        .Borders(xlInsideVertical).Weight = xlThin
        .Borders(xlInsideHorizontal).Weight = xlThin
        .Columns.AutoFit
        End With
End Sub
 
Last edited:
Upvote 0
@mohadin thanks again .if you still interest . nothing changes . when show the data in second sheet should be like in first sheet like this
every row contain TTL should be like this
ST1.xlsm
BCDEFG
116TTL4,3385,047
In & Out Balance
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,537
Messages
6,125,386
Members
449,221
Latest member
DFCarter

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