A really annoying Loop, Help please!

[MarkNumskull]

Hi!

Iv been working on VBA code to go through peoples staff number and transfer data form one spreadsheet to another while converting forumlas to values. This works fine at the minute but i would love to loop the below bit of code as currently for each staff member i have the code pasted in 30 times which means my SUB is rediculously big. Iv tried to add a loop to the code but as iv never used loops before i dont know what im doing and it doesnt work, any help would be much appreciated as this has been annoying me for ages!

[vba]Sheets("Sheet1").Select
If ActiveCell = 10057914 Then - 'this is the condition, if the active cell <> 10057914 then it should go onto the
'next staff number, but while it does have this value it should loop through the row copying and pasting them into sheet2
ActiveCell.EntireRow.Copy
ActiveCell.Offset(1, 0).Select
Sheets("Sheet2").Select
ActiveCell.Select
Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(1, 0).Select
End If
[/vba] Many Thanks!

Mark

[Bob Phillips]

Put all the staff numbers in a list on sheet1 and name that list StaffNums (Insert>Name>Define...), and use

[vba]

With wokSheets("Sheet1").Range("A2") '<<<< change to actual cell

If Not IsError(Application.Match(.Value, .Range("StaffNums"), 0)) Then

.EntireRow.Copy
Worksheets("Sheet2").Range("A1").End(xlDown).PasteSpecial Paste:=xlPasteValues
End If
End With
[/vba]

[MarkNumskull]

Hi xld,

Thanks for quick reply, i cant quite get it to work though, i wouldn't be able to make a staff list as the staff numbers arn't coming from the same source, for each line of code to work it would just have to have the staff number itself, i have copied in the whole code below, what i gave above was just the second half, the first part of the code openes the other sheet, copies the data into a1 as values and then closes the sheet, so i already have the cell activated when the next bit of code comes as below

[VBA]
On Error Resume Next
Workbooks.Open("C:\Documents and Settings\mark.smith\Desktop\Tally Sheets\Tallysheetstocomplile\Chris.xls") _
.Sheets("data").Select
ActiveSheet.Range("A2:L40").Copy
Windows("intermediateSheet.xls").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("a1").Select
Workbooks.Open("C:\Documents and Settings\mark.smith\Desktop\Tally Sheets\Tallysheetstocomplile\Chris.xls").Close
Windows("intermediateSheet.xls").Activate
Sheets("Sheet1").Select
If Range("C1") = 10057914 Then
Range("c1").EntireRow.Copy
ActiveCell.Offset(1, 2).Select
Sheets("Sheet2").Select
ActiveCell.Select
Selection.PasteSpecial Paste:=xlPasteValues
ActiveSheet.Range("A1").Offset(1, 0).Select
End If



Sheets("Sheet1").Select
If ActiveCell = 10057914 Then
ActiveCell.EntireRow.Copy
ActiveCell.Offset(1, 0).Select
Sheets("Sheet2").Select
ActiveCell.Select
Selection.PasteSpecial Paste:=xlPasteValues
ActiveCell.Offset(1, 0).Select
End If
[/VBA]
Does this make a bit more sense?

Then what happens after its all pasted is deletes sheets 1 and opens the next staff members spreasheet and does it all again.

Just need to get second part to loop until the active cell in sheet1 no longer has the staff number.

Thanks,

Mark

[rbrhodes]

Hi Mark,

Here's how I would do it. It looks like yoi might want to loop on the files as well? What would be the criteria for the files or is it all files in that directory? Fairly easy to do...

[MarkNumskull]

Hi Rdrhodes

Thanks for looking at this for me, i tried your attachment but i can't get it to work how i would like. The first half of the code that i pasted above opens the staff members tallysheet copies all the ranges and pastes them into sheet 1 on intermediate sheet, i would need this code to be there for each staff member as i need to have the on error code in as there are days when files will be missing within the directory (staff member on hols etc). It is just the second half of code that i need to loop through, the data pasted into sheet 1of intermediate sheet contains the staff number and not all the values pasted in the first instance are required which is why i need it to loop through and paste only the values that have a staff number next to them. The way i have it at the mo works fine but there is alot of code as i just pasted the 2nd half of code in 30 times! Is there a way to use a run until type loop on this?

[GTO]

Greetings Mark,

I am guilty of not finely reading the progress thus far, but might I suggest you attach an example workbook. Obfuscate any personal/company/sensitive info of course, but it should accurately portray what you are trying to do.

I believe this would help - and of course with a "Cool" name and from Ireland to beat (my mom's side), a great solution is obviously deserved!

BTW - I see that you just joined. As one who has benefitted so much from the great guys and gals here, welcome and a friendly Howdy from Arizona :-)

Mark

[MarkNumskull]

Ah a fellow Mark, a good powerful name that, Whereabouts in this land are they from? Im currently in belfast where its actually sunny for change!

I attach my sheet that the procedure runs from, and a tallysheet where it gets its data from (no personal info perse, just a name and staff number, dont think theyr much use to any one!)

As you will see when you run (you will need to change file names) it openes chris' tally sheet selects all the values in the sheet called "Data" and pastes in intermediatesheet sheet1 as values. Then what it does is selects the cell c1 (this contains the staff number) selects entire row, copies, then offsets the cell down 1 to make the next cell containing the staff number active and then pastes into sheet2. After its pasted it into sheet 2 it then offsets to a2 making this the active cell (for the nest row that gets pasted).

As you will see form the vba in intermediate sheet i have just copied the second row of code 30 times, this is because each tallysheet will have different amounts of data.

What i want to do is get rid of all the extra code and replace it with a code that loops the procedure to select the staff number and paste in sheet2 until the activecell in sheet 1 is blank, at which pioint it will open the next tally sheet and do the same thing..

Im v new to vba so im sure its a relaly obvious thing i need to do, i just cant figure it out!

[MarkNumskull]

and heres the intermediate sheet

[rbrhodes]

Hi Mark,

Had a look at your examples. Try this one.

Notes:

There is a hidden sheet 'List' that contains the staff names 'SaveAs' names. In other words William, Brenda, etc. with Mark and Mark listed as 'MarkMc' and MarkS. This would be a list of the saved file names and must match what the users are naming their files.

'List' also matches the payroll numbers to the names.

Any changes to staff names or numbers would be done on this sheet for the code, keeping it simple to manage, I think.

The code picks up the Stafflist names into an array and the payroll numbers into another array then does it all.