Option Explicit Sub loopTest() Dim hdr As Range 'header range Dim dta As Range 'data range Dim cl As Integer 'copy line Dim ns As Excel.Worksheet Set hdr = Excel.Worksheets(1).Range("A2:Q2") 'set this range for what ever range your headers are on cl = 3 'set this value for what ever row your data starts on Do While Excel.Worksheets(1).Range(Cells(cl, 1), Cells(cl, 1)).Value <> "" 'this stops the loop when there are no more records Set dta = Excel.Worksheets(1).Range(Cells(cl, 1), Cells(cl + 2, 17)) 'this sets the data range change the number 3 to how ever many columns there are in your dataset Set ns = Excel.Worksheets.Add(, ActiveSheet) 'this sets the new sheet to the ns (new sheet = ns ) variable hdr.Copy ns.Range("A1").PasteSpecial xlPasteAll dta.Copy ns.Range("A2").PasteSpecial xlPasteAll cl = cl + 5 Loop End Sub
Do While Excel.Worksheets(1).Range(Cells(cl, 1), Cells(cl, 1)).Value <> "" 'this stops the loop when there are no more records
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE