+ Reply to Thread
Results 1 to 3 of 3

Paste Data under header with same name

Hybrid View

  1. #1
    Registered User
    Join Date
    11-26-2014
    Location
    London
    MS-Off Ver
    2013
    Posts
    95

    Unhappy Paste Data under header with same name

    Hi The below code is working for me, however on the first Loop in Column A when I paste it overwrites the column heading, but every column after that it pastes the data below the heading. Why is the first column different?

    Is the Paste section of my code incorrect? Can it be done better? Its the code under 'Paste header that is causing the trouble?


    Dim LookupTab As Worksheet
    Set LookupTab = ThisWorkbook.Sheets("Lookups")
    LookupTab.Select
    LookupTab.Range("A2", Range("A" & Rows.Count).End(xlUp)).Name = "Dynamic"
    UserForm1.lstHeaders.RowSource = "Dynamic"
    
    Dim LocationTab As Worksheet
    Set LocationTab = ThisWorkbook.Sheets("Location")
    Dim RawDataTab As Worksheet
    Set RawDataTab = ThisWorkbook.Sheets("Raw_Data")
    Dim x As Integer
    
                With UserForm1.lstHeaders
                    For x = 0 To .ListCount - 1
                        Header = UserForm1.lstHeaders.List(x, 0)
                                    On Error Resume Next
    
                                            'Copy Header
                                            LookupCopyHeader = Application.WorksheetFunction.Match(Header, RawDataTab.Rows(1), 0)
                                            RawDataTab.Select
                                            
                                            With RawDataTab
                                                .Range(.Cells(3, LookupCopyHeader), .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row, LookupCopyHeader)).Copy
                                            End With
                                                
                                                
                                            'Paste Header
                                            LookupPasteHeader = Application.WorksheetFunction.Match(Header, LocationTab.Rows(1), 0)
                                            
                                            LocationTab.Select
                                            With LocationTab
                                                .Range(.Cells(2, LookupCopyHeader), .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row, LookupCopyHeader)).PasteSpecial
                                            End With
    
                    Next x
                End With

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,667

    Re: Paste Data under header with same name

    No attached file, so not tested.
    Try pasting to first (and following) cell(s), not to strictly (byt may be not perfectly well) defined range
    .Cells(2, LookupCopyHeader).PasteSpecial
    instead of:
    .Range(.Cells(2, LookupCopyHeader), .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row, LookupCopyHeader)).PasteSpecial
    you may also (on testing and dev. stage) add checking what opasting address you really select by msgbox, say:
    msgbox .Range(.Cells(2, LookupCopyHeader), .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row, LookupCopyHeader)).address
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    11-26-2014
    Location
    London
    MS-Off Ver
    2013
    Posts
    95

    Re: Paste Data under header with same name

    Perfect. Cheers Kaper.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro to copy & paste data into new sheet depending on column header (date)?
    By BobbyH89 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-20-2015, 03:44 PM
  2. Copy data from header to header in consolidation sheet
    By Ignesh in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-12-2013, 04:10 AM
  3. Replies: 5
    Last Post: 08-25-2013, 02:59 PM
  4. Replies: 2
    Last Post: 10-13-2012, 03:30 AM
  5. Cut Header from a set of data and paste the same next to the Data in C& D Col
    By aravindhan_31 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-20-2008, 04:31 AM
  6. Macro for Appending Data without Header to a database with header
    By tiang1209 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-30-2007, 04:38 AM
  7. Excel: custom header - is it possible to paste into header?
    By Maureen D. in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-04-2005, 11:10 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