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.

 

[Trebor76]

Hi Coyotex3,

Try this:

Option Explicit
Sub Macro1()

    Dim ws As Worksheet
    Dim lngRow As Long, lngLastRow As Long
    Dim strPayee As String, strCode As String
    Dim blnPaste As Boolean
    
    Application.ScreenUpdating = False
    
    Set ws = ThisWorkbook.Sheets("Sheet1") '<- Sheet name with data. Change to suit if necessary.
    lngLastRow = ws.Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    For lngRow = 2 To lngLastRow
        If Len(ws.Range("A" & lngRow)) > 0 And Len(ws.Range("B" & lngRow)) > 0 Then
            strPayee = ws.Range("A" & lngRow): strCode = ws.Range("B" & lngRow)
            blnPaste = True
        ElseIf Len(ws.Range("A" & lngRow)) = 0 And Len(ws.Range("B" & lngRow)) = 0 And blnPaste = True Then
            ws.Range("A" & lngRow).Value = strPayee: ws.Range("B" & lngRow).Value = strCode
        Else
            If ws.Range("A" & lngRow).Value = "Total" Then
                blnPaste = False
            End If
        End If
    Next lngRow
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[Peter_SSs]

.. or this (assumes the text values in column C are not the result of formulas)

Sub CopyPayeeCode()
  Dim rA As Range
  
  For Each rA In Range("C2", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
    rA.Offset(, -2).Resize(, 2).Value = rA.Offset(-1, -2).Resize(1, 2).Value
  Next rA
End Sub

 

[Fluff]

How about

Sub Coyotex()
   Dim Rng As Range
   For Each Rng In Range("C2", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
      Rng.Offset(-1, -2).Resize(Rng.Count + 1, 2).FillDown
   Next Rng
End Sub

 

[Fluff]

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.
On this occasion I have merged both threads.

 

[Coyotex3]

Hi Coyotex3,

Try this:

Option Explicit
Sub Macro1()

    Dim ws As Worksheet
    Dim lngRow As Long, lngLastRow As Long
    Dim strPayee As String, strCode As String
    Dim blnPaste As Boolean
   
    Application.ScreenUpdating = False
   
    Set ws = ThisWorkbook.Sheets("Sheet1") '<- Sheet name with data. Change to suit if necessary.
    lngLastRow = ws.Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   
    For lngRow = 2 To lngLastRow
        If Len(ws.Range("A" & lngRow)) > 0 And Len(ws.Range("B" & lngRow)) > 0 Then
            strPayee = ws.Range("A" & lngRow): strCode = ws.Range("B" & lngRow)
            blnPaste = True
        ElseIf Len(ws.Range("A" & lngRow)) = 0 And Len(ws.Range("B" & lngRow)) = 0 And blnPaste = True Then
            ws.Range("A" & lngRow).Value = strPayee: ws.Range("B" & lngRow).Value = strCode
        Else
            If ws.Range("A" & lngRow).Value = "Total" Then
                blnPaste = False
            End If
        End If
    Next lngRow
   
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

This one works!

 

[Coyotex3]

.. or this (assumes the text values in column C are not the result of formulas)

Sub CopyPayeeCode()
  Dim rA As Range
 
  For Each rA In Range("C2", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
    rA.Offset(, -2).Resize(, 2).Value = rA.Offset(-1, -2).Resize(1, 2).Value
  Next rA
End Sub

Thank you! This one works as well!

 

[Coyotex3]

How about

Sub Coyotex()
   Dim Rng As Range
   For Each Rng In Range("C2", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
      Rng.Offset(-1, -2).Resize(Rng.Count + 1, 2).FillDown
   Next Rng
End Sub

This one works as well. Sorry for creating a duplicate thread. Since this one already said solved I figured it wouldn’t get much traction. My apologies, and thank you once again!

As it pertains to learning VBA, where can I go for the basics? You guys are awesome and I appreciate you guys providing me with these amazing codes, would love to research this a bit to learn what different things do.

Like yesterday I was playing around with your initial code to try to figure out the offsets, but couldn’t.

 

[Fluff]

Glad we could help & thanks for the feedback.

As it pertains to learning VBA, where can I go for the basics?

There are loads of site out there, best bet is to do a search & have look to try & find the best site for you.

 

[Coyotex3]

Glad we could help & thanks for the feedback.

There are loads of site out there, best bet is to do a search & have look to try & find the best site for you.

I majored in accounting and have to use excel a lot. I find this to be way more interesting than accounting.

Hoping you could help me out a bit further, not necessarily to give me the code, just to help me understand what I’m missing.

So here is the code that I have so far

Sub Macro14()



Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = "=RC[3]"

ActiveCell.Offset(1, 0).Range("A1").Select

Selection.End(xlDown).Select

Selection.End(xlDown).Select

ActiveCell.Offset(-1, 0).Range("A1").Select

End Sub


Here is what I’m trying to accomplish with this macro. When I export my reports into Excel, I want to populate what is on “E2” on “B2” and then jump into the next range. That is where I run into some issues as the ranges vary just like in previous examples.

What am I missing as it pertains to the offsets and the varying ranges?

 

[Coyotex3]

I majored in accounting and have to use excel a lot. I find this to be way more interesting than accounting.

Hoping you could help me out a bit further, not necessarily to give me the code, just to help me understand what I’m missing.

So here is the code that I have so far

Sub Macro14()



Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = "=RC[3]"

ActiveCell.Offset(1, 0).Range("A1").Select

Selection.End(xlDown).Select

Selection.End(xlDown).Select

ActiveCell.Offset(-1, 0).Range("A1").Select

End Sub


Here is what I’m trying to accomplish with this macro. When I export my reports into Excel, I want to populate what is on “E2” on “B2” and then jump into the next range. That is where I run into some issues as the ranges vary just like in previous examples.

What am I missing as it pertains to the offsets and the varying ranges?

So I just realized part of the problem is some ranges are not displaying any data on B2 so Excel is bypassing that range and going one row above where there is a non blank cell.

How can I tell excel to put what is on E2 on B2 as long as column A is full and then jumó to the next range.