Help creating a looping macro for data in the same format but with different number of rows?

[Coyotex3]

Hello all I hope that you can all help me. I'm trying to create a macro that accomplishes the following.

Everytime I export data into Excel, I want it to copy whatever is on A7 until it reaches "Total." Then I want it to go 3 cells below the "Total" and copy whatever is on that cell until it reaches "Total." Then I want it to go 3 cells below the "Total" etc.

The next thing I would like is if possible for it to be dynamic. For example one day A7 will be empty until row A15 but one day it may be empty until row 25. I want it to always copy down until "Total" and then jump the three cells down.

I would like for the Macro to do that until there is no more data. I usually export files with about 2000 rows. Sometimes we have 100 different cells that need to be copied down. I'm trying to make that easier.

 

[petertenthije]

What do you want to do with the copied data?

Can you show a bit of data. How do to tell if something is a total? Are there always a 2 empty rows between data sets? Can there be empty rows within data sets?

 

[Coyotex3]

What do you want to do with the copied data?

Can you show a bit of data. How do to tell if something is a total? Are there always a 2 empty rows between data sets? Can there be empty rows within data sets?

My apologies I should restate that. Instead of copying the value until "Total" I want to copy the value until the next Non-Blank Cell(In the template the next Non-Blank Cell always says "Total.") There are always two empty rows between data sets.

 

[Coyotex3]

This is what it'll look like roughly except it'll be much m=larger

 

[petertenthije]

So you want to copy all data in columns B:G except for the blank rows?
And where does it have to go to?

 

[Coyotex3]

So you want to copy all data in columns B:G except for the blank rows?
And where does it have to go to?

Yes. The current format has it that Column one is never copied down. I would like for that command to continue until it hits the bottom of the workbook. Once it senses that there is nothing to jump to.

 

[Fluff]

How about

Sub Coyotex()
   Dim Rng As Range
   For Each Rng In Range("B1", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
      Rng.Offset(, -1).FillDown
   Next Rng
End Sub

 

[petertenthije]

Sub Fill_column_A()

' This macro assumes there are no blank rows inside data-sets!

Dim LastRow As Integer
Dim repetition As Integer
Dim Start_cel As Integer
Dim end_cel As Integer
Dim Interval_between_datasets As Integer

' Identify data range:
    Start_cel = 7
    LastRow = ActiveSheet.Cells.Find(What:="*", Lookat:=xlPart, LookIn:=xlFormulas, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    repetition = 1
    Interval_between_datasets = 2

' Begin of loop
    Do Until end_cel = LastRow
        Range("A" & Start_cel).Copy
        Worksheets("Coyotex3").Range("A" & Start_cel).End(xlDown).Select
        end_cel = ActiveCell.Row
        Range("A" & Start_cel & ":A" & end_cel - 1).Select
        Selection.PasteSpecial Paste:=xlPasteValues
        Start_cel = end_cel + Interval_between_datasets
    Loop

End Sub

 

[Coyotex3]

How about

Sub Coyotex()
   Dim Rng As Range
   For Each Rng In Range("B1", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
      Rng.Offset(, -1).FillDown
   Next Rng
End Sub

This is absolutely perfect. Where can I go look to learn these? You just helped me with the first command I was trying to figure out. Now I'm trying to put the rest together.

 

[Coyotex3]

Sub Fill_column_A()

' This macro assumes there are no blank rows inside data-sets!

Dim LastRow As Integer
Dim repetition As Integer
Dim Start_cel As Integer
Dim end_cel As Integer
Dim Interval_between_datasets As Integer

' Identify data range:
    Start_cel = 7
    LastRow = ActiveSheet.Cells.Find(What:="*", Lookat:=xlPart, LookIn:=xlFormulas, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    repetition = 1
    Interval_between_datasets = 2

' Begin of loop
    Do Until end_cel = LastRow
        Range("A" & Start_cel).Copy
        Worksheets("Coyotex3").Range("A" & Start_cel).End(xlDown).Select
        end_cel = ActiveCell.Row
        Range("A" & Start_cel & ":A" & end_cel - 1).Select
        Selection.PasteSpecial Paste:=xlPasteValues
        Start_cel = end_cel + Interval_between_datasets
    Loop

End Sub

Thank you. This one did not work. I assume it is because there are empty values on Column A which need to be filled out.