copy the firs four column & the last three columns

A

abdo meghari

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

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
Hi
Try
VBA Code: 
Sub test()
Dim a As Variant
a = Sheets("In & Out Balance").Cells(1).CurrentRegion.Value2
Sheets("Output").Cells(1, 1).Resize(UBound(a), 7) = Application.Index(a, Evaluate _
("row(1:" & UBound(a) & ")"), Array(1, 2, 3, 4, UBound(a, 2) - 2, UBound(a, 2) - 1, UBound(a, 2)))
End Sub
 
Upvote 0
excellent ! but I would with the same formatting and borders and if it's possible make number format like this #,##0.00
 
Upvote 0
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);_(* ""-""??_);_(@_)"
End With
End Sub
 
Upvote 0
abdo meghari said:
with the same formatting and borders and if it's possible make number format like this #,##0.00
Click to expand...
See if this does what you want.

VBA Code: 
Sub Copy_Columns()
  With Sheets("In & Out Balance")
    Intersect(.UsedRange, Union(.Columns("A:D"), .Columns(.Cells(2, Columns.Count).End(xlToLeft).Column - 2).Resize(, 3))).Copy
  End With
  With Sheets("output")
    .Range("A1").PasteSpecial Paste:=xlPasteValues
    .Range("A1").PasteSpecial Paste:=xlPasteFormats
    .Columns("E:G").NumberFormat = "#,##0.00"
    .UsedRange.Columns.AutoFit
  End With
End Sub
 
Upvote 0
@Peter_SSs your code works from the first time but if I run again doesn't seem dealing with the merged cells , but why works in first time if the merged cells are the problem?
 
Upvote 0
Try this
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
        .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
 
Upvote 0
Solution
abdo meghari said:
@Peter_SSs your code works from the first time but if I run again doesn't seem dealing with the merged cells , but why works in first time if the merged cells are the problem?
Click to expand...
I assume that a second run means you would be doing another month and any data already in the 'output' sheet could be removed?
If so, try adding this extra line of code

Rich (BB code): 
Sub Copy_Columns()
  Sheets("output").UsedRange.EntireColumn.Delete
  With Sheets("In & Out Balance")
    Intersect(.UsedRange, Union(.Columns("A:D"), .Columns(.Cells(2, Columns.Count).End(xlToLeft).Column - 2).Resize(, 3))).Copy
  End With
  With Sheets("output")
    .Range("A1").PasteSpecial Paste:=xlPasteValues
    .Range("A1").PasteSpecial Paste:=xlPasteFormats
    .Columns("E:G").NumberFormat = "#,##0.00"
    .UsedRange.Columns.AutoFit
  End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,523
Messages
6,125,318
Members
449,218
Latest member
Excel Master

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