COPY EVERY NTH ROW TO OTHER SHEET AUTOMATICALLY

[SKN2022]

Hello expert i am struggling with very difficult task
actially i want to copy data from a sheet which is connected to network and keeps updated every hour i have connected it thorugh the nertwork and it keeps updated whenever i refresh the data, but now task is that i want to extract specific rows data from the sheet to another sheet and if possible i want it to get copied automatically
now to copy from the sheet the row no is 45 means after every 45 row needs to be copied to another sheet, or a row which is starting from the text Day Avg. is also the same 45th number.
now before putting the formula or VBA, one issue is that we need to leave first 3 rows of the sheet and than every 45th row to be copied to other sheet.
and the destination sheet where it gets copied one column should be left means copied data should paste to B column, as A column will be used for date.

Column1

Column2

Column3

Column4

Column5

Column6

Column7

Column8

Column9

Column10

Column11

Column12

Column13

Column14

Column15

Column16

25-07-2022​

KILN FEED LINE 3

Residue (%)

Sio2 (%)

Al2O3 (%)

Fe2O3 (%)

CaO (%)

Mgo (%)

K2O (%)

Na2O (%)

SO3 (%)

CL (%)

TiO2 (%)

LSF

SM

AM

+90 mic

+212 Mic

TARGETS

3​

99.5​

2.05±0.05

1.20±0.05

0.041666667​

12.87​

3.18​

3.31​

42.68​

0.73​

0.41​

0.13​

0.26​

0.011​

0.446​

100.5035066​

1.855536332​

1.095468278​

0.083333333​

-

-

-

0.125​

-

-

-

0.166666667​

15.7​

2.4​

13.06​

3.13​

3.19​

42.96​

0.74​

0.41​

0.13​

0.26​

0.012​

0.403​

100.349354​

1.942585155​

1.107523511​

0.208333333​

-

-

-

0.25​

-

-

-

0.291666667​

14.8​

2.5​

13.02​

3.24​

3.51​

42.54​

0.72​

0.39​

0.15​

0.26​

0.011​

0.63​

98.23550195​

1.764227642​

1.102564103​

0.333333333​

-

-

-

0.375​

13.02​

3.1​

3.18​

42.9​

0.7​

0.38​

0.14​

0.25​

0.012​

0.53​

100.2186589​

1.911894273​

1.141509434​

0.416666667​

-

-

-

0.458333333​

16.1​

2.4​

13.19​

3.05​

3.08​

43.17​

0.7​

0.37​

0.14​

0.25​

0.011​

0.54​

99.99953672​

1.977511244​

1.165584416​

0.5​

-

-

-

0.541666667​

-

-

-

0.583333333​

-

-

-

0.625​

14.5​

1.7​

13.27​

2.99​

2.94​

43.27​

0.69​

0.37​

0.14​

0.25​

0.011​

0.5​

100.1963636​

2.063763608​

1.18707483​

0.666666667​

-

-

-

0.708333333​

12.99​

3.15​

3.16​

43.11​

0.69​

0.37​

0.14​

0.25​

0.01​

0.54​

100.7709174​

1.896350365​

1.167721519​

0.75​

-

-

-

0.791666667​

13​

2​

13.26​

3.17​

3.11​

43.14​

0.7​

0.36​

0.14​

0.25​

0.011​

0.51​

99.19088382​

1.95287187​

1.183279743​

0.833333333​

-

-

-

0.875​

-

-

-

0.916666667​

-

-

-

0.958333333​

14.2​

1.9​

13.12​

3.1​

3.22​

42.75​

0.69​

0.37​

0.13​

0.25​

0.011​

0.49​

99.26808653​

1.926578561​

1.114906832​

1​

-

-

-

Day Avg

14.71666667​

2.15​

13.08888889​

3.123333333​

3.188888889​

42.94666667​

0.706666667​

0.381111111​

0.137777778​

0.253333333​

0.509888889​

99.85920105​

1.921257672​

