Quick VBA Copy range with data in question

[tezza]

Hi All,

I've done searches but can't quite find the right answer so I've come to you for help.

I'd like to vba code the following:

Count how many rows have data in it (B7:G7 Down with no empty rows but not all Columns filled hense the range)

Copy the data from M7:P7 down the number of rows counted above (This range has formulas in so using something like xldown doesn't appear to work).

Kind Regards
Terry

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[tezza]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

I'm not sure the works laptop will allow that.

Ultimately, what I'm after is to copy the data from M7:P7 down to the last row of data baring in mind it uses formulas to collect data from B7:G7 Down as staggered data.

Hope that explains it better.

 

[Joe4]

Is this what you are looking for?

Sub MyCopy()

    Dim lr As Long
   
'   Find last row in column B with data
    lr = Cells(Rows.Count, "B").End(xlUp).Row
   
'   Copy down formula in M7:P7 to last row
    If lr > 7 Then Range("M7:P7").Copy Range("M8:M" & lr)
   
End Sub

If not, please at least post images (you can copy and paste images).
We won't be able to copy them, but at least then we can see how your data is structured and get a better idea of what you are after.

 

[tezza]

Is this what you are looking for?

Sub MyCopy()

    Dim lr As Long
 
'   Find last row in column B with data
    lr = Cells(Rows.Count, "B").End(xlUp).Row
 
'   Copy down formula in M7:P7 to last row
    If lr > 7 Then Range("M7:P7").Copy Range("M8:M" & lr)
 
End Sub

If not, please at least post images (you can copy and paste images).
We won't be able to copy them, but at least then we can see how your data is structured and get a better idea of what you are after.

HI,

It does something but doesn't save it to clipboard so that I can paste it somewhere else.

I can supply partial data - I wish to copy all the data in the yellow columns but not beyond as the destination has the same amount of rows.

 

[Joe4]

OK, I think I misunderstood your original query.

This will copy columns M:P from row 7 down to the last row, looking at column B, and wait for your paste.

Sub MyCopy()

    Dim lr As Long
    
'   Find last row in column B with data
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Copy down formula in M7:P7 to last row
    Range("M7:P" & lr).Copy
    
End Sub

 

[tezza]

OK, I think I misunderstood your original query.

This will copy columns M:P from row 7 down to the last row, looking at column B, and wait for your paste.

Sub MyCopy()

    Dim lr As Long
   
'   Find last row in column B with data
    lr = Cells(Rows.Count, "B").End(xlUp).Row
   
'   Copy down formula in M7:P7 to last row
    Range("M7:P" & lr).Copy
   
End Sub

Hi,

I think it might fail as it can't rely on the data in Column B being in the last row, it would need to check the range (B:G) for the last row of data.

Regards

 

[Joe4]

OK, I think this should do that:

Sub MyCopy()

    Dim lr As Long
    
'   Find last row in column B with data
    With Range("B7:G7").CurrentRegion
        lr = .Rows(.Rows.Count).Row
    End With
    
'   Copy down formula in M7:P7 to last row
    Range("M7:P" & lr).Copy
    
End Sub

 

[tezza]

OK, I think this should do that:

Sub MyCopy()

    Dim lr As Long
   
'   Find last row in column B with data
    With Range("B7:G7").CurrentRegion
        lr = .Rows(.Rows.Count).Row
    End With
   
'   Copy down formula in M7:P7 to last row
    Range("M7:P" & lr).Copy
   
End Sub

Almost, it copies to the bottom of col B:G not to the bottom row of data.

 

[Joe4]

Almost, it copies to the bottom of col B:G not to the bottom row of data.

I am not sure what you are talking about.

In your previous post, you said:

it would need to check the range (B:G) for the last row of data.

And that is exactly what it is doing.

If this is not what you want, then I don't think you are explaining yourself clearly.
Please walk us through an actual example, based on the data you posted, step-by-step.
Tell us EXACTLY what you want to happen, specifically (meaning, tell us EXACT range addresses).