Runtime error 1004 when selct a range

[blaksnm]

My macro:
Sheets("Formulas").Range("FormulaTransit").Copy
Sheets("List").Select
Range("A1").Select
Set tbl = ActiveCell.CurrentRegion
tbl.Offset(tbl.Rows.Count, 0).Resize(1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

fails in line "Range("A2").select": "Run Time error 1004

I want to paste the result of my formula as value in the bottom of the list in another sheet

Any clue how to make it work anyone?

Best regards Snoopy

 

[blaksnm]

That's odd as there is no selecting in the code I posted. Which line causes it?

Here the macro stops: tbl.Offset(tbl.Rows.Count, 0).Resize(1, 1).Select 'PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

 

[blaksnm]

That's odd as there is no selecting in the code I posted. Which line causes it?

tbl.Offset(tbl.Rows.Count, 0).Resize(1, 1).Select 'PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

 

[blaksnm]

tbl.Offset(tbl.Rows.Count, 0).Resize(1, 1).Select 'PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

tbl.Offset(tbl.Rows.Count, 0).Resize(1, 1).Select 'PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Here is the essence of my macro:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
ActiveSheet.Unprotect 'DrawingObjects:=False, Contents:=True, Scenarios:=False, AllowFormattingColumns:=True, AllowFormattingRows:=True
Application.ScreenUpdating = False

' Bla bla bla - does some stuff

If Not Intersect(Target, Range("K" & Rw)) Is Nothing Then
Cancel = True

Sheets("Formula").Range("Transit").Copy'(=range("a5:R5") in sheet("Formula")
Set tbl = Sheets("List").Range("A1").CurrentRegion
tbl.Offset(tbl.Rows.Count, 0).Resize(1, 1).Select 'PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If

Application.ScreenUpdating = True
'ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowInsertingHyperlinks:=True


'Does some more stuff
End Sub

My aim is to copy at formula-set an paste the values in the bottom og a list (in sheet("List)

Br Snoopy

 

[RoryA]

Why did you put Select there instead of the pastespecial?

 

[blaksnm]

Why did you put Select there instead of the pastespecial?

I did it due not to damish anything (as a test) - if select worked out - thren I would change to paste

 

[RoryA]

Unfortunately all that did was reintroduce essentially the same problem we were trying to fix - namely that you can't select a range on a non-active sheet.

 

[blaksnm]

Unfortunately all that did was reintroduce essentially the same problem we were trying to fix - namely that you can't select a range on a non-active sheet.

Thanks
Ill look into this advice
Br S