Error 1004 Application defined or object defined when code runs

[chizzy42]

Hi First time posting, let me know please if im doing anything wrong.

Im trying to run this code from a button on an excel spreadsheet which is running active(workbook y). When clicked the code picks the values from book1.xlsm(workbook x) and pastes the data in another workbook called bellcurve.xls (workbook y), when i run it i get a run time error 1004 Application defined or object defined error.i put msgbox boxes after the workbook and worksheet variables respectively and got back bellcurve.xls for y and prob for ws2...prob is the name of the sheet.

i tried running a variant of the code with the button on book1.xlsm which opened the bellcurve.xls and transfered column data ok, problem is book1 will have random data which bellcurve.xls will have to pull in to graph which is why i have to use bellcurve.xls as the active sheet to run the code.. I think the issue is im not declaring the variables correctly in an active sheet for workbook and sheet. i have marked up the error on the code below.
''''''ERROR HAPPENS ON LINE BELOW

ws2.Cells(Rows.Count, "a").End(xlUp).Offset(0, 0).PasteSpecialAny help on this matter would be greatly appreciated, thanks in advance

ian

Option Explicit
Sub Button6_Click()
Dim x As Workbook, y As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim strWbkName As String
Dim strSheetName As String
Set y = ActiveWorkbook
'assign an object
strWbkName = ActiveWorkbook.Name
MsgBox "Wbk: " & y.Name & vbNewLine & "Name: " & strWbkName
Set ws2 = ActiveSheet
strSheetName = ActiveSheet.Name
MsgBox (strSheetName)
Set x = Workbooks.Open("C:/bellcurve/book1.xlsm")
Set ws1 = x.Sheets("sheet1")
'clear existing values form target book
ws2.Range("A:A").ClearContents
'clear any thing on clipboard to maximize available memory
Application.CutCopyMode = False
Workbooks("book1.xlsm").Worksheets("sheet1").UsedRange.Copy
'Workbooks("book2.xlsx").Worksheets("sheet2").Cells(Rows.Count, "a").End(xlUp).Offset(0, 0).PasteSpecial
'Workbooks(y).Worksheets("ws2").Cells(Rows.Count, "a").End(xlUp).Offset(0, 0).PasteSpecial

''''''ERROR HAPPENS ON LINE BELOW
ws2.Cells(Rows.Count, "a").End(xlUp).Offset(0, 0).PasteSpecial

 'clear any thing on clipboard to maximize available memory
   Application.CutCopyMode = False
End Sub

[westconn1]

try
ws2.Cells(ws2.Rows.Count, "a").End(xlUp).Offset(0, 0).PasteSpecial
note this will overwrite data on the last row, if you want new data on the next row, change offset to (1, 0)

[chizzy42]

Hi westconn1, that change works perfectly....you've just made my day. Im looking to overwrite the first column to update a bell curve...thanks again.

Do i have to mark this as a correct answer somewhere?

Have a nice day

[GTO]

You do not "have to", but it is nice to mark the thread as solved. I believe right above your first post, under 'thread tools', there should be a command akin to 'mark as solved' or similar, that is only available to the OP.