vba copy and paste dynamic ranges

Question

Not used to using VBA. I have a program that logs 1 min snapshots of a data series and creates a log. Then I want to then take snapshots of the log X columns back to fill in other snapshot columns. Ie data 1 min ago, 10 min ago, 60 min ago, etc. Code seems to work fine as long as the log sheet is filled out enough. But if it's only been 8 min ago it has an error trying to copy 10 min ago data that doesn't exist. I can't seem to figure out why this Sub isn't working like i think.

Run-time error '1004' Application or object defined error. I thought my If statement would eliminate this.

Here's my code:

Sub tenMinBack()
Dim rng As Range

Application.CutCopyMode = False
Application.ScreenUpdating = False

ThisWorkbook.Worksheets("Log").Select
rng = Range(Cells(1, Columns.Count).End(xlToLeft).Offset(0,-10), Cells(13,Columns.Count).End(xlToLeft).Offset(0,-10)).Select
#### So I know this rng is pointing to cells that don't exist for first 10 minutes,
#### Not sure why this If statement isn't stopping it from attempting to copy it
If Not IsEmpty(rng) Then
    rng.Copy
    ThisWorkBook.Worksheets("Prices").Range("D3:D15").PasteSpecial xlValues
    ThisWorkBook.Worksheets("Log").Cells.EntireColumn.AutoFit
    ThisWorkBook.Worksheets("Prices").Cells.EntireColumn.AutoFit
End If
ThisWorkBook.Worksheets("Prices").Select

End Sub

Try using `Set rng = Range(Cells(1, Columns.Count).End(xlToLeft).Offset(0,-10), Cells(13,Columns.Count).End(xlToLeft).Offset(0,-10))` and side note: try to [avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code — cybernetic.nomad, Nov 23 '22 at 16:19
To offset your columns by `-10` you need at least 11 columns, maybe put that as a condition before setting `rng`? — cybernetic.nomad, Nov 23 '22 at 16:21
added: If ThisWorkBook.Worksheets("Log").UsedRange.Columns.Count > 10 Then Set rng = Range(Cells(1, Columns.Count).End(xlToLeft).Offset(0,-10), Cells(13,Columns.Count).End(xlToLeft).Offset(0,-10)) Else Set rng = *blank range* — bm922, Nov 23 '22 at 17:11
seemed to help somewhat but still getting bugs where it gives that same Run-time error. Seems like that UsedRange.Columns.Count isn't alway accurate, counting black columns somehow — bm922, Nov 23 '22 at 17:31

score 0 · Answer 1 · answered Nov 24 '22 at 05:29

Try this

Sub tenMinBack()


Application.CutCopyMode = False
Application.ScreenUpdating = False

ThisWorkbook.Worksheets("Log").Select
col = Cells(1, Columns.Count).End(xlToLeft).Column - 10
If col > 0 Then
    Range(Cells(1, col), Cells(13, col)).Copy
    ThisWorkbook.Worksheets("Prices").Range("D3:D15").PasteSpecial xlValues
    ThisWorkbook.Worksheets("Log").Cells.EntireColumn.AutoFit
    ThisWorkbook.Worksheets("Prices").Cells.EntireColumn.AutoFit
End If
ThisWorkbook.Worksheets("Prices").Select

End Sub

vba copy and paste dynamic ranges

1 Answers1