0

I have a spreadsheet I've created and I would like to copy info from a master sheet "VARS Info" into a sheet. I've created separate tabs using a string of names. I keep stumbling when it's trying to find the value A. A is showing the value I would like to copy but it keeps saying type mismatch. Column "A" has the names and column "B" has the info I would like copying but I would only like to copy the info that relates to column "A". For example I would like to copy both entries in column B for "Joe Bloggs" into the sheet name "Joe Bloggs" which has already been created.

Sub ImportInfo()

Dim I As Long
Dim A As Range
Dim Name As Object
Dim NumRows As Integer

Application.ScreenUpdating = False

Set A = Range("a2:a92")

Sheets("VARS Info").Select

NumRows = Range("A2", Range("A2").End(xlDown)).Rows.Count

For I = 1 To NumRows

For Each Name In Range("A:A")

If InStr(1, Name.Value, A) > 0 Then

A.Offset(0, 1).Select

A = Cells(I, 1).Copy

Selection.Copy Destination:=Sheets(A).Range("B19:C19")

Sheets(A).Select

Selection.Insert Shift:=xlDown

End If

Cells.Select
Cells.EntireColumn.AutoFit450

Next
Next

End Sub


A       B

Joe Bloggs £100 Dave Smith £100 Joe Bloggs £100 Dave Smith £100

I hope this make sense.

braX
  • 11,506
  • 5
  • 20
  • 33
Tom Eggins
  • 1
  • 1
    Maybe reading [How to avoid using select](https://stackoverflow.com/a/23913882/16578424) and [How to avoid copy/paste](https://stackoverflow.com/a/64611707/16578424) will help you to fix it. – Ike Jan 19 '23 at 11:51
  • 1
    Welcome to SO. Can you post a data example and expected output? – Foxfire And Burns And Burns Jan 19 '23 at 11:55
  • Hi there. I would like to copy all info in relation to Joe Bloggs from one sheet and paste it in the sheet name Joe Bloggs – Tom Eggins Jan 19 '23 at 13:09

0 Answers0