-1

I found some info on the forum but I haven't been able to understand it. I need to copy specific rows (range) in sequence from one excel sheet to a new sheet for data plotting. Can someone please help me with this visual basic code?

My data looks like this: https://i.stack.imgur.com/VACgN.jpg

What I need to do is combine A1-1:E1-1 and A1-2:E1-2 data side by side, as in I want D5:D9 side by side with D19:D23. Similarly, E5:E9 side by side with E19:E23. These combinations can be done by creating a new sheet I hope.

Then I think I need to run a loop to combine A2-1 and A2-2 data. A2 data starts at row 32. This spacing is maintained throughout the spreadsheet. so I think one should be able to put in a loop to go through the entire sheet. There are spaces in between data (rows), so I am not sure as to how you would tell the program to run through the entire sheet.

sub copydata()

LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).row

For i = 8 To LastRow

Range(Cells(i, 1), Cells(i, 4)).Select
Selection.Copy

erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).row

ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False
End If

Next i

End Sub
Anjil
  • 11
  • 3
  • 1
    Have you attempted this yourself yet? Post the links you looked at on the forum already, and state what areas you are confused by. – Grade 'Eh' Bacon Jul 21 '15 at 18:48
  • https://stackoverflow.com/questions/9929366/copy-and-paste-the-data-one-excel-to-another-excel-based-on-column-name-include . This is another link: https://stackoverflow.com/questions/23740364/excel-vba-copying-cell-contents. I can't figure out how to loop through the entire sheet and put the data side by side. – Anjil Jul 21 '15 at 18:55
  • There are multiple answers in that question. What parts of it don't you understand? Have you tried to solve this at all yourself? Show us your code, and tell us where it errors / gives a value you weren't expecting. – Grade 'Eh' Bacon Jul 21 '15 at 18:56
  • i have updated the question with what i got get from reading the codes posted in forums. I have never used VBA, so trying to understand. – Anjil Jul 21 '15 at 19:06

1 Answers1

1

You might be able to do this without a macro. So if you take the structure of the sample sheet you showed, I would create new sheets and use the index formula to bring the values you need and the order you want them by. So for column D, I would have a new sheet named appropriately. For the Column A, I would list a partial sample name (A1-, B1-, etc.). For Row 1, I would have the rest (1-5). Then in the cell B2, enter this formula

=INDEX('Sheet1'!$D:$D,MATCH($A2 & B$1,'Sheet1'!$B:$B,0))

Fill down and right. Should get you what you want. On the other sheets, just change the initial range of the INDEX formula to the respective column.

I hope this helps.

legendjr
  • 276
  • 1
  • 12