Subscript out of range, and more
John,
Simply paste values - no need to loop. Below is how I would do it - copy values from Account
Activity to the bottom of Transactions.
HTH,
Bernie
MS Excel MVP
Public Sub CopyAccountActivitytoTransactions2()
Dim numRows As Long
Dim Sht1 As Worksheet
Dim Sht2 As Worksheet
Dim StartHere As Long
Set Sht1 = Worksheets("Transactions")
Set Sht2 = Worksheets("Account Activity")
StartHere = Sht1.Cells(Rows.Count, 1).End(xlUp).Row + 1
numRows = Sht2.Cells(Rows.Count, 1).End(xlUp).Row - 1
Sht1.Cells(StartHere, 1).Resize(numRows).Value = _
Sht2.Cells(2, 4).Resize(numRows).Value
Sht1.Cells(StartHere, 2).Resize(numRows).Formula = _
"=IF(AND(Event=""Transfer"",Amount<0),""Sell Shares""," & _
" IF(Event=""Dividend"",""Reinvest"",""Buy Shares""))"
Sht1.Cells(StartHere, 3).Resize(numRows).Value = _
Sht2.Cells(2, 2).Resize(numRows).Value
Sht1.Cells(StartHere, 4).Resize(numRows).Value = ""
Sht1.Cells(StartHere, 5).Resize(numRows).Value = _
Sht2.Cells(2, 1).Resize(numRows).Value
Sht1.Cells(StartHere, 6).Resize(numRows).Value = _
Sht2.Cells(2, 5).Resize(numRows).Value
Sht1.Cells(StartHere, 7).Resize(numRows).Value = _
Sht2.Cells(2, 7).Resize(numRows).Value
Sht1.Cells(StartHere, 8).Resize(numRows).Value = _
Sht2.Cells(2, 6).Resize(numRows).Value
Sht1.Cells(StartHere, 9).Resize(numRows).Formula = _
"=IF(Security=""Some Stock Fund"",Amount/SharePrice,UnitsShares)"
Sht1.Cells(StartHere, 10).Resize(numRows).Formula = _
"'=IF(Security=""Some Stock Fund""," & _
"VLOOKUP(ProcessDate,Prices,5,FALSE),UnitPrice )"
Sht1.Cells(StartHere, 11).Resize(numRows).Value = _
Sht2.Cells(2, 3).Resize(numRows).Value
End Sub
"John Pierce" wrote in message
...
If I "pick up" the data as a range, how then do I "lay it down" on the
other sheet as individual cells?
|