We print job cards for employees and to save paper we print two cards per A4 sheet. To achieve this in Word mail merge, I copy record 2 next to record 1 and so on in Excel so mail merge can print record 1 on the top half and record 2 on the bottom. The new requirement now is such that the print should run continuously on the top half and then continue on the bottom from where it stopped. E.g.1-50 on the top half and then 51 to 100 on the bottom. I'm lost as to how to achieve this. I've attached my file for reference with two sheets.
Posts by Hyperventilate
-
-
I tried searching other threads but couldn't find something related to my requirement.
I have a roster of employee data (attached). I want this data to be bifurcated into two halves based on date and shift and cut-pasted beside each other.
Eg. 14th June has 4 shifts, if the first shift has 100 employees, it has to be divided by two, so 1-50 will stay in the same place, whereas 51-100 will be cut and pasted next to the 1-50 data. And so on date-wise and shift-wise. If the number is an odd number, the adjoining row can be left blank. The Example Sheet in the file has this arrangement which I did manually for demo. I left a row blank in the example sheet data to demonstrate, the actual output doesn't need that or the headers to be repeated.
In case someone wants to know why - we print job cards for these employees and to save paper we do two per A4 sheet. Once data is bifurcated like this, is becomes easy to print. Once the A4 sheets are cut and the bottom stack is placed below the top stack, it becomes one continuous stack of job cards to be distributed.
-
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
-
-
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.
-
-
-
Really sorry for dragging this Roy, but it still shows an error. Please see screenshot.
-
-
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?
-