1.140625852​

Min

13​

1.7​

12.87​

2.99​

2.94​

42.54​

0.69​

0.36​

0.13​

0.25​

0.403​

98.23550195​

1.764227642​

1.095468278​

Max

16.1​

2.5​

13.27​

3.24​

3.51​

43.27​

0.74​

0.41​

0.15​

0.26​

0.63​

100.7709174​

2.063763608​

1.18707483​

SD

1.108903362​

0.327108545​

0.132895113​

0.074161985​

0.157990858​

0.248997992​

0.018708287​

0.018333333​

0.006666667​

0.005​

0.063879661​

0.80480214​

0.082644426​

0.036365994​

CV

7.535017184​

15.21435092​

1.01532769​

2.374449889​

4.95441714​

0.57978421​

2.647399094​

4.810495627​

4.838709677​

1.973684211​

12.5281532​

0.805936891​

4.301579487​

3.188249117​

26-07-2022​

KILN FEED LINE 3

Residue (%)

Sio2 (%)

Al2O3 (%)

Fe2O3 (%)

CaO (%)

Mgo (%)

K2O (%)

Na2O (%)

SO3 (%)

CL (%)

TiO2 (%)

LSF

SM

AM

+90 mic

+212 Mic

TARGETS

3​

99.5​

2.05±0.05

1.20±0.05

0.041666667​

13.02​

3.15​

3.2​

42.77​

0.71​

0.37​

0.14​

0.25​

0.011​

0.51​

99.80212252​

1.897959184​

1.14375​

0.083333333​

-

-

-

0.125​

-

-

-

0.166666667​

15.2​

2.1​

12.67​

3.23​

3.35​

42.73​

0.69​

0.36​

0.14​

0.25​

0.011​

0.56​

101.4345162​

1.774509804​

1.131343284​

0.208333333​

-

-

-

0.25​

-

-

-

0.291666667​

13.9​

2.2​

13.31​

3.07​

3.1​

43.16​

0.69​

0.36​

0.14​

0.25​

0.011​

0.49​

99.25535487​

1.998498498​

1.148387097​

0.333333333​

-

-

-

0.375​

-

-

-

0.416666667​

-

-

-

0.458333333​

14​

2.3​

13.43​

3.02​

3.05​

42.76​

0.69​

0.36​

0.14​

0.25​

0.011​

0.49​

97.78564454​

2.047256098​

1.150819672​

0.5​

-

-

-

0.541666667​

-

-

-

0.583333333​

-

-

-

0.625​

14​

1.9​

12.78​

3.05​

3.12​

42.79​

0.7​

0.36​

0.14​

0.25​

0.013​

0.49​

101.9071571​

1.918918919​

1.134615385​

0.666666667​

-

-

-

0.708333333​

12.77​

3.09​

3.12​

42.9​

0.7​

0.36​

0.14​

0.25​

0.013​

0.52​

102.0364477​

1.897473997​

1.157051282​

0.75​

-

-

-

0.791666667​

-

-

-

0.833333333​

11.6​

1.7​

13.3​

3.16​

3.15​

43.12​

0.72​

0.39​

0.14​

0.25​

0.012​

0.47​

98.96513499​

1.961651917​

1.152380952​

0.875​

-

-

-

0.916666667​

-

-

-

0.958333333​

12.8​

1.8​

13.17​

3.13​

3.16​

43.1​

0.71​

0.37​

0.14​

0.25​

0.012​

0.53​

99.65594421​

1.931085044​

1.158227848​

1​

-

-

-

Day Avg

13.58333333​

2​

13.05625​

3.1125​

3.15625​

42.91625​

0.70125​

0.36625​

0.14​

0.25​

0.5075​

100.1052903​

1.928419183​

1.14707194​

Min

11.6​

1.7​

12.67​

3.02​

3.05​

42.73​

0.69​

0.36​

0.14​

0.25​

0.47​

97.78564454​

1.774509804​

1.131343284​

Max

15.2​

