Forum Discussion

GMSchaaf's avatar
GMSchaaf
Copper Contributor
Oct 31, 2019

Help with creating a loop to backfill data from one sheet to another

Hi,

 

I have values in this "Sample Analysis Data" sheet in cells B2:B10. The code then looks for that value in the sheet "Meta Data". It then copies the cells in that row and pastes it in "Sample Analysis Data" (to the right of the searched value). This all works fine for the value in B2. However, I can't get it to move on to B3...and then B4 and such. It just loops though and does the same thing again for B2. 

 

* What do I need to do to get it to loop to from B2 to B10?

* Along with this, how do I get it to go from B2 to the last entry in the column (as each data set I work with could have any different number of rows of data, not just to B10?

 

A sample file is attached.

 

Thanks!!

Greg

 

 

Sub GetMetaData()


Worksheets("Sample Analysis Data").Activate
Range("B2").Select


Dim srch As Range, Cell As Variant
Set srch = Range("B2:B10")


For Each Cell In srch
Sheets("Meta Data").Activate

    Cells.Find(What:=Cell, LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate

ActiveSheet.Cells(ActiveCell.Row, 1).Select
Range(ActiveCell, ActiveCell.End(xlToRight).End(xlToRight)).Select

Application.CutCopyMode = False
Selection.Copy

Sheets("Sample Analysis Data").Activate

ActiveCell.Offset(0, 7).Select
ActiveSheet.Paste

Next Cell
End Sub

 

 

 

 

excel
Macros and VBA

1 Reply

Sort By
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Nov 01, 2019

    GMSchaaf 

    Saw your message yesterday. Took your code and changed it a bit. When I was ready to post it, your message had disappeared. Now I see you added a workbook and changed some of the code. 

     

    The solution I created yesterday will work as long as your first sample cell is B2, and I believe the code in the attached workbook will do the trick for you, but you'll need to amend it to make it work in yours. Perhaps you need to change the structure of your meta data so that the Sample Name is in the first column. You want to delete column A as well.

     

     

     

Resources