Help copying values

[ColdGeorge]

Hello all

What I’m trying to do is copy names from list in one sheet, on to another sheet where the word LECTOR is found, the thing is when I run code, last name of list is shown in all cells, I need first name of list in first cell, second name of list in second cell, and so on, any help would be appreciated.

ColdGeorge

Dim c As Range
Dim d As Range


For Each c In Sheets("Hoja1").Range("A2:A6")
    If c.Value <> "" Then
        c.Copy
        Sheets("Hoja2").Select
    End If
Next
        
    For Each d In Sheets("Hoja2").Range("A5:A13")
        If d.Value = "LECTOR" Then
            d.Offset(0, 1).PasteSpecial xlPasteValues
        End If
Next
    
Application.CutCopyMode = False

 

[iliace]

It looks like you need to next the second For loop in the first one. However, the end result will still be the last found non-blank. You might want to use Find instead. But, even then, if your number of names is more than LECTOR rows on the Hoja2, you will still get them overwritten.

  Dim c As Range
  Dim d As Range
  
  Dim sFirst As String
  
  
  For Each c In Worksheets("Hoja1").Range("A2:A6")
    If c.Value <> "" Then
      If Len(sFirst) = 0 Then
        Set d = Worksheets("Hoja2").Range("A5:A13").Find("LECTOR")
        sFirst = d.Address
      Else
        Set d = Worksheets("Hoja2").Range("A5:A13").FindNext(after:=d)
      End If
      
      d.Offset(0, 1).Value = c.Value
    End If
  Next c

 

[ColdGeorge]

Hello iliace

Thanks for your help, it almost worked the way I want it, values from sheet1 are copied to the sheet2 starting in second cell and so on, last value is copied to first cell, what should I do to fix it?

ColdGeorge

 

[iliace]

Hello iliace

Thanks for your help, it almost worked the way I want it, values from sheet1 are copied to the sheet2 starting in second cell and so on, last value is copied to first cell, what should I do to fix it?

ColdGeorge

This is where I'm stuck, I'm not entirely sure what you are trying to do. Can you post a before/after example?

 

[ColdGeorge]

Hi iliace

This is a simple lectores schedule, there's a list of lectores and a date when they perform, list is in sheet1, dates are in sheet2, I just want to assign dates for each person in a orderly manner, first date first guy, second date second guy and so forth, what is the best way to perform this task?

ColdGeorge

 

[iliace]

Can you post a before/after example? Your instance makes no sense. Please clarify.

 

[ColdGeorge]

Hi, I am not sure I dit it right,

this is data in sheet1, column A1:A8, no spaces.
[TABLE="width: 80"]
<tbody>[TR]
[TD="width: 80, bgcolor: transparent"]LECTOR[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]ANA[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]BERTHA[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CARMEN[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]DEBORA[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]MARTHA[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SUSANA[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TANIA[/TD]
[/TR]
</tbody>[/TABLE]


This is sheet2, please note LECTOR is every other row, A1:A13, expected result is to have first name from list on top/first cell right next to LECTOR
[TABLE="width: 160"]
<tbody>[TR]
[TD="width: 80, bgcolor: transparent"]LECTOR[/TD]
[TD="width: 80, bgcolor: transparent"]TANIA[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]LECTOR[/TD]
[TD="bgcolor: transparent"]ANA[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]LECTOR[/TD]
[TD="bgcolor: transparent"]BERTHA[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]LECTOR[/TD]
[TD="bgcolor: transparent"]CARMEN[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]LECTOR[/TD]
[TD="bgcolor: transparent"]DEBORA[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]LECTOR[/TD]
[TD="bgcolor: transparent"]MARTHA[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]LECTOR[/TD]
[TD="bgcolor: transparent"]SUSANA[/TD]
[/TR]
</tbody>[/TABLE]


Thanks for your patience, ColdGeorge

 

[Greg Truby]

A solution has been provided here.