2.3​

13.43​

3.23​

3.35​

43.16​

0.72​

0.39​

0.14​

0.25​

0.56​

102.0364477​

2.047256098​

1.158227848​

SD

1.233558538​

0.236643191​

0.289133558​

0.068190908​

0.08991067​

0.181811323​

0.011259916​

0.010606602​

0​

0​

0.028660575​

1.532498325​

0.080895534​

0.009867653​

CV

9.08141255​

11.83215957​

2.214522224​

2.190872562​

2.84865489​

0.423642147​

1.605692159​

2.896000469​

0​

0​

5.647403982​

1.530886451​

4.194914415​

0.860247066​

27-07-2022​

KILN FEED LINE 3

Residue (%)

Sio2 (%)

Al2O3 (%)

Fe2O3 (%)

CaO (%)

Mgo (%)

K2O (%)

Na2O (%)

SO3 (%)

CL (%)

TiO2 (%)

LSF

SM

AM

+90 mic

+212 Mic

TARGETS

3​

99.5​

2.05±0.05

1.20±0.05

0.041666667​

13.35​

3.05​

3.22​

43​

0.69​

0.36​

0.14​

0.25​

0.01​

0.51​

98.4571986​

1.969026549​

1.105590062​

0.083333333​

-

-

-

0.125​

-

-

-

0.166666667​

15.8​

2.2​

13.35​

3​

3.04​

43.15​

0.69​

0.36​

0.14​

0.25​

0.011​

0.49​

99.25427035​

2.044410413​

1.148026316​

0.208333333​

-

-

-

0.25​

-

-

-

0.291666667​

14.4​

2.2​

13.44​

3.06​

3.15​

43.12​

0.7​

0.36​

0.14​

0.25​

0.01​

0.508​

98.24619604​

2.000595415​

1.132698413​

0.333333333​

-

-

-

0.375​

-

-

-

0.416666667​

13.32​

3.11​

3.22​

42.834​

0.69​

0.36​

0.14​

0.25​

0.011​

0.549​

98.00346035​

1.936327955​

1.136335404​

0.458333333​

-

-

-

0.5​

14.8​

2.2​

13.2​

3.06​

3.18​

42.55​

0.69​

0.36​

0.14​

0.25​

0.01​

0.549​

98.30054415​

1.944321697​

1.13490566​

0.541666667​

-

-

-

0.583333333​

-

-

-

0.625​

12.4​

1.8​

13.05​

3.11​

3.16​

42.75​

0.68​

0.37​

0.14​

0.25​

0.012​

0.53​

99.67544277​

1.919117647​

1.151898734​

0.666666667​

-

-

-

0.708333333​

13.15​

3.07​

3.17​

42.96​

0.69​

0.36​

0.15​

0.25​

0.011​

0.57​

99.50041343​

1.930983847​

1.148264984​

0.75​

-

-

-

0.791666667​

0.833333333​

14​

2.4​

13.25​

2.99​

3.03​

43.37​

0.69​

0.35​

0.15​

0.25​

0.011​

0.58​

100.2030863​

2.007575758​

1.178217822​

0.875​

-

-

-

0.916666667​

-

-

-

0.958333333​

12.4​

1.8​

13.09​

2.94​

3.16​

43.09​

0.68​

0.36​

0.14​

0.25​

0.011​

0.51​

100.7317016​

1.980332829​

1.091772152​

1​

-

-

-

Day Avg

13.96666667​

2.1​

13.24444444​

3.043333333​

3.147777778​

42.98044444​

0.688888889​

0.36​

0.142222222​

0.25​

0.532888889​

99.15247928​

1.970299123​

1.136412172​

Min

12.4​

1.8​

13.05​

2.94​

3.03​

42.55​

0.68​

0.35​

0.14​

0.25​

0.49​

98.00346035​

1.919117647​

1.091772152​

Max

15.8​

2.4​

13.44​

3.11​

3.22​

43.37​

0.7​

0.37​

