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​

 

[SKN2022]

It is confusing leaving 3 rows from Nth row ,leaving the row having Day avg etc. also mention the starting cell of then sheet where data is copied.
Pl give some examples like.
Page1: Row 5 to 25
Page2: Row 30 to 55
Page3: Row 65 to 95

i didnt get ur question , but again done some changes in the updated sheet
1. KF3 QC data sheet is automatically updated from server whenever i refresh it, so it is a source data sheet and i cant do any formating in this data sheet , now to extract data from this sheet i have created one more sheet named KF3, now from row no. 310 name as Day Avg i am copying data and pasting it into KF3 sheet row no. 7 date wise......................... hope now i am more clear
link of new file is below

data file.xlsm

KF3 QC Column1,Column2,Column3,Column4,Column5,Column6,Column7,Column8,Column9,Column10,Column11,Column12,Column13,Column14,Column15,Column16,Column17,Column18,Column19,Column20 44767,KILN FEED LINE 3,HOT MILL(CYCLONE 5) Residue (%),Sio2 (%),Al2O3 (%),Fe2O3 (%),CaO (%),Mgo (%),K2O (%),Na2O (%)

docs.google.com

 

[kvsrinivasamurthy]

I am not clear . Try this code.

Sub CopyToSheets()
Dim M
Dim Lclm As Long
Lclm = Range("a1").End(xlToRight).Column
M = Filter(Evaluate("Transpose(If(A1:A3000=""Day Avg."",Row(A1:A3000),False))"), False, False)
K = M(1)
For T = 0 To UBound(M)
Range(Cells(M(T), 1), Cells(M(T), Lclm)).Copy Sheets("Sheet2").Range("B" & 4 + T)

Next T
End Sub

 

[SKN2022]

I am not clear . Try this code.

Sub CopyToSheets()
Dim M
Dim Lclm As Long
Lclm = Range("a1").End(xlToRight).Column
M = Filter(Evaluate("Transpose(If(A1:A3000=""Day Avg."",Row(A1:A3000),False))"), False, False)
K = M(1)
For T = 0 To UBound(M)
Range(Cells(M(T), 1), Cells(M(T), Lclm)).Copy Sheets("Sheet2").Range("B" & 4 + T)

Next T
End Sub

getting error subscript out of range

K = M(1)

 

[kvsrinivasamurthy]

On which file you are running macro. The column A must have text "Day Avg.".
In One file you are having "Day Avg" , in another file You are having "Day Avg."
The dot in the end causes problem. Pl clarify which is correct or you will have both the values.

 

[kvsrinivasamurthy]

Try this code.

Sub CopyToSheets()
Dim M
Dim Lclm As Long
Lclm = Range("a1").End(xlToRight).Column
M = Filter(Evaluate("Transpose(If(Left(A1:A3000,7)=""Day Avg"",Row(A1:A3000),False))"), False, False)
K = M(1)
For T = 0 To UBound(M)
Range(Cells(M(T), 1), Cells(M(T), Lclm)).Copy Sheets("Sheet2").Range("B" & 4 + T)

Next T
End Sub

 

[SKN2022]

Try this code.

Sub CopyToSheets()
Dim M
Dim Lclm As Long
Lclm = Range("a1").End(xlToRight).Column
M = Filter(Evaluate("Transpose(If(Left(A1:A3000,7)=""Day Avg"",Row(A1:A3000),False))"), False, False)
K = M(1)
For T = 0 To UBound(M)
Range(Cells(M(T), 1), Cells(M(T), Lclm)).Copy Sheets("Sheet2").Range("B" & 4 + T)

Next T
End Sub

bingo ....................now its working
thank brother
i am interested in learning to write the code can u refer tutorial video ?
from where did u learnt?

thanks again ........................

 

[SKN2022]

hello
one more problem with the file it is having so many empty column between the dat i tried fn+F5 function to select blank column & delete it but its not working as it is deleting some data also, can a vba code will help in cleaning the data.
2 format of the same data is here file 1 is original and second one is after removing the data
need macro for any one of these.

Loading Google Sheets, Loading Google Sheets