Copy data from a tab to another with vba code

Question

Hi I try to copy some data from other tabs in to a new one to create a database of my clients but I get this error: run time error 424 object required.

this one is fine: ' Vezi care este urmatorul rand NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1

'Valorile importante din Registru facturi F2 -data, F3-nyumar factura, B10 -client, B11-reg com, B12-CUI, B13 adresa, B14 telefon, B15 email, B16 cont bancar, C36  suma achitat, C37 rest plata, D32 valuta facturii

WS2.Cells(NextRow, 1).Resize(1, 14).Value = Array(WS1.Range("F2"), WS1.Range("F3"), WS1.Range("B10"), Range("InvTot"), WS1.Range("B11"), WS1.Range("B12"), WS1.Range("B13"), WS1.Range("B14"), WS1.Range("B15"), WS1.Range("B16"), WS3.Range("C36"), WS3.Range("C37"), WS3.Range("D32"), WS3.Range("C38"))

but this one returns an error:

 ' Vezi care este urmatorul rand Baza de date
NextRow = W7.Cells(Rows.Count, 1).End(xlUp).Row + 1
'Valorile importante din Baza de date
    WS7.Cells(NextRow, 1).Resize(1, 14).Value = Array(WS7.Range("C5"), WS7.Range("C23"), WS7.Range("C24"), Range("InvTot"), WS7.Range("C25"), WS7.Range("C26"), WS7.Range("C28"), WS7.Range("C29"), WS7.Range("C30"), WS7.Range("C31"), WS7.Range("C32"), WS7.Range("C33"), WS7.Range("C6"), WS7.Range("C7"), WS7.Range("C8"), WS7.Range("C9"), WS7.Range("C10"), WS7.Range("C11"), WS7.Range("C12"), WS7.Range("C13"), WS7.Range("C14"), WS7.Range("C15"), WS7.Range("C16"), WS7.Range("C17"), WS7.Range("C18"), WS7.Range("C19"))

Sub PostToRegister()

Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim WS3 As Worksheet
Dim WS4 As Worksheet
Dim WS5 As Worksheet
Dim WS6 As Worksheet
Dim WS7 As Worksheet
Set WS1 = Worksheets("Invoice")
Set WS2 = Worksheets("Registru Facturi")
Set WS3 = Worksheets("Customers")
Set WS4 = Worksheets("chitanta")
Set WS5 = Worksheets("chitanta diferenta")
Set WS6 = Worksheets("valuta")
Set WS7 = Worksheets("Baza de date Clienti")

' Vezi care este urmatorul rand
NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1

'Valorile importante din Registru facturi F2 -data, F3-nyumar factura, B10 -client, B11-reg com, B12-CUI, B13 adresa, B14 telefon, B15 email, B16 cont bancar, C36  suma achitat, C37 rest plata, D32 valuta facturii
WS2.Cells(NextRow, 1).Resize(1, 14).Value = Array(WS1.Range("F2"), WS1.Range("F3"), WS1.Range("B10"), Range("InvTot"), WS1.Range("B11"), WS1.Range("B12"), WS1.Range("B13"), WS1.Range("B14"), WS1.Range("B15"), WS1.Range("B16"), WS3.Range("C36"), WS3.Range("C37"), WS3.Range("D32"), WS3.Range("C38"))

 ' Vezi care este urmatorul rand Baza de date
NextRow = W7.Cells(Rows.Count, 1).End(xlUp).Row + 1
'Valorile importante din Baza de date
WS7.Cells(NextRow, 1).Resize(1, 14).Value = Array(WS7.Range("C5"), WS7.Range("C23"), WS7.Range("C24"), Range("InvTot"), WS7.Range("C25"), WS7.Range("C26"), WS7.Range("C28"), WS7.Range("C29"), WS7.Range("C30"), WS7.Range("C31"), WS7.Range("C32"), WS7.Range("C33"), WS7.Range("C6"), WS7.Range("C7"), WS7.Range("C8"), WS7.Range("C9"), WS7.Range("C10"), WS7.Range("C11"), WS7.Range("C12"), WS7.Range("C13"), WS7.Range("C14"), WS7.Range("C15"), WS7.Range("C16"), WS7.Range("C17"), WS7.Range("C18"), WS7.Range("C19"))

End Sub

Shai Rado Shai Rado · Accepted Answer · 2017-01-23T11:48:28

In you first WS2.Cells(NextRow, 1).Resize(1, 14).Value you are trying to put in 14 array elements, in your second WS7.Cells(NextRow, 1).Resize(1, 14).Value you are actually trying to "squeeze" 26 array elements in 14 cells - Excel screams for help !

Another issue, NextRow = W7.Cells(Rows.Count, 1).End(xlUp).Row + 1 I think W7 should be WS7 worksheet, and also it's safer to fully qualify also the Rows.Count to WS7.Rows.Count.

Change your last 2 lines to:

 ' get last row from WS7 (not W7)
NextRow = WS7.Cells(WS7.Rows.Count, 1).End(xlUp).Row + 1

' resize to 26, as you want to put in 26 array elements
WS7.Cells(NextRow, 1).Resize(1, 26).Value = Array(WS7.Range("C5"), WS7.Range("C23"), WS7.Range("C24"), Range("InvTot"), WS7.Range("C25"), WS7.Range("C26"), WS7.Range("C28"), WS7.Range("C29"), WS7.Range("C30"), WS7.Range("C31"), WS7.Range("C32"), WS7.Range("C33"), WS7.Range("C6"), WS7.Range("C7"), WS7.Range("C8"), WS7.Range("C9"), WS7.Range("C10"), WS7.Range("C11"), WS7.Range("C12"), WS7.Range("C13"), WS7.Range("C14"), WS7.Range("C15"), WS7.Range("C16"), WS7.Range("C17"), WS7.Range("C18"), WS7.Range("C19"))

Copy data from a tab to another with vba code

1 Answers