0

I have an excel sheet that contains information along a number of variable columns. the information needs to go to a second sheet. The information is also variable and needs to go into the appropriate column of the other worksheet. Once its gone through that column, it needs to go to the next row and start again. Number of rows also variable.. ie. column AN may contain: total meterage of glass, ask specification questions, or type of project. If the column contains total meterage of glass it needs to go to column 21 of the second sheet, if its ask specification questions to column 27 etc. The column loop will need to stop 2 columns before the end of the data

I've got an if statement so that it offsets to the appropriate column (I get it to go the beginning of the row each time). I have got it counting the rows so it stops, but I am struggling to work out how to get it to go to the next row and start in the right place. The information I'm copying doesn't start until column 20

Dim lastRow As Long

lastRow = Sheets("Leadbank").Range("a" & Rows.Count).End(xlUp).Row

Do Until ActiveCell.Row = lastRow + 1



Sheets("Leadbank").Select

If ActiveCell.Value Like "" Then
 ActiveCell.Offset(0, 2).Select

 Sheets("Results").Select
ActiveCell.Offset(1, 0).Select

If ActiveCell.Value = "" Then
ActiveCell.Offset(1, 0).Select


End If
 End If

If ActiveCell.Value Like "Total Meterage of Glass" Then
Selection.Copy
Sheets("Results").Select
ActiveCell.Offset(0, 21).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("a" & ActiveCell.Row).Select

End If

If ActiveCell.Value Like "Stage of Project" Then
Selection.Copy
Sheets("Results").Select
ActiveCell.Offset(0, 22).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
         Range("a" & ActiveCell.Row).Select
End If

Sheets("Leadbank").Select
'Selection.End(xlUp).Select
If ActiveCell.End(xlUp).Value Like "" Then
    Range("a" & ActiveCell.Row).Select
ActiveCell.Offset(1, 50).Select

End If

Sheets("Leadbank").Select
 ActiveCell.Offset(0, 2).Select



Loop

End Sub

I need the information to be transposed to the second worksheet against the right customer information. I think I should be able to use some form of lookup to do this. I just can't get it to loop and put the information in the right column

Jacky
  • 19
  • 5
  • 1
    You can do this through `.Find` to get the column, and then paste the value with references to ranges. Either way there isn't a real need for using `.Select` and you should try avoid that. Plenty of examples around on SO. Also to get the last used row + 1. [Here](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) is a good starting point. – JvdV Apr 08 '19 at 07:53
  • Thanks. Not actually used .Find before, so off to have a look around.. did not know that about .Select. good tip – Jacky Apr 08 '19 at 08:44

0 Answers0