Greetings folks !
I've been bashing my head against the wall for awhile on this one. Here's what I'm trying to do :
Sheet1 = Several columns of time stamps
- The user clicks a check box to determine if they want the include this column in a data analysis
- The user presses an 'Analyze' button and it copies the data to a new column in sheet2 where I can do all kinds of wonderful math against it
- Each individual column is then put together in one column on Sheet2 and sorted
The last step is where I'm stuck. The data will need to start in cell A40. Cell A39 is populated by a cell with the string "Sorted Data" in it. My sort algorithm works, but I can't get the data properly into one column starting in A40. Here's the function I'm using for one of the columns :
If Sheet1.[include1].Value = 1 Then
Sheet2.Range("A1") = Sheet1.Range("B5")
Sheet1.Range("B6", "B40").Copy
Sheet2.Range("A2", "A36").PasteSpecial Paste:=xlPasteValues
Sheet2.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
End If
The last line is the problem. It ends up overwriting my data above A40, as if there's nothing in A39.
I've also tried the following :
Sheet2.Range("B6").Offset(Application.WorksheetFunction.Count(Range("B6").EntireColumn), 0).PasteSpecial paste :=xlPasteValues
The above works when I'm inserting the time stamps in the columns for Sheet1 through a Command button click with this :
Sub Button1_Click() Sheet1.Range("B6").Offset(Application.WorksheetFunction.Count(Range("B6").EntireColumn), 0).Value = Time
End Sub
It doesn't seem to like that I'm using Sheet2.Range instead of Activesheet.Range or just Range and gives me an user object defined error. I cannot use either of the former options since Sheet2 is going to be hidden from the person using the workbook.
Any help would be appreciated !
Thanks,
-Budd
Bookmarks