Copy and paste last row

[Martin2]

Hello to all.

¿how can I copy the last row (with data, obviously) in sheet1 in a
workbook named "first" in the first blank row of the sheet1 in another
workbook named "second"?


I try with this code but I am a beginner and my excel is an spanish
version. Doesn´t works:


Sub CopyIt()
Dim rngCopy As Range, rngDest As Range, wsName As String

With Worksheets("Sheet1")
Set rngCopy = .Range("A" & .Cells(Rows.Count, "A").End(xlUp).Row)
'the last row with data (by reference to column A)
wsName = .Range("B1").Value
'get destination sheet name from a given cell
End With

Set rngDest = Worksheets(wsName).Range("A1")
'always paste from A1

rngCopy.EntireRow.Copy Destination:=rngDest
'do the copy

End Sub



Thanks in advance.

Mart.

 

[Bob Phillips]

Mart,

A couple of things. VBA is in English, regardless of the Excel language
version. The things that will (might?) be in Spanish are Excel objects such
as the Sheet names. So the sheet may not be Sheet1, look in Excel and see
what it is actually called.

Other than that, your code works exactly as you describe for me. What are
you experiencing?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Martin2]

Thanks Bob,

When I try the macro it stops in

Set rngDest = Worksheets(wsName).Range("A1")


and says:


error '9'


I am very new in this excel world and i think the problem is I don´t know
the correct syntax for my name files.


Thanks other time.


Mart.

 

[Bob Phillips]

Martin,

There is nothing wrong with that code, as I say it works for me. The only
thing that I can think of is that the sheet name in cell B1 is not correct.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Martin2]

Bob, if I have only two books and with one sheet everyone, named:

principal1 hoja1

secundario2 hoja1

Iwant to copy last row in principal1 hoja1 into first blank row in
secundario2 hoja1

Can you re-write for me the code?.


Thanks a lot


Mart.


(Sheet is - hoja - in spanish)

 

[Bob Phillips]

Sub CopyIt()
Dim rngCopy As Range, rngDest As Range, wsName As String

With Worksheets("Hoja1")
Set rngCopy = .Range("A" & .Cells(Rows.Count, "A").End(xlUp).Row)
'the last row with data (by reference to column A)
wsName = .Range("B1").Value
'get destination sheet name from a given cell
End With

Set rngDest = Worksheets(wsName).Range("A1")
'always paste from A1

rngCopy.EntireRow.Copy Destination:=rngDest
'do the copy

End Sub

and make sure that cell B1 on Hoja1 has the value Hoja2

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Martin2]

Thanks Bob, it works now.


Mart.