VBA to copy a dynamic range to clipboard

[Purplemuffin]

Hi.

I'm trying and failing to get my head around VBA so thought I'd ask here.

I'm trying to write some VBA to attach to a button so that the user can copy a specific range of cells to clipboard in order to paste elsewhere.

This is the basic of my code:

Sub Copy_Test()
Range("A36:F39").Copy
End Sub

However - where it says F39, this could be anywhere between F38 and F50, where the row is taken to be the last non blank row (it would also have TOTAL in column E if that means the code is easier to write?)

Any ideas anybody please?

 

[Tom Urtis]

See if this helps:
Range("A36").CurrentRegion.Copy

 

[Skyybot]

Try this

Sub CopyRange()
Dim wb As Workbook, sht As Worksheet, rng As Range, lRow As Long, lCol As Long
Set wb = ThisWorkbook: Set sht = wb.ActiveSheet
lRow = sht.Rows(sht.Rows.Count).End(xlUp).Row
lCol = sht.Columns(sht.Columns.Count).End(xlToLeft).Column
Set rng = sht.Range("A36", Cells(lRow, lCol))
rng.Copy
End Sub

 

[Purplemuffin]

That did something good, but still copies everything up until row 50 as there is shading in those cells (it is a table that gets pasted into another program entirely, with formatting to make the dividers bold.

Is there a way to get it to ignore formatting but to look at what is actually written within cells?

 

[Purplemuffin]

The code in #3 copied everything from A36 to R50 - not sure what went wrong

Thanks for trying though

 

[Skyybot]

When you say "another program", do you mean another Workbook, or a totally different application?

 

[Purplemuffin]

It's an entirely different application - not Microsoft based

 

[Skyybot]

The code in #3 copied everything from A36 to R50 - not sure what went wrong

Thanks for trying though

It copied the Range. Is that not what was requested?

 

[Skyybot]

It's an entirely different application - not Microsoft based

Oh. I can't test without the receiving program. Sorry.

 

[Purplemuffin]

Sorry If I wasn't clear - I need to copy from cell A36 to cell F & (whatever row is the last one that isn't blank). There will also be text in the last non-blank row in column E saying "TOTAL" if that makes it easier