0.15​

0.25​

0.58​

100.7317016​

2.044410413​

1.178217822​

SD

1.35302131​

0.244948974​

0.131729943​

0.056568542​

0.068698213​

0.243057149​

0.006009252​

0.005​

0.004409586​

0​

0.030884642​

0.958738178​

0.04167777​

0.025548299​

CV

9.687503416​

11.66423687​

0.994605271​

1.858769195​

2.182435282​

0.565506365​

0.872310792​

1.388888889​

3.100489818​

0​

5.795700124​

0.966933136​

2.115301666​

2.248154319​

28-07-2022​

KILN FEED LINE 3

Residue (%)

Sio2 (%)

Al2O3 (%)

Fe2O3 (%)

CaO (%)

Mgo (%)

K2O (%)

Na2O (%)

SO3 (%)

CL (%)

TiO2 (%)

LSF

SM

AM

+90 mic

+212 Mic

TARGETS

3​

99.5​

2.05±0.05

1.20±0.05

0.041666667​

-

-

-

0.083333333​

12.9​

2.93​

3.15​

42.88​

0.67​

0.35​

0.13​

0.25​

0.013​

0.51​

101.5471254​

1.957511381​

1.092063492​

0.125​

-

-

-

0.166666667​

14.4​

2​

13.14​

3.06​

3.22​

43.04​

0.67​

0.35​

0.14​

0.25​

0.011​

0.52​

99.83901423​

1.932352941​

1.111801242​

0.208333333​

-

-

-

0.25​

-

-

-

0.291666667​

14.6​

2.5​

12.91​

3.01​

3.26​

43.12​

0.68​

0.35​

0.14​

0.25​

0.011​

0.51​

101.6487273​

1.904129794​

1.079754601​

0.333333333​

-

-

-

0.375​

13.04​

3.03​

3.14​

43.26​

0.67​

0.35​

0.13​

0.25​

0.01​

0.47​

101.3518263​

1.963855422​

1.114649682​

0.416666667​

-

-

-

0.458333333​

13.1​

2.3​

13.08​

3.04​

3.23​

43.27​

0.68​

0.35​

0.13​

0.25​

0.01​

0.47​

100.9441203​

1.940652819​

1.086687307​

0.5​

-

-

-

0.541666667​

-

-

-

0.583333333​

-

-

-

0.625​

12.2​

1.9​

XRF IN MAINTENANCE

-

-

0.666666667​

-

-

-

0.708333333​

13.221​

3.06​

3.53​

42.96​

0.64​

0.35​

0.13​

0.25​

0.011​

0.47​

98.80700205​

1.87266289​

1​

0.75​

-

-

-

0.791666667​

11.6​

1.9​

13.36​

3​

3.28​

43.01​

0.6​

0.35​

0.13​

0.26​

0.11​

0.48​

98.54191869​

1.976331361​

1.06097561​

0.833333333​

-

-

-

0.875​

-

-

-

0.916666667​

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

0.958333333​

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

1​

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

Day Avg

13.18​

2.12​

13.093​

3.018571429​

3.258571429​

43.07714286​

0.658571429​

0.35​

0.132857143​

0.251428571​

0.49​

100.3828192​

1.935356658​

1.077990276​

Min

11.6​

1.9​

12.9​

2.93​

3.14​

42.88​

0.6​

0.35​

0.13​

0.25​

0.47​

98.54191869​

1.87266289​

1​

Max

14.6​

2.5​

13.36​

3.06​

3.53​

43.27​

0.68​

0.35​

0.14​

0.26​

0.52​

101.6487273​

1.976331361​

1.114649682​

SD

1.319848476​

0.268328157​

0.165125205​

0.045250625​

0.130566531​

0.147728838​

0.029113898​

5.99589E-17​

0.0048795​

0.003779645​

0.02236068​

1.315294896​

0.036342434​

0.03902111​

CV

10.01402486​

12.65698855​

1.261171657​

1.499074178​

4.006864173​

