Adding data to the same workbook

I have a macro that copy the content of the current worksheet to another "target" workbook. This works fine:

n1 = ActiveWorkbook.Name
Selection.Copy
Workbooks.Add Template:="MyTemplate.xlt"
Workbooks(Workbooks.Count).Activate
n2 = ActiveWorkbook.Name
ActiveSheet.Paste
' return to the source workbook
Workbooks(n1).Activate

Now, I would like to modify this macro so that if the user execute it more times from the same workbook, the content of the current sheet must be appended at the end of the same target n2 workbook, "collecting" all the contents.

The questions I can't answer are..
1 - How can I be sure to create the new target workbook only the first time the macro executes
2- How can I append the content at the end of the target workbook without overwriting the existing one

thank you

Answer this question

Adding data to the same workbook

Andy Pham
Thank you! It works, I declared the variables in the Option Explicit region and they are persistent.

But now when I try to append the content at the end of the sheet with...

Workbooks(TargetWb).Activate
ActiveSheet.Paste

...I always overwrite the previous content because the active cell is always A1. When I execute the macro the second time I don't want to overwrite the content.
I want to add the content at the end of the sheet, below the rows pasted before, not from the cell A1.
I need some way to move the cursor at the end of the pasted data.

Something like:
If Count > 1 Then
Range(<firstCellInColumn_A_AfterThePreviouslyPastedRows>).Select
ActiveSheet.Paste
End If

<firstCellInColumn_A_AfterThePreviouslyPastedRows> could be A24 or A35...
mrayyan
sorry Duck Thing, it wasn't helpful.
This is my current macro (simplified):

Option Explicit
Dim Count As Integer
Dim TargetWb As String

Sub MultiplePast()

Dim n1 As String
n1 = ActiveWorkbook.Name

Count = Count + 1

Selection.Copy

If Count = 1 Then

Workbooks.Add Template:= "MyTemplate.xlt"
Workbooks(Workbooks.Count).Activate
TargetWb = ActiveWorkbook.Name
Range("A2").Select
ActiveSheet.Paste

' is there a way to know which is the last cell or the last row I have pasted

Else

Workbooks(TargetWb).Activate

'---------------------------
Range( ).Select
' this is where I cannot do the right thing!
ActiveSheet.Paste
' I don't want to overwrite the previously pasted rows!!!
'---------------------------

End If

' return to the first workbook
Workbooks(n1).Activate

End Sub
Leaf.

Here's a piece of code which demonstrates this:

Option Explicit

Public Sub Main()
    With Sheet1
        .Range("A1").Value = "TESTING!"
        .Range("A1").Copy
        .Paste .Range("B2")
    End With
    Application.CutCopyMode = Empty
End Sub
Nilesh Ingale
Hi,

Not sure about the second question but on Q1 you could add a varaible at the beginning of the macro, say 'Count' and increment it once the macro runs. Add in an IF statement and if 'Count' is 1 then create new target workbook otherwise don't.
Adding data to the same workbook

Answer this question