How to copy data from one spreadsheet to another with VBA

Asked

Viewed 525 times

-1

I have a problem to copy data from one spreadsheet to another, because either it does not run or simply does not copy, so, now I am using the code below, but it gives error in the definition of lr, will be that could help me, already thank Situation: I would like to copy the D8 data to the last cell used and paste in the second tab of B3 onwards, only this, however I am having a difficulty, the most recent tried code was.:

Sub CopyCurrentRegion()

   Dim dest As Worksheet

Dim lr As String

   Dim Source As Worksheet

   Set dest = Sheets("Receita Projetos")

   Set Source = Sheets("Análise de Wip")

   lr = Worksheets("Análise de Wip").Range("D8" & Rows.Count).End(xlUp).Row + 1

   dest.Range("B3" & lr).Value = Source.Range("D8").Value

End Sub
excel vba excel-vba

1 answer

0


Good morning,

Follow code that can help:

Sub CopyCurrentRegion()
    Dim dest As Worksheet
    Dim lr As Variant
    Dim source As Worksheet

    Set dest = Sheets("Receita Projetos")
    Set source = Sheets("Análise de wip")

    ultimalinha = source.Cells(Rows.Count, "D").End(xlUp).Row
    lr = source.Range("D8:D" & ultimalinha)

    dest.Range("B3:B" & UBound(lr)) = lr

End Sub

  • So, first of all thank you for trying to help, but this Ubound, he says that a matrix was expected, before he said that df was not defined)

  • Sorry, I was really having an incoherence in the code, I changed the variables. I fixed now, Ubound is in the variable lr gets Ubound(lr). Ubound returns the size of the vector or matrix, so the variable that should be inside this case is lr.

  • Hello thanks so much for the help, but another question, if I want to do with more columns, I would have to repeat everything again or I could simply adapt my code?

  • If they are columns in sequence, you can adjust the code for lr to capture an array, in this case only need adjustment, if they are separate columns would have to do one by one, as it would be necessary to count the number of rows of each one.

  • Ah yes perfect, thank you so much for your help

Browser other questions tagged excel vba excel-vba

You are not signed in. Login or sign up in order to post.