0.342940195​

4.420765399​

1.71311E-14​

3.67274221​

1.50326779​

4.563404036​

1.310278897​

1.877815824​

3.61980165​

29-07-2022​

KILN FEED LINE 3

Residue (%)

Sio2 (%)

Al2O3 (%)

Fe2O3 (%)

CaO (%)

Mgo (%)

K2O (%)

Na2O (%)

SO3 (%)

CL (%)

TiO2 (%)

LSF

SM

AM

+90 mic

+212 Mic

TARGETS

3​

99.5​

2.05±0.05

1.20±0.05

0.041666667​

9.8​

1.5​

13.23​

3.05​

3.3​

43.37​

0.64​

0.36​

0.13​

0.2​

0.01​

0.49​

100.0087626​

1.934210526​

1.072727273​

0.083333333​

-

-

-

0.125​

11​

2​

13.14​

3.09​

3.22​

43.35​

0.64​

0.36​

0.13​

0.26​

0.01​

0.51​

100.5030951​

1.926686217​

1.118012422​

0.166666667​

-

-

-

0.208333333​

-

-

-

0.25​

-

-

-

0.291666667​

13.1​

1.9​

13.4​

3.14​

3.43​

43.27​

0.63​

0.35​

0.14​

0.25​

0.011​

0.558​

98.08868741​

1.879910213​

1.078134111​

0.333333333​

-

-

-

0.375​

13.25​

3.02​

3.06​

43.65​

0.66​

0.36​

0.13​

0.26​

0.011​

0.492​

100.9641673​

2.016129032​

1.147712418​

0.416666667​

-

-

-

0.458333333​

-

-

-

0.5​

14.7​

2.5​

13.31​

3.12​

3.06​

43.38​

0.67​

0.35​

0.14​

0.25​

0.011​

0.514​

99.62080711​

1.988347774​

1.187581699​

0.541666667​

-

-

-

0.583333333​

-

-

-

0.625​

14.4​

2.3​

13.22​

3.15​

3.26​

43.2​

0.67​

0.35​

0.14​

0.25​

0.011​

0.54​

99.33500731​

1.902158273​

1.13190184​

0.666666667​

-

-

-

0.708333333​

-

-

-

0.75​

12.82​

3.24​

3.01​

43.38​

0.69​

0.35​

0.12​

0.25​

0.01​

0.47​

102.7224528​

1.907738095​

1.23255814​

0.791666667​

-

-

-

0.833333333​

12.6​

1.9​

13.29​

3.22​

3.04​

43.35​

0.68​

0.35​

0.13​

0.25​

0.01​

0.49​

99.50465732​

1.968888889​

1.220394737​

0.875​

0.916666667​

0.958333333​

14.7​

3​

13.21​

3.18​

2.98​

43.26​

0.68​

0.35​

0.13​

0.25​

0.011​

0.49​

100.0101721​

1.986466165​

1.231543624​

1​

-

-

-

Day Avg

12.9​

2.157142857​

13.20777778​

3.134444444​

3.151111111​

43.35666667​

0.662222222​

0.353333333​

0.132222222​

0.246666667​

0.506​

100.084201​

1.945615021​

1.157840696​

Min

9.8​

1.5​

12.82​

3.02​

2.98​

43.2​

0.63​

0.35​

0.12​

0.2​

0.47​

98.08868741​

1.879910213​

1.072727273​

Max

14.7​

3​

13.4​

3.24​

3.43​

43.65​

0.69​

0.36​

0.14​

0.26​

0.558​

102.7224528​

2.016129032​

1.23255814​

SD

1.918332609​

0.489411697​

0.162617479​

0.07350359​

0.156000356​

0.126885775​

0.021081851​

0.005​

0.006666667​

0.018027756​

0.027838822​

1.275506145​

0.046283687​

0.063019328​

CV

14.87079542​

22.68795948​

1.231225128​

2.345027693​

4.950645998​

0.292655744​

3.183501​

1.41509434​

