0

I have been trying to get this to work, but I am not sure how to get it to work, any help would be appreciated.

I have 2 workbooks, workbook 1 has multiple sheets, each one labelled with a different name. Workbook 2 has one summary sheet with a column of values for each individual. What I am trying to achieve is:

  1. on workbook 1 check the sheet name

  2. switch to workbook 2 and find the column with the same name. All the names are on row 6, from column I to DD. Also, each name is in 2 cells merged together, I don't know if this affects it.

  3. Once the name on row 6 is found, I want it to go down 6 cells, and copy the value.

  4. switch back to workbook 1 and paste it into cell B37.

  5. Repeat this process but this time go down 7 cells, copy the value and paste it into cell B102 OF Workbook 1. I have about 30 cells to copy and past like that.

  6. once complete repeat everything again for the next sheet on workbook 1.

Another Important issue is that, not all sheet names on workbook 1 exists on workbook 2, I have a feeling the below code will throw an error as soon as it doesn't find a match. So I would like to be able to skip the sheets on workbook 1 that it doesn't find a matching name for on workbook 2 summary sheet.

I have put the code I have below, but I keep getting the error "Method or data member not found"

Sub Measures()

    Dim wb1 As Workbook
    Dim Sht As Worksheet
    Dim Rng, Rng2 As Range
    Dim wb2 As Workbook
    Dim cell As Range
    Dim ws As Worksheet

    Set wb1 = ThisWorkbook
    Set wb2 = Workbooks("November Stream 1.xlsm")
    Set Sht = wb1.Worksheets("Summary")
    Set Rng = Sht.Range("A6:A" & Sht.Cells(Sht.Column.Count, "A").End(xlUp).Column)

    For Each cell In Rng
        Set ws = wb2.Sheets(cell.Text)
        ws.Range("B37").Value = cell.Offset(6, 0).Value
        ws.Range("B102").Value = cell.Offset(7, 0).Value
    Next cell
End Sub

Thank you for any help!

kira123
  • 75
  • 10
  • Unfortunately there could be several issues with this code. `Dim Rng,` will declare Rng as Variant. I'd suggest `Dim Rng As Range, Rng2 As Range` to be explicit. `Set wb2 = Workbooks("November Stream 1.xlsm")` is probably where the error is stemming from. First of all, where is the file? You didn't give the full path. Secondly, you have to make sure it's open. Try instead to use the method `Set wb2 = Workbooks.Open(filepath)`. – MoondogsMaDawg Nov 27 '17 at 15:26
  • Thanks for your response. I have both workbooks open when I run the code. I have used a very similar code previously without defining the full path and it worked as long as I had both workbooks open. the error message "Method or data member not found" highlights sht.column.count. I think the issue is that this code doesn't go through each cell on row 6. – kira123 Nov 27 '17 at 15:40
  • The previous working code had the following ' Set Rng = Sht.Range("A5:A" & Sht.Cells(Sht.Rows.Count, "A").End(xlUp).Row) ' This code checked cell names in column A, from cell A5 and below. Whereas the new code, I want it to go check through row 6, column I onwards. I hope that make sense – kira123 Nov 27 '17 at 15:51
  • 1
    Ok if the workbook is open that should work, I just tend to avoid requiring the user to maintain the environment. Your error is caused by using `Column.Count` instead of the plural: `Columns.Count`. My go to solution is [this one](https://stackoverflow.com/a/11927387/5936117), but you might also find [this](https://www.excelcampus.com/vba/find-last-row-column-cell/) useful. – MoondogsMaDawg Nov 27 '17 at 16:50
  • Thank you, I will give that a go. – kira123 Nov 28 '17 at 12:40

0 Answers0