Posts by Hyperventilate

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.

Quote from Mumps

I'm not really sure what you mean. The first sheet already has the sheet names in column A.

Also, it's hard to see what kind of data needs to be sorted because all the cells in the last column in each sheet in your sample file contain the same data. I think that it would be easier to understand what you want to do if you could attach your actual file with the data de-sensitized if necessary.

My bad, I had a feeling my post wasn't clear. This is what my work flow looks like:

1. I have a single spreadsheet with data that I need to first split into different sheets based on values in a particular column.

2. I already use a macro that does this splitting for me and names the sheets with the respective column values (eg. male, female, etc.)

3. I then use your macro to specify how much to copy from each sheet and create a dataset in sheet2 (eg. copy 40 rows from male and 30 from female).

4. Right now I am manually entering each sheet name into column A in the first sheet, I was wondering if there was a way to auto-populate these sheet names in column A so I know how much I need to copy from which sheet. This is because the data is dynamic and isn't always the same sheet names.

Regarding the sorting part, I figured a way to do it without the macro, so we could leave that out.

Quote from Hyperventilate

Sorry I couldn't revert earlier. Just got to using the macro today, thank you. If I may ask, I have two more tweaks that can make my work much easier:

1. Can we have a code to populate all the sheet names in the first sheet? Right now I'm getting a little confused as to which sheet has which data to specify the number of rows to cut-paste into Sheet2

2. After each cut-paste, I need the data to be sorted based on the last column.

Forgot to add to point 2:

2. After each cut-paste, I need the moved data set to be sorted based on the last column (not with the previous existing data in sheet2, but just that particular copy). Because each copy action is a different set and only that has to be sorted. Sorting should be in ascending order.

Quote from Mumps

Please ignore the previous post and try this version:

Code

Sub CutRows()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWS As Worksheet, sh As Range
    Set srcWS = Sheets(1)
    Set desWS = Sheets(2)
    For Each sh In srcWS.Range("A2", srcWS.Range("A" & Rows.Count).End(xlUp))
        With Sheets(sh.Value).Rows("2:" & sh.Offset(, 1).Value + 1)
            .Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
            .Delete
        End With
    Next sh
    Application.ScreenUpdating = True
End Sub

Display More

Sorry I couldn't revert earlier. Just got to using the macro today, thank you. If I may ask, I have two more tweaks that can make my work much easier:

1. Can we have a code to populate all the sheet names in the first sheet? Right now I'm getting a little confused as to which sheet has which data to specify the number of rows to cut-paste into Sheet2

2. After each cut-paste, I need the data to be sorted based on the last column.