Results 1 to 6 of 6

VBA Copy/Paste data from one workbook to a table in another workbook

Threaded View

  1. #1
    Registered User
    Join Date
    11-19-2012
    Location
    Manassas Park
    MS-Off Ver
    Excel 2007
    Posts
    6

    VBA Copy/Paste data from one workbook to a table in another workbook

    I have a module that I've developed with the help of various communities whereby I want to take information from one worksheet in one workbook (or rather several workbooks) and throw all that information to another workbook (a master). I get this to work just fine if it's *not* in a table, however, I want to put it on the table. The issue I'm having stems, I believe, from this line within my code: (j = .Cells(.Rows.Count, "A").End(xlUp).Row + 1).

    Here's a bulk of the code to put it into perspective, with comments on what it's doing:

    ...
    
    ' This code will find the last row of information that has data in it
    ' When a table is present, however, even if there are blank rows, 
    ' this script treats those as 'data' fields and will go to the line UNDER the table
    
    With Workbooks("MasterWB.xlsm").Worksheets("Sheet1")
        j = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    End With
    
    ' This code will copy the data from the slave worksheet and paste to master
    
    For i = 1 To LastRowSlave
        With Workbooks("Slave1.xlsm").Worksheets(strName)                                              'strName is just the ActiveSheet.Name
            If .Cells(i, 1).Value = "x" Then                                                                         'if there is an x in column A, it's marking it as data that needs to be moved to the master
                .Rows(i).Copy Destination:=Workbooks("MasterWB.xlsm").Worksheets("Sheet1).Range("A" & j)
                .Cells(i, 1).Value = "Added on " & Date                                                          'This replaces the 'x' with a date the info was added so as to prevent manual marking of these fields
                j = j + 1
            End If
        End With
    Next i
    
    ...
    So the biggest question, if anyone can help: How can I paste that information into a TABLE on the MasterWB.xlsm spreadsheet?

    Additionally (and probably off topic, but just to pose the question), is there an easy way to identify rows that may have been edited? My worry is that they edit one block on the slave worksheet and even if they manually place an 'x' in that Column A, it will only add a brand new row to my Master spreadsheet, thus double-counting that row. Is there a simpler solution?

    Any help is appreciated. Thank you!
    Last edited by Ramtrap; 03-26-2018 at 11:14 AM. Reason: clarity of comments

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Copy and Paste Special a data range from one workbook to another workbook
    By Kimston in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-11-2018, 12:21 AM
  2. Replies: 3
    Last Post: 02-16-2018, 06:40 PM
  3. [SOLVED] Macro to copy & paste data from workbook 1 to workbook 2
    By ricdamiani in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-04-2018, 02:27 AM
  4. Replies: 12
    Last Post: 10-28-2015, 11:48 PM
  5. [SOLVED] Macro to find data in source workbook and copy paste to target workbook
    By D.Lovell in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 04-23-2014, 06:21 AM
  6. [SOLVED] Code to copy data from a closed workbook and paste in active workbook using named range.
    By paullie1912 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-28-2014, 02:38 AM
  7. Replies: 6
    Last Post: 01-29-2013, 07:01 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1