Solved

Transfer info to another sheet

Posted on 2014-07-28
2
113 Views
Last Modified: 2014-07-28
Hi,

I have a ss where i want rows A23:G23 to be transferred to tab "transfer"

I need the data to append, so first append into A2, then any further appends, a3, a4

So the user will press the button "Add", the code transfers "A23:G23"  to "A2:G2" in sheet "transfer"

If they press add again, it transfers to A3:G3

If the user hits add twice, but the none of the data in A23:G23 changes, then the user gets a msg box "This is the same data, are you sure you want to send", if Yes, Send, if no, exit sub

Many thanks!
0
Comment
Question by:Seamus2626
2 Comments
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
Comment Utility
Hi,

pls try

Sub macro()
Dim arrData As Variant
Dim arrTransfer As Variant
LastTransferRow = Sheets("transfer").Range("A" & Rows.Count).End(xlUp).Row
arrData = Join(WorksheetFunction.Index(Sheets("data").Range("A23:G23").Value, 1, 0), ";")
arrTransfer = Join(WorksheetFunction.Index(Sheets("transfer").Range("A" & LastTransferRow & ":G" & LastTransferRow).Value, 1, 0), ";")
If arrData = arrTransfer Then
    Res = MsgBox("This is the same data, are you sure you want to send", vbYesNo)
    If Res = vbYes Then
        Sheets("data").Range("A23:G23").Copy Sheets("transfer").Range("A" & LastTransferRow + 1)
    Else
        Exit Sub
    End If
Else
    Sheets("data").Range("A23:G23").Copy Sheets("transfer").Range("A" & LastTransferRow + 1)
End If
End Sub

Open in new window

Regards
0
 

Author Closing Comment

by:Seamus2626
Comment Utility
Legend!!

Thanks!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now