Copy value to another workbook

Question

why twb cells(i,7) value don't show up in the extwb(pasterowIndex, 1)?

can you make this

twb.Sheets(1).Activate
Cells(i, 7).Select
Selection.Copy
extwb.Sheets(8).Activate
Cells(pasterowIndex, 1).Select
ActiveSheet.Paste

code little simple, because I have many value to copy?

Sub historical()
    Dim twb As Workbook
    Dim extwb As Workbook
    Dim extwb3 As Worksheet
    Dim i As Long
    Dim pasterowIndex As Long
    pasterowIndex = 2

    Set twb = Workbooks.Open("C:\Users\faisal.abraham\Documents\Travel\PUPD.xlsx")
    Set extwb = Workbooks.Open("C:\Users\faisal.abraham\Documents\Travel\PIRD.xlsx")

    With twb.Sheets("Actuary_Travel_Voucher_Engineer")
        For i = 8 To Cells(Rows.Count, 1).End(xlUp).Row
            If twb.Cells(i, 23).Value = "PERMATA HIJAU  " And Cells(i, 28).Value = "PAID" Then
            twb.Sheets(1).Activate
            Cells(i, 7).Select
            Selection.Copy
            extwb.Sheets(8).Activate
            Cells(pasterowIndex, 1).Select
            ActiveSheet.Paste

            pasterowIndex = pasterowIndex + 1
            End If
        Next i

        pasterowIndex = 2
    End With
End Sub

My recommendation: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Pᴇʜ, Oct 12 '18 at 09:59
I can see that you are missing a `End If`. I think it should after the line `pasterowIndex = pasterowIndex + 1` — Wizhi, Oct 12 '18 at 10:05
@Wizhi sorry i am forgot to input in this question, but in my original code it's already here — Faisal Abraham, Oct 12 '18 at 10:13
I'm surprised `twb.Cells(i, 23).Value` works as `twb` is a workbook not a worksheet. — SJR, Oct 12 '18 at 10:20
@SJR I'm pretty sure too, that `twb.Cells(i, 23).Value` will throw a *Run time error “483” “Object doesn't support this property or method”* — Pᴇʜ, Oct 12 '18 at 10:56
@Pᴇʜ - aye, OP has some explaining to do. Also don't understand why `pasterowIndex` is reset. — SJR, Oct 12 '18 at 11:16

score 1 · Accepted Answer · answered Oct 12 '18 at 11:40

This code

    twb.Sheets(1).Activate
Cells(i, 7).Select
Selection.Copy
extwb.Sheets(8).Activate
Cells(pasterowIndex, 1).Select
ActiveSheet.Paste

can be replaced with

 twb.Sheets(1).cells(i,7).copy extwb.sheets(8).cells(pasteindex,1)

Which doesn't fix the other issues but at least makes the code less painful

Copy value to another workbook

1 Answers1