Macro to Append Data

[ExcelQ1]

In need of macro that will do the following:

(1) Copy data from Book1.Worksheet1 in range a1:j2
(2) Paste values into Book2.Worksheet1 in range a1:j2

The values in (1) refresh periodically. When they do, I'd like to be able to hit the macro button proceed to (2) again, but this time the data will append to the bottom.

Thanks in advance for the help.

[jaslake]

Hi ExcelQ1

Try this

Option Explicit
Sub UpDate_Book2()
    Dim wbData As Workbook
    Dim wbTarget As Workbook
    Dim wsData As Worksheet
    Dim wsTarget As Worksheet
    Dim LR As Long
    Dim MyPath As String
    Application.ScreenUpdating = False
    MyPath = ActiveWorkbook.Path
    Set wbData = ThisWorkbook
    Application.Workbooks.Open (MyPath & "\" & "Book2.xls")
    Set wbTarget = Workbooks("Book2.xls")
    For Each wsData In wbData.Sheets
        For Each wsTarget In wbTarget.Sheets
            If wsTarget.Name = wsData.Name Then
                LR = wsTarget.Range("A" & Rows.Count).End(xlUp).Row
                If Not LR = 1 Then LR = LR + 1
                wsData.Range("A1:J2").Copy Destination:=wsTarget.Range("A" & LR)
                Exit For
            End If
        Next wsTarget
    Next wsData
    wbTarget.Close savechanges:=True
    Application.ScreenUpdating = True
End Sub

Let me know of issues.

John

[ExcelQ1]

Thank you, John. That helped tremendously. I do have one follow-up question...
In the aforementioned code, we export the range "A1:J2" every time the macro is executed.

Within that range, A1:J1 is merely a header row. The first time the macro is executed, I'd like it to export the entire "A1:J2" range (including header row). However, for every subsequent execution I only want to export the range "A2:J2" because I do not need to repeat the header row. Any thoughts on how I do this?

[jaslake]

Hi ExcelQ1

See if this works for you

Option Explicit
Sub UpDate_Book2()
    Dim wbData As Workbook
    Dim wbTarget As Workbook
    Dim wsData As Worksheet
    Dim wsTarget As Worksheet
    Dim LR As Long
    Dim MyPath As String
    Application.ScreenUpdating = False
    MyPath = ActiveWorkbook.Path
    Set wbData = ThisWorkbook
    Application.Workbooks.Open (MyPath & "\" & "Book2.xls")
    Set wbTarget = Workbooks("Book2.xls")
    For Each wsData In wbData.Sheets
        For Each wsTarget In wbTarget.Sheets
            If wsTarget.Name = wsData.Name Then
                wsData.Range("A1:J1").Copy Destination:=wsTarget.Range("A1")
                LR = wsTarget.Range("A" & Rows.Count).End(xlUp).Row + 1
                wsData.Range("A2:J2").Copy Destination:=wsTarget.Range("A" & LR)
                Exit For
            End If
        Next wsTarget
    Next wsData
    wbTarget.Close savechanges:=True
    Application.ScreenUpdating = True
End Sub

John

[MacroLover]

Hi John,

I used below code to append data from one workbook to another, but it's pasting the data into the target workbook. I copied the code without Sub UpDate_Book2() line, could that be the problem?

[jaslake]

Hi MacroLover

Welcome to the Forum!

Unfortunately, Rule # 2 reads... Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.

[ExcelQ1]

Excellent! This is just what I need. Thank you so much.

[taudano]

Hello, looking for help with macro code to append a list of neighborhoods to a list of keywords. For example:

Neighborhood Names:

Mountainbrook
Myers Park
Nevin Community
Newell
Newell South

Keywords:
keyword1
keyword 2
keyword 3

Results:
Mountainbrook keyword1
Myers Park keyword1
Nevin Community keyword1
Newell keyword1
Newell South keyword1
Mountainbrook keyword2
Myers Park keyword2
Nevin Community keyword2
Newell keyword2
Newell South keyword2
Mountainbrook keyword3
Myers Park keyword3
Nevin Community keyword3
Newell keyword3
Newell South keyword3

In addition, would like to output the reverse scenario:

keyword1 Mountainbrook
keyword1 Myers Park
keyword1 Nevin Community
keyword1 Newell
keyword1 Newell South
keyword2 Mountainbrook
keyword2 Myers Park
keyword2 Nevin Community
keyword2 Newell
keyword2 Newell South
keyword3 Mountainbrook
keyword3 Myers Park
keyword3 Nevin Community
keyword3 Newell
keyword3 Newell South

I have the data on separate tabs in a spreadsheet - one tab for keywords (column b) and one tab for neighborhood names (column a).

Thanks!
Tiffany

[jaslake]

Hi Tiffany
Welcome to the Forum!

Unfortunately, Rule # 2 reads... Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.

Please start your own thread. If you wish, you can PM me with a link and I'll look at it.