5.042016807​

7.308549883​

5.501743442​

1.27443306​

2.378871779​

5.44283234​

 

[rollis13]

Have a try with this macro to be pasted in a standard module. I am supposing that your sheet names are Sheet1 and Sheet2 (in macro adjust as needed).
It filters all your data in Sheet1 on criteria "Day Avg" then it copies what found and pastes in Sheet2 starting from row 4 column B.

Option Explicit
Sub FilterCopyPaste()
    With Sheets(1)                                '<- adjust source sheet name as needed
        .AutoFilterMode = False
        With .Range("A1:P" & .UsedRange.Rows.Count)
            .AutoFilter Field:=1, Criteria1:="Day Avg"
            .Offset(1).SpecialCells(xlCellTypeVisible).Copy Sheets(2).Cells(4, "B") '<- adjust destination sheet name as needed
        End With
        .AutoFilterMode = False
        .Cells(1.1).Select
    End With
    Application.CutCopyMode = False
End Sub

if possible i want it to get copied automatically

This is a different question but could be added with a timer macro to trigger every hour (search in Forum, you will find many examples).

 

[SKN2022]

Have a try with this macro to be pasted in a standard module. I am supposing that your sheet names are Sheet1 and Sheet2 (in macro adjust as needed).
It filters all your data in Sheet1 on criteria "Day Avg" then it copies what found and pastes in Sheet2 starting from row 4 column B.

Option Explicit
Sub FilterCopyPaste()
    With Sheets(1)                                '<- adjust source sheet name as needed
        .AutoFilterMode = False
        With .Range("A1:P" & .UsedRange.Rows.Count)
            .AutoFilter Field:=1, Criteria1:="Day Avg"
            .Offset(1).SpecialCells(xlCellTypeVisible).Copy Sheets(2).Cells(4, "B") '<- adjust destination sheet name as needed
        End With
        .AutoFilterMode = False
        .Cells(1.1).Select
    End With
    Application.CutCopyMode = False
End Sub

This is a different question but could be added with a timer macro to trigger every hour (search in Forum, you will find many examples).

thanks for the reply, i searched alot but didnt found. i ran several code but some or another error occured, tried the above one also but it also cam with an error

 

[kvsrinivasamurthy]

Does Sheets(2) is having merged cells.

 

[SKN2022]

Does Sheets(2) is having merged cells.

yes it has as it is system generated document so removing merged cell will create more nuisance in data sheet, tried that also, there is lose in data also

 

[kvsrinivasamurthy]

Is it possible to upload a sample file.

 

[rollis13]

I ran several code but some or another error occured

Code found in Forum is there to be copied and pasted to your project but aways needs to be adapted for your needs. Are you able to do it ?

tried the above one also but it also came with an error

In post #1 you didn't mention that you had merged cells in data area of your sheets. Merged cells always mess up macros. It's been already asked, and I confirm, sample file complete of sheet and data structure is needed.

i searched alot but didn't found

Huh, I'm sure you didn't search enought. As said, what you find must always be adapted to your project. Example: LINK

 

[rollis13]

As before, extra LINK

 

[SKN2022]

Is it possible to upload a sample file.

here only 2 option i can see, upload image & upload mini sheet, how to upload the file ??

 

[SKN2022]

Code found in Forum is there to be copied and pasted to your project but aways needs to be adapted for your needs. Are you able to do it ?

yes i am adapting it to my need in general like changing the sheet name as per written in the code, nothing more i knew abt it

In post #1 you didn't mention that you had merged cells in data area of your sheets. Merged cells always mess up macros. It's been already asked, and I confirm, sample file complete of sheet and data structure is needed.


Huh, I'm sure you didn't search enought. As said, what you find must always be adapted to your project. Example: LINK

thanks for taking time to reply, actually i am not a pro like many are here, just learning to clean the data as well as adapting it according to my need, gone through many tutorials but not getting wat my requirement is , as u know one change in code or sheet layout also creates issue.