Need VBA to copy active row and paste it in last row of another sheet

[forginganewone]

Need macro to copy active row and paste it in xfiles-xsheets-lastrow. With same format and everything.

I've tried this :
<code>Option Explicit

Sub copypasterow()
Dim wbTarget As Workbook
Selection.EntireRow.Copy
Set wbTarget = Workbooks.Open("C:\YOURPATH\FILE.xlsx")
wbTarget.Sheets("Sheet1").Range("B1").End(xlDown).Offset(1, -1).PasteSpecial
End Sub


And this:

<code>Option Explicit

Sub copypasterow()
Dim wbTarget As Workbook
Dim Filetarget As String
Dim WorksheetEndRow As Integer


Filetarget = "C:\Users\francesco.dinh\Downloads\asdf.xlsx"


Selection.EntireRow.Copy
Set wbTarget = Workbooks.Open(Filetarget)
WorksheetEndRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("A" & WorksheetEndRow + 1).PasteSpecial
Application.CutCopyMode = False
End Sub



It gives me following error :</code></code>[h=2]runtime error 1004 pastespecial method of range class failed[/h]<code><code></code></code>

 

[Rick Rothstein]

Untested, but I think this will copy the active row to the first empty row under the existing data on Sheet1 of the workbook referenced by the wbTarget variable...

[table="width: 500"]
[tr]
	[td]ActiveCell.EntireRow.Copy Rows(wbTarget.Sheets("Sheet1").Cells.Find("*", , xlFormulas, , xlRows, xlPrevious, , , False).Offset(1).Row).Cells(1)[/td]
[/tr]
[/table]

 

[forginganewone]

Untested, but I think this will copy the active row to the first empty row under the existing data on Sheet1 of the workbook referenced by the wbTarget variable...

[TABLE="width: 500"]
<tbody>[TR]
[TD]ActiveCell.EntireRow.Copy Rows(wbTarget.Sheets("Sheet1").Cells.Find("*", , xlFormulas, , xlRows, xlPrevious, , , False).Offset(1).Row).Cells(1)[/TD]
[/TR]
</tbody>[/TABLE]

It selects active row and opens the target sheet goes in last row but does not paste. Secondly everytime i run the macro it reopens the target workbook, is there someway to disable it ?

 

[Rick Rothstein]

It selects active row and opens the target sheet goes in last row but does not paste.

I have almost never had to work with multiple open workbooks, so I am not completely sure of what the problem is... maybe this will complete the copying...

[table="width: 500"]
[tr]
	[td]ActiveCell.EntireRow.Copy [B][COLOR="#FF0000"]wbTarget.[/COLOR][/B]Rows(wbTarget.Sheets("Sheet1").Cells.Find("*", , xlFormulas, , xlRows, xlPrevious, , , False).Offset(1).Row).Cells(1)[/td]
[/tr]
[/table]

Secondly everytime i run the macro it reopens the target workbook, is there someway to disable it ?

I think the workbook must be open in order to copy to it. What I think you would need to do is now save the workbook and then close it. I believe executing these two lines of code after executing the above code will do that...

wbTarget.Save
wbTarget.Close

 

[forginganewone]

I have almost never had to work with multiple open workbooks, so I am not completely sure of what the problem is... maybe this will complete the copying...

ActiveCell.EntireRow.Copy wbTarget.Rows(wbTarget.Sheets("Sheet1").Cells.Find("*", , xlFormulas, , xlRows, xlPrevious, , , False).Offset(1).Row).Cells(1)

I am getting Run-time error '438' : object doesn't support this property or method. On selecting debug it highlights the row of your code.

ActiveCell.EntireRow.Copy wbTarget.Rows(wbTarget.Sheets("Sheet1").Cells.Find("*", , xlFormulas, , xlRows, xlPrevious, , , False).Offset(1).Row).Cells(1)

 

[Rick Rothstein]

I have almost never had to work with multiple open workbooks, so I am not completely sure of what the problem is... maybe this will complete the copying...

ActiveCell.EntireRow.Copy wbTarget.[B][COLOR="#FF0000"]Sheets("Sheet1").[/COLOR][/B]Rows(wbTarget.Sheets("Sheet1").Cells.Find("*", , xlFormulas, , xlRows, xlPrevious, , , False).Offset(1).Row).Cells(1)

I am getting Run-time error '438' : object doesn't support this property or method. On selecting debug it highlights the row of your code.

Sorry, I forgot to include the sheet reference... try adding what I show in red above and see if that makes it work.

 

[Fluff]

Another option

Sub copypasterow()
Dim wbTarget As Workbook
Dim Filetarget As String
Dim rng As Range

Filetarget = "C:\Users\francesco.dinh\Downloads\asdf.xlsx"
Set rng = activecell

Set wbTarget = Workbooks.Open(Filetarget)
rng.EntireRow.Copy Rows(Cells.find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Offset(1).Row)
End Sub

This will copy to whatever the active sheet is in the target workbook.

 

[forginganewone]

Sorry, I forgot to include the sheet reference... try adding what I show in red above and see if that makes it work.

It removed the error but was not pasting anything again.

 

[forginganewone]

Another option

Sub copypasterow()
Dim wbTarget As Workbook
Dim Filetarget As String
Dim rng As Range

Filetarget = "C:\Users\francesco.dinh\Downloads\asdf.xlsx"
Set rng = activecell

Set wbTarget = Workbooks.Open(Filetarget)
rng.EntireRow.Copy Rows(Cells.find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Offset(1).Row)
End Sub

This will copy to whatever the active sheet is in the target workbook.

YES !!! Atlast it works !!
Fluff one last thing what if do not want it to open the workbook and just paste there, like if i keep the workbook open so it doesn't open the workbook every time i run the macro. SHould i only delete this line ?
Set wbTarget = Workbooks.Open(Filetarget

 

[Fluff]

Fluff one last thing what if do not want it to open the workbook and just paste there, like if i keep the workbook open so it doesn't open the workbook every time i run the macro. SHould i only delete this line ?
Set wbTarget = Workbooks.Open(Filetarget

not quite, try

Sub copypasterow()
Dim wsTarget As Worksheet
Dim rng As Range

Set rng = activecell

Set wsTarget = Workbooks("asdf.xlsx").Sheets("Sheet1")
rng.EntireRow.Copy wsTarget.Rows(wsTarget.Cells.find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Offset(1).Row)
End Sub