+ Reply to Thread
Results 1 to 2 of 2

VBA Copy paste to next row

  1. #1
    Registered User
    Join Date
    05-25-2017
    Location
    manchester, England
    MS-Off Ver
    365 Mac
    Posts
    8

    VBA Copy paste to next row

    Thank you for reading my post.

    I am new to VBA and I was wondering if you could advise me or guide me in the right direction.

    I have the below VBA code that works to an extent by matching column data between 2 worksheets and copies and pastes the corresponding data into the master tab.

    However the issue I am encountering its overwriting the data and the xlUP code doesn't work. I don't want to overwrite data but paste new data under data thats currently there.

    Can anyone help at all. I have tried various VBA codes and still no joy.


    Sub copyDataBlocks2()
    Dim intErrCount As Integer

    ' create worksheet objects
    Dim shtSource As Worksheet: Set shtSource = Sheets("ws2")
    Dim shtTarget As Worksheet: Set shtTarget = Sheets("ws1")

    ' create range objects
    Dim rngSourceHeaders As Range: Set rngSourceHeaders = shtSource.Range("A1:BB1")

    With shtTarget
    Dim rngTargetHeaders As Range: Set rngTargetHeaders = .Range("A1:AB1") '.Cells(1, 1), .Cells(1, .Columns.Count).End(xlToLeft)) 'Or just .Range("A1:AB1")
    Dim rngPastePoint As Range: Set rngPastePoint = .Cells(.Rows.Count, 1).End(xlUp).Offset(1 + 1) 'Shoots up from the bottom of the sheet untill it bumps into something and steps one down
    End With

    Dim rngDataColumn As Range

    ' process data
    Dim cl As Range, i As Integer
    For Each cl In rngTargetHeaders ' loop through each cell in target header row

    ' identify source location
    i = 0 ' reset I
    On Error Resume Next ' ignore errors, these are where the value can't be found and will be tested later
    i = Application.Match(cl.Value, rngSourceHeaders, 0) 'Finds the matching column name
    On Error GoTo 0 ' switch error handling back off

    ' report if source location not found
    If i = 0 Then
    intErrCount = intErrCount + 1
    Debug.Print "unable to locate item [" & cl.Value & "] at " & cl.Address ' this reports to Immediate Window (Ctrl + G to view)
    GoTo nextCL
    End If

    ' create source data range object
    With rngSourceHeaders.Cells(1, i)
    Set rngDataColumn = Range(.Cells(2, 1), .Cells(1000000, 1).End(xlUp))
    End With

    ' pass to target range object
    cl.Offset(1, 0).Resize(rngDataColumn.Rows.Count, rngDataColumn.Columns.Count).Value = rngDataColumn.Value

    nextCL:
    Next cl

    ' confirm process completion and issue any warnings
    If intErrCount = 0 Then
    MsgBox "process completed", vbInformation
    Else
    MsgBox "WARNING: " & intErrCount & " issues encountered. Check VBA log for details", vbExclamation
    End If
    End Sub
    Last edited by VBA learner ITG; 06-27-2017 at 05:46 AM.

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,137

    Re: VBA Copy paste to next row

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    My Rules if you want my help:

    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 to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 01-01-2015, 06:26 AM
  2. How do I make the copy/paste of one column contingent on the copy/paste of another?
    By Kwasimitsu in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-23-2014, 01:51 PM
  3. [SOLVED] Copy Paste Loop ends before copy/paste is finished
    By brgr4u in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-08-2013, 04:01 PM
  4. Automate copy and paste and copy and paste back to excel
    By Bmw318be in forum Access Tables & Databases
    Replies: 0
    Last Post: 02-23-2013, 05:42 AM
  5. Replies: 1
    Last Post: 01-16-2013, 05:36 AM
  6. 2 Macro's: only vertical copy/paste action and copy-paste 14 columns to the right.
    By vdongen in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-08-2010, 10:34 AM
  7. Replies: 1
    Last Post: 01-04-2005, 06:06 PM

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