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

 

[Joe4]

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

That isn't even one of the lines of the code you posted.
Is that a typo, or did you not inlcude that section of code?

Can you post a small sample of the range you are trying to copy, and then the range that you are trying to copy it to?
It is important for us to see the structure of your data, and get a full understanding of the location and size of the two ranges.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[RoryA]

There is no need to select (I suspect your code is in a worksheet code module):

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

 

[blaksnm]

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

Sorry, still error
Br Snoopy

 

[RoryA]

What error?

 

[blaksnm]

I run a Norwegian language and this is the error msg:

Run time error '1004'
Select-metoden i Range-klassen mislyktes

(Briefly translated: Select-methode in Range-category? failed)

 

[blaksnm]

That isn't even one of the lines of the code you posted.
Is that a typo, or did you not inlcude that section of code?

Can you post a small sample of the range you are trying to copy, and then the range that you are trying to copy it to?
It is important for us to see the structure of your data, and get a full understanding of the location and size of the two ranges.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Selection of codes Joe - will it help to print the complete macro? (its long)

 

[Joe4]

Can you post data samples of the "from" range and "to" range, like I asked?

 

[RoryA]

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

 

[blaksnm]

Selection of codes Joe - will it help to print the complete macro? (its long)

Here is more:

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