+ Reply to Thread
Results 1 to 3 of 3

Macro for copying rows in sheet1 to end of existing rows in sheet2 based on criteria

Hybrid View

  1. 10-21-2015, 04:32 PM #1
    Karl Gustaf Karsten
    Karl Gustaf Karsten is offline
    Registered User
    Join Date
    04-20-2015
    Location
    Norway
    MS-Off Ver
    360
    Posts
    24

    Macro for copying rows in sheet1 to end of existing rows in sheet2 based on criteria

    Hi there!
    I’m struggling with the last part(s) of a project.

    I have two sheets with data and both contain a row specific ID in all rows of column A. The matching ID can be found in only one row in each sheet. I’m trying to merge rows from the two sheets if they contain the same ID in column A.

    Sheets: “Data1”, “Data2”, and “Merged Data”
    The idea is to organize the “Merged Data” sheet like this:
    Data from “Data1” in the first 62 columns, one blank column, then the data from “Data2” in the next xx columns.

    Problem:
    - The matching IDs are in random rows.
    - There is data in an unknown number of columns in “Data2” (however, no more than 30)
    - The number of rows is unknown in both “Data1” and “Data2”.

    I’ve attached a short example of the workbook, and included how I picture the result.
    The code I’ve managed to put together only inputs the new sheet and gets me the headers – I really have no clue on how to get the rest.
    Sub Merge_Data()

Dim MySheetName As String
    MySheetName = "Merged Data"
    Sheets("Data1").Copy After:=Sheets("Data1")
    ActiveSheet.Name = MySheetName

Dim source As Worksheet
Dim destination As Worksheet
Dim emptyColumn As Long
    
    Set source = Sheets("Data2")
    Set destination = Sheets("Merged Data")

    emptyColumn = destination.Cells(1, destination.Columns.Count).End(xlToLeft).Column
        If emptyColumn > 1 Then
        emptyColumn = emptyColumn + 1
        End If

    source.Range("A1:BA1").Copy destination.Cells(1, emptyColumn + 1)

    Worksheets("Merged Data").Cells(1, emptyColumn).Value2 = "Compatible Data2 data-->"

End Sub
    I can complete the project if someone can help me with this - And, of course, I’ll be very thankful!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-21-2015, 06:25 PM #2
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Macro for copying rows in sheet1 to end of existing rows in sheet2 based on criteria

    Find number of rows in Data1 LR1

    LR1 = Sheets("Data1").Cells(rows.count,1).End(xlUp).Row
    Find number of rows in Data2 LR2

    LR2 = Sheets("Data2").Cells(rows.count,1).End(xlUp).Row
    A simple macro to copy Data1 to MergedData

    Sheets("MergedData").Range("A1:BJ" & 62).Value = Sheets("Data1").Range("A1:BJ" & 62).Value
    A simple Macro to insert a formula in columns 63 onwards to match ID and copy data.

    
Sub Macro1()

LR1 = Sheets("Data1").Cells(Rows.Count, 1).End(xlUp).Row

LR2 = Sheets("Data2").Cells(Rows.Count, 1).End(xlUp).Row

LC = Sheets("Data2").Cells(1, Columns.Count).End(xlToLeft).Column

Sheets("MergedData").Range("A1:BJ" & 62).Value = Sheets("Data1").Range("A1:BJ" & 62).Value

Temp = "BL1:" & Cells(LR2, 63 + LC).Address

Sheets("MergedData").Range(Temp).FormulaR1C1 = _
        "=OFFSET(Data2!R1C[-62],MATCH(RC1,Data2!R1C1:R30C1,0)-2,0)"

Sheets("MergedData").Range(Temp).value = Sheets("MergedData").Range(Temp).value

End Sub
    Last edited by mehmetcik; 10-21-2015 at 07:10 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.
    Reply With QuoteRegister To Reply
  3. 10-22-2015, 04:19 AM #3
    Karl Gustaf Karsten
    Karl Gustaf Karsten is offline
    Registered User
    Join Date
    04-20-2015
    Location
    Norway
    MS-Off Ver
    360
    Posts
    24

    Re: Macro for copying rows in sheet1 to end of existing rows in sheet2 based on criteria

    Thank you, mehmetcik! This works very well

    I changed a few things to get it like I wanted it.
    Sub Merge_Data()

Dim MySheetName As String
    MySheetName = "Merged Data"
    Sheets("Compustat").Copy After:=Sheets("Compustat")
    ActiveSheet.Name = MySheetName
        
    LR1 = Sheets("Compustat").Cells(Rows.Count, 1).End(xlUp).Row
    LR2 = Sheets("CRSP").Cells(Rows.Count, 1).End(xlUp).Row
    LC = Sheets("CRSP").Cells(1, Columns.Count).End(xlToLeft).Column + 8

        Sheets("Merged Data").Range("A1:BJ" & 62).Value = Sheets("Compustat").Range("A1:BJ" & 62).Value
        Temp = "BL1:" & Cells(LR2, 63 + LC).Address
        Sheets("Merged Data").Range(Temp).FormulaR1C1 = _
                "=OFFSET(CRSP!R1C[-63],MATCH(RC1,CRSP!R1C1:R10000C1,0)-1,0)"

        Sheets("Merged Data").Range(Temp).Value = Sheets("Merged Data").Range(Temp).Value

        Worksheets("Merged Data").Cells(1, 63).Value2 = "CRSP Matches"

End Sub
    Thanks again, this is very helpful!
    Last edited by Karl Gustaf Karsten; 10-22-2015 at 10:09 AM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. automatically copying rows to an existing spreadsheet based on criteria
    By Jocatlett in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-30-2013, 07:21 PM
  2. Need to be able to input a name in Sheet2 and pull rows from Sheet1 based on that name
    By dmcleod in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-16-2012, 11:34 AM
  3. [SOLVED] macro to copy the formulas in Sheet2 to as many rows as rows available in Sheet1
    By flakedew in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 06-21-2012, 04:52 AM
  4. [SOLVED] Copying selective data from sheet2 to sheet1 based on the selection made in sheet1.
    By daavis in forum Excel General
    Replies: 3
    Last Post: 06-06-2012, 05:36 AM
  5. [SOLVED] Copying Data from Sheet1 to sheet2 based on criteria for copy
    By pprane in forum Excel General
    Replies: 1
    Last Post: 04-22-2012, 02:32 AM
  6. Copying Data from Sheet1 to sheet2 based on criteria for copy
    By pprane in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-22-2012, 02:07 AM
  7. Help! Hiding rows in sheet2 based on values in sheet1
    By Oti in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-23-2011, 12:47 PM
  8. Macro to copy values from Sheet1 to Sheet3, based on criteria in Sheet2
    By John74 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-13-2010, 04:55 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1