Replaces row instead of next avail

[Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

here is the macro i am working with - works great with one problem it replaces the information in the first row instead of pasting in the next available

Option Explicit

Public Sub MoveToTab()
Dim rngStart As Range
Dim rngEnd As Range
Dim rngCell As Range

On Error GoTo ErrHnd

With Worksheets("Source")
'set start as A7 i.e., after heading row in column A
Set rngStart = .Range("A7")
'set end - last used row in column T
Set rngEnd = .Range("A" & CStr(Application.Rows.Count)).End(xlUp)

'loop through cells in column A
For Each rngCell In Range(rngStart, rngEnd)
'test if tab exists
On Error Resume Next
If Not Worksheets(rngCell.Text).Name <> "" Then
On Error GoTo ErrHnd
'No worksheet of this name - so create one and copy row
Worksheets.Add After:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = rngCell.Text
rngCell.EntireRow.Copy Destination:=Worksheets(rngCell.Text).Range("A1")
Else
On Error GoTo ErrHnd
'worksheet exists
'copy row to end of used range
rngCell.EntireRow.Copy Destination:=Worksheets(rngCell.Text).Range("A1") _
.Offset(Worksheets(rngCell.Text).UsedRange.Rows.Count, 0)
End If
Next rngCell
End With
Exit Sub

'error handler
ErrHnd:
Err.Clear
End Sub

Please help!


1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
801
the macro works but does not give the answer you want.

when writing codes I suggest you have number of msgbox(s) so that you can easily debug and finally you can always remove these msbox(s)

suppose A7 is "a"
then your macro finds there is not "a" sheet and it creates a new sheet and calls it "a' and then copie form surce file the row 7 to row 1 of sheet1
you if condition is

If Not Worksheets(rngCell.Text).Name <> "" Then


that is if worksheets("A") exists
you have already created a sheet and called it "a" that means that answer to if condition is "yes" .
so the row 7 is copied to A1 row

where is the chance of "else" coming in
perhaps I am confused.


first time the pasting is done on the first row of new sheets
second time you run the macro now sheet("a") exist and "else" condition will come into effect and 7th row is pasted to second row.
everytime you run macro this will happen? Is this what you want

there is some confusion here. will you please explain the logic step by step so that the macro can be modified.