Posts by Hyperventilate

Thank you, I converted them again and the macro works!

Thanks Rory, I changed the source data to reflect dates, but encounter the same error. Please see attached.

Hi Carim,

This macro was working perfect until it suddenly started throwing 1004 error today. I am lost now. Attached my file for your reference please Test Shift Unique V3 (1).xlsm.

Debug points to this line: "Sheet2.Cells(2, 1).Resize(n, 5).Value = var"

Yes I did, will check again. Cheers!

Thanks Roy, works great. One thing is, even when I click 'no' in the pop-up the cell gets locked.

I have a spreadsheet that goes around the organization for many to fill. It gets messy when people keep editing their entries, so I need a macro to do the following:

1. When user enters any information in a cell (could be anywhere in the spreadsheet), give them a pop-up asking if they're sure about the entry

2. Once they confirm, accept the entry (irrespective of what is is, no need of any validation)

3. Lock that cell so there is no more edit possible, if someone attempts to edit, prompt for a password

Works perfect as always, thanks a ton!

Quote from Mumps

I'm not sure what you mean. The 'CutRows' macro loops once through all the sheets referenced in column A of Sheet1. Are you saying that you want to loop through all those sheets more than once? Please explain in detail what you are trying to accomplish.

Yes, you are right. I want it to loop through all those sheets more than once. I'm creating sets of data in a particular sequence, one loop is one set and so on. Sometimes I need 20 sets, so I run the macro 20 times, was wondering if it can be done in the code itself (though the number of sets varies each time).

Mumps, been using this macro splendidly. Just need another small addition please. Currently I need to execute the 'cut rows' macro manually for the number of times I want it to work. Could we do it in a way where I specify the number of times I want it to loop? Sometimes I want it to go 20 times and doing that manually is a pain. Attaching my file just for reference.

Works perfect now, thank you so much!

Oops, fixed that and it's giving me a different error now. "Subscript out of range". Screenshot attached.

Mumps, I just did a new file with your code and now it works well. Also very fast without hogging RAM. Thank you again!

Quote from Mumps

Oops! Replace those two lines of code with these:

Code

icol = ws.Cells(1, Columns.Count).End(xlToLeft).Column + 1
ws.Cells(1, icol) = "Unique"

I did this but now the macro doesn't do anything when I run it. Just starts and stops.

Really sorry for dragging this Roy, but it still shows an error. Please see screenshot.

It gives me a compile error. Sub or Function not defined, debug shows this line

ElseRange("A" & r).Interior.coloindex = vbRed

Quote from royUK

What do you mean. I thought the table contained the names of all the files?

Is this caused by empty cells in the list?

If files might not exist then the code will need to check first.

Employees are required to upload their photographs into one folder with file name as their Emp ID. I just generate the file names assuming everyone has uploaded the photos. But as is the case, not everyone has done it and the old macro stops when the file isn't found. Your new code runs and completes the move without stopping, but I'm unable to find out which files were not found in the folder.

Roy, this macro works perfect but it stops when a particular file is not found in the specified folder. Can we amend the code so it just makes a note of the missing file in another column and continues executing the movement?

I couldn't find that particular line of code, what I see is:

icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"

Mumps, I was running the file super good until today when I ran into "Excel cannot complete this task with available resources". When I run the macro, my task manager shows Excel using close to a GB of memory. I believe this happens when there are too many data ranges? After trying everything I could find on the net, I came across this solution that supposedly could fix it -

Mumps, I just managed to find the code for my requirement via Google. Thank you so much for your help.