Copying cell values from one Workbook into another

Question

I have the following code that I've written to take some names and use them to populate a timesheet.

Sub InitNames()
    Dim i As Integer
    i = 0
    Dim name As String

    Windows("Employee Data.xlsx").Activate
    Sheets("Employees").Select
    Range("A2").Select
    Do Until IsEmpty(ActiveCell)
        name = ActiveCell.Value
        Workbooks("Timesheet").Sheets("ST").Range("A9").Offset(i * 9).Value = name
        ActiveCell.Offset(1, 0).Select
    Loop
End Sub

Basically, the cells in the target sheet are spaced 9 rows away from each other, so the first name would go in cell A9, the second in A18, the third in A27, and so on. I'm sure I'm missing something incredibly simple, but I'm not getting any feedback from Excel whatsoever (no error messages). The cells in the timesheet are merged cells, but I cannot change them (locked by the owner), but I don't think that has anything to do with it.

EDIT: I added a line: OriginalValue = Workbooks("Timesheet").Sheets("ST").Range("A10").Offset((x - 2) * 9, 0).Value so I could watch to see what values were being overwritten in my Timesheet and I noticed something interesting: OriginalValue only grabs the first cell's text (A9), thereafter, for every cell (A18, A27, etc.) the debugger indicates that OriginalValue = "" even though those cells also contain names. However, when I open another worksheet and reference A9, A18, etc., I AM pulling the names.

EDIT 2: I modified the test line to read Workbooks("Timesheet").Sheets("ST").Range("A" & ((x - 1) * 9)).Value = "Test" which does change the values in all the target cells. Why would VBA allow me to assign "Test" to a cell value but not the names in the other worksheet?

I know this question was to get working code, but you should never rely on `.Activate` or `.Select`. http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros — Kyle, Mar 17 '15 at 21:23

score 0 · Accepted Answer · answered Mar 17 '15 at 21:33

0

Try something like this. It will accomplish the task you are requesting without using .Select or .Activate

Sub InitNames()
Dim i As Integer
Dim Wksht as Worksheet
i = 0

Set Wksht = Workbooks("Employee Data.xlsx").Sheets("Employees")
For i = 2 to Wksht.Range("A" & Wksht.Rows.Count).End(xlUp).Row
    Workbooks("Timesheet").Sheets("ST").Range("A9").Offset((i-2) * 9,0).Value = Wksht.Range("A" & i).Value
Next i
End Sub

answered Mar 17 '15 at 21:33

Kyle

2,543
2
16
31

I tried this solution, and while it populates the first cell with the first name, it doesn't have any effect on the other cells. Any idea what might be causing this? – Steven Mar 18 '15 at 13:32
So I don't know why, but it seems that using Offset was the issue. I modified the reference to the target cell to use the same syntax you used to grab the name values, `("A" & I)`. Solved the problem. So I guess you cannot use Offset that way (although I used it to clear the very same cells just fine). Thank you! – Steven Mar 18 '15 at 14:25

Copying cell values from one Workbook into another

1 Answers1