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
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.
