chizzy42
08-12-2014, 03:18 AM
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
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