VBA Copy paste to next row

[VBA learner ITG]

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

[mehmetcik]

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)