Macro to transpose data from one sheet to another

hi

I need a routine to make sheet1 become sheet 2 as per my example

i have produced a sample of the work as i can not show the actual data

anything with xxxx is not needed on the new worksheet

Try:

Please Login or Register  to view this content.

this is my version of the code above to stop subscript errors and it works a little bit how ever some line of data are missing i have attched a screen shot of what it looks like after the code is amended.Ran code.doc

Sub UpdateFD()


Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Sheets("Financial Difficulties").Cells.Find(" ", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Dim x As Long
Dim y As Long
y = Cells(Rows.count, "A").End(xlUp).Row
For x = 2 To LastRow
Sheets("Financial Difficulties").Cells(x, "R").Copy Sheets("FD").Cells(Rows.count, "B").End(xlUp).Offset(1, 0)
Sheets("FD").Cells(Rows.count, "A").End(xlUp).Offset(1, 0) = y
Sheets("Financial Difficulties").Cells(x, "V").Copy Sheets("FD").Cells(Rows.count, "C").End(xlUp).Offset(1, 0)
Sheets("Financial Difficulties").Cells(x, "A").Copy Sheets("FD").Cells(Rows.count, "D").End(xlUp).Offset(1, 0)
Sheets("Financial Difficulties").Cells(x, "W").Copy Sheets("FD").Cells(Rows.count, "E").End(xlUp).Offset(1, 0)
Sheets("Financial Difficulties").Cells(x, "L").Copy Sheets("FD").Cells(Rows.count, "L").End(xlUp).Offset(1, 0)
Sheets("Financial Difficulties").Cells(x, "Z").Copy Sheets("FD").Cells(Rows.count, "M").End(xlUp).Offset(1, 0)
Sheets("Financial Difficulties").Cells(x, "AA").Copy Sheets("FD").Cells(Rows.count, "N").End(xlUp).Offset(1, 0)
Sheets("Financial Difficulties").Cells(x, "AB").Copy Sheets("FD").Cells(Rows.count, "O").End(xlUp).Offset(1, 0)
y = y + 1
Next x
Application.ScreenUpdating = True

I also need it to recognise that if Row AB on the Financial Difficulties sheet = Farrah Taheri or Jill slater to ignor that row

Try the attached file. It looks like it's working properly.

Do you mean column AB? If so, it contains city names. What column would the names be in?

I have not looked the thread beyond post #1. The attached code is a solution as per the attachment.

sorry meant AA

Try:

Please Login or Register  to view this content.

When i have done this i get the same results as my Ran Code.doc

If i leave the extra out of it everything works fine

I tried changeing the And to Or as it can be either manager but it is not filling out the other details only what is the my sample

Try the attached file. I have put Farrah Taheri and Jill Slater in column AA. When I run the macro it copies only rows 2 and 4 and not the rows that contain those names in column AA. I believe that is what you were asking. If your actual worksheet is organised differently, please post your actual file.

I am running the code and getting Application-defined or object-defined error

On the line

Sheets("Financial Difficulties").Cells(x, "R").Copy Sheets("FD").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)

I'm using the following macro and it works for me.

Please Login or Register  to view this content.