holdagent = Sheet1.btnag6.Caption

[newuser08]

Hi all,

I have a workbook with multiple sheets in it, what I am trying to accomplish is when I push a cmdbtn with a given users name as its caption the code will scan through all the worksheets select the range where = to the sheet1.cmdbtn.caption then paste a copy into a ‘results’ sheet.

The code itself was written to scan through one sheet than I was going to reproduce it to scan through the remaining sheets not ideal I know, however I’m still getting to grips with excel vba.

The code:

Application.Calculation = xlCalculationManual
frmworking.Show (0)
DoEvents
scandown = 1
foundagent = 0
Do
scandown = scandown + 1
holdagent = Sheet1.btnag6.Caption
HoldSelectedagent = Sheet1.btnag6.Caption
If holdagent = HoldSelectedagent Then
foundagent = 1
leftcell = "A" & scandown
rightcell = "H" & scandown
checkblank = 1
Do
checkblank = checkblank + 1
Loop Until Sheet12.Cells(checkblank, 1) = ""
pastetocells = "A" & checkblank
Sheet12.Range(pastetocells & ":H" & checkblank).Value = Sheet4.Range(leftcell, rightcell).Value
End If
Loop Until foundagent = 1 And holdagent <> HoldSelectedagent
frmworking.Hide
Application.Calculation = xlCalculationAutomatic

Oh I should also mention that the above falls over at holdagent=””

Any suggestions appreciated Guys.

Thanks in advance.

 

[SkipVought]

Hi,

Here's a start...

dim ws as worksheet, rng as range
for each ws in worksheets
  with ws
    select case .name
      case "results", "Sheet1"
      case else
      'do your find here
          set rng = .cells.find(sheet1.cmdbtn.caption)
          if not rng is nothing then
          'now we have found something! So DO something!

          end if
    end select
  end with
next

I do NOT see where you are doing ANY copy or paste. Nether can I find a sheet named "results".

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[newuser08]

Thanks Skip,

I had a play around and added some bits:

Private Sub btnag6_Click()
scany = 0

Dim ws As Worksheet, rng As Range
For Each ws In Worksheets
With ws
Select Case .Name
Case "Check Agent Results", "Sheet12"
Case Else
Set rng = .Cells.Find(Sheet1.btnag6.Caption)
If Not rng Is Nothing Then
rng.Copy
Do
scany = scany + 1
Loop Until Sheet12.Cells(scany, 1) = ""
Sheet12.Cells(scany, 1) = rng

End If
End Select
End With
Next
End Sub


When it pastes the data it appears that one cell section is pasted not a range (“A:H”) and the actual search only seams to scan one sheet – ‘Total Errors’ sheet1 as when the paste is complete its cell C13 form sheet1 only that is pasted into sheet12.

 

[SkipVought]

Try this...

          lRow = Sheet12.[A1].end(xldown).row + 1
          rng.Copy Destination:=Sheet12.Cells(lRow, 1)

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}