VBA Code To Unmerge Cells On Specific Worksheets
Jan 27, 2010
I have a workbook with multiple worksheets. On some of those worksheets a have merged cells, so in order to paste new data I need to unmerge them first. Below is the code that I wrote for it. However it doesn’t work and returns “Run-time error '1004': Application-defined or object-defined error”. I looked at this post List Maximum Value From Each Worksheet and tried adding With – End With, but it failed too. As far as I understood the problem is that the Worksheets/Sheets object does not support UnMerge property, but I may be wrong. If I add ws.Activate line (test2) everything works fine.
However I was wondering if there is a way to do it WITHOUT activating the worksheets.
Sub test1()
Dim i As Integer, ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Select Case ws.Name
Case "5 - Top Network Facilities", _
"5b - Top Arb Facilities"
For i = 0 To 9
ws.Range(Cells(2 + i * 5, 1), _
Cells(6 + i * 5, 1)).UnMerge
Next i
Case Else
End Select
Next ws
End Sub
View 4 Replies
ADVERTISEMENT
Dec 4, 2009
Let's say I have 6 Worksheets in my Workbook with the following names:
Sheeta1
Sheeta2
Sheeta3
Sheetb1
Sheetb2
Sheetb3
And I have three Subs that I want to run, one Sub for two specific Worksheets.
How could I code my Subs so that the first Sub runs on Sheeta1 and Sheetb1, the second Sub runs on Sheeta2 and Sheetb2, and the last Sub runs on Sheeta3 and Sheetb3?
As you can see, the 1, 2 and 3 in the Worksheet names are what links the Subs to the Worksheet.
View 9 Replies
View Related
Dec 31, 2009
I have a question/problem on using Unmerge in VBA. I have a spreadsheet that in Column J has some cells/rows that are merged. I need to unmerge these cells. Not all rows have this merge, so I think there would need to be some sort of IF statement or something...but I'm not sure how to go about it.
I can say that if, for example, cell J19 is merged, it would be with K and L. Then maybe, J24 with K24 and L24 are merged. Then maybe J27 with K27 and L27....and so on. For the most part the sheet would have about 200 rows of data. But only would I find about 20 or so of these merged cells. I also have 19 excel files to do this to, or otherwise, I would just do it quick and dirty, manually-style.
As an added task, once the cells are unmerged I'd like to take the value found in cell O19 (same row as where the merge was found) and copy it into L19.
View 5 Replies
View Related
Aug 10, 2009
Seems like a dumb question but is it possible to unmerge say one "3 cell" merged cell with a value of "A" into 3 SINGLE CELLS with a value of "A"?
View 9 Replies
View Related
Jun 14, 2008
I have read the posts regarding merged cells and understand they should be avoided, but I am working with spreadsheets created by others. My problem is that I need to sort the data, but some of the cells are merged so it won't work. Is there a macro command to easily find the merged cells and unmerge them?
View 5 Replies
View Related
Nov 6, 2008
Is there any quick and easy way to unmerge merged cells and put the merged cell value in all the merged cells. For example, if I have A6:A15 merged together and its value is "FirstMerge" then I would like to unmerge all A6:A15 and then put "FirstMerge" in all the cell A6,A7,A8 etc. etc. upt oA15. I have lot of these merged cells in a sheet and I get this every month and makes any data processing very difficult. A macro or VBA code or any other solution will be fine.
View 4 Replies
View Related
Nov 29, 2011
is there any vba code that I can add to my workbook sheet 'Cognos Input' to make anything I paste onto/into it unmerge on the paste key stroke.
View 1 Replies
View Related
Jan 29, 2008
I have created an excel addin, the purpose of which is to run a piece of code attached below in code section, which iterates to each cell of excel and remove merged cells. I am getting a very basic error as my addin is not able to recognize the workbook which is opened and it returns Nothing for ActiveWorkbook object....
View 9 Replies
View Related
May 16, 2007
In the attached document, there are some cells which have been merged. For instance the cells A4 thru A7 were merged as one field. if you examine B4 which is actually made up of B4 thru B7 the data contained therein is 123. Is there a way to unmerge these merged cells and have the value (in this case 123) appear in each line of the unmerged cells? If you right click and click unmerge, it unmerges okay but does not populate each line with the data which was on the original field.
View 3 Replies
View Related
Jul 9, 2014
I am using Excel 2010 and have the problem as shown in the attached file.
Input Sheet shows the Data I have at present
Output Sheet is the desired result.
I need a macro which should create an "Output" sheet by doing the following on the Input Sheet
1)Insert a Blank Column before Column A
2)Unmerge the Region Heading and insert the respective Region Name in the newly inserted Column. Region Heading will be in Bold Font.
3)Repeat Step 2 for all Regions
4)Delete the Rows which was merged.
Please note that the number of Data Rows will vary for each Region.
I have shown two Regions for explanation purpose only. There will be several Regions in reality.
The result is shown on the Output sheet
Merge Problem - Forum.xlsx‎
View 3 Replies
View Related
Mar 27, 2009
Is there a way to compare specific data from three separate worksheets to see if they are equal?
I receive three daily reports that contain some of the same information that must match in order to correctly compile an Executive Report. Currently, I am doing this manually. Is there any way to automate the process to have a warning or something similar pop up if the numbers do not match?
View 2 Replies
View Related
Aug 13, 2013
I have a workbook that's setup like a form, with 30+ worksheets in it (lets say this file is called "source.xlsx"). I have information in specific cells I want pulled out in each worksheet and brought in to a new workbook called "Destination.xlsx" and listed in a row.
View 2 Replies
View Related
Jan 13, 2014
I have made a basic holiday planner, where each employee has one worksheet which shows 52 x 5 day weeks.
When an employee asks for holiday, I put a 1 in the corresponding day (cell). What I need to happen is that if 4 first aiders all ask for holiday on the the same date a warning User form loads up.
The names of the 4 worksheets are;
"Jason L 2014", "Sharon L 2014", "Alan B 2014" and "Chris M 2014".
The ranges for the 5 day weeks are
"B7:F7", "L7:P7", "V7:Z7"
"B10:F10", "L10:P10", "V10:Z10"
"B13:F13", "L13:P13", "V10:Z13"
"B16:F16", "L16:P16", "V16:Z16"
[Code] ........
The code should not run the User form until the sum of the same cells across the 4 worksheets = 4.
By this I mean if 3 of the first aiders book B7 off, the code searches the 4 worksheets and if the sum is 3 then nothing happens. However if the 4th and final first aider tries to book the same day off, when the code runs it totals 4 and the User form runs.
View 1 Replies
View Related
Aug 28, 2013
know the VBA script to select only specific cells within a worksheet. To be more precise, on sheet 1 - A2, A6 and A10 cells are filled with red color. I want only those cells which are highlighted in red to be selected, copied and pasted to sheet2.
View 3 Replies
View Related
Apr 15, 2014
I wanted to ask how to delete a specific range of data in a cell range using VBA
Here is a snipit of code I currently have:
Sname1 = Sheets("Numbers").Range("FX3").Value 'FX3 = cell F starting column letter
Range(Sname1 & Range("FR7").Value).Select 'FR7 = row number
If I added the following:
Sname2 = Sheets("Numbers").Range("FX4").Value 'FX4 = cell n ending column letter
Changed the code to the following: obviously this code is incorrect below:
Range(Sname1 & Range("FR7").Value) : Range(Sname2 & Range("FR7").Value).Select
View 3 Replies
View Related
Oct 24, 2008
I am trying to write a VBA code in excel to delete any columns when a cell in that particular column = 0 (zero). That cell would start at B19 and subsequently follow to C19, D19 and so on.
View 9 Replies
View Related
Nov 9, 2006
I have the following code attached to a userform. It works perfectly (for what I need) although I know it may not be perfectly written. However it does seem to take for ever to execute considering it's just filling some cells on different worksheets. Have I missed something out or got it tied up doing something that takes a long time. If it's just a fact of life that it takes this time then I can live with it.
Private Sub UserForm_Activate()
Dim wsCB As Worksheet, wsLL As Worksheet, wsBond As Worksheet
Dim rngFound As Range
Set wsCB = Sheets("Current Bonds")
Set wsLL = Sheets("Landlords")
Set wsBond = Sheets("Bond")
TenancyStartTxt = Format(TenancyStartTxt, "dd-mmm-yy")
RnwlDteTx = Format(RnwlDteTx, "dd-mmm-yy")
Application. ScreenUpdating = False
10 MyBond = Application.InputBox("Bond number for renewal?", "Bond Number")
strFindMe = MyBond
With wsCB
Set rngData = .Range("c13:c490").......................
View 5 Replies
View Related
Aug 8, 2008
"Get rid of merged cells!"
You can read it often on this Board.
I will not open any debate on the subject, but just want to provide a solution to get rid of them automatically. Anybody who likes can refer to this thread.
WARNING
This code is changing layout.
1. Could take a while when you have sheets with large used range or a lot of merged cells. You can follow the status and can interrupt it if you want.
2. Save before executing. I'm quite sure it works good, but the result might be something else then you had in mind, especially when you are quite new to this.
Option Explicit
Sub Unmerge_CenterAcross()
'Erik Van Geit
'080808
'merged cells will be unmerged
'contents will be centered across merged area
Dim LR As Long 'Last Row
Dim LC As Integer 'Last Column
Dim i As Long
Dim j As Long
View 9 Replies
View Related
Jul 31, 2009
Can we unmerge a complete address in seperate seperate coloumns? Like Complete address is "1st Floor, 2a Harrison Road, Erdington, West Midlands, B24 9AA" and i want to split it like Address[1] "1st Floor" , Address[2]: "2a Harrison Road", Address[3]: "Erdington"...
Is there is any formula which can split data with the help of seperators (Comma or space) ??
View 3 Replies
View Related
Sep 15, 2014
I have a protected sheet to avoid delete of functions. In a specific area, however, i have merged cells of five columns in rows. Because in these lines i have write some text and overcomes the rows, after, what i do is: i unmerge the rows and choose 3 rows from 5 columns together and merge. Thus, the text is in one framework. In fact, every time, i want to merge different regions, but always i want to merged any areas who have marked with the mouse. Then, i delete the text and i need to redo the context of three lines, three separate lines. I hope you understand what i need.MergeAndUnMerge.xlsm
View 1 Replies
View Related
Jul 7, 2014
Let's say I have a workbook with 7 worksheets named, for example, "Instruction", "Begin", "Worksheet 1", "Worksheet 2", "Worksheet 3", "End", and "Data". (in that order)
What I want to do is run a macro to go to whatever worksheet that is in between "Begin" and "End" and copy, for example, cells $C$1:$D$10; then paste as formula into worksheet "Data" starting from cell C1 and then down a list (i.e., copied cells from "Worksheet 1" get pasted as formula into "Data" cells C1:D10; then copied cells from "Worksheet 2" get pasted as formula into "Data" cells C11:D20, and so on and so forth).
But if I were to add more worksheets (e.g., "Recipe" and "ToDo") positioned in between "Begin" and "End" and run the macro again, it'll either 1) re-copy all the formulas from the included worksheets back into "Data" including the formulas from the newly added/placed worksheets or 2) it'll add the formulas from the newly added/placed worksheets and paste into "Data" at the end of the list.
Can create the macro to run based on the position of worksheet, and not based on the name of worksheet, since ultimately there will probably be over 10 worksheets between "Begin" and "End".
View 4 Replies
View Related
Nov 7, 2008
I have a protected template and unprotected source worksheets - - - what I would like to happen is for the macro to start and if the source worksheet cell B3 equals "Report Total" then stop - otherwise copy template worksheet then copy 6 specific cells from the source to paste values to specific cells on the newly created worksheet (B_ to C7, D_ to I7, E_ to C9, F_ to K9, A_ to C11, M_ to K11 and then K13=F13-30)
After that then start all over again unless the next row’s cell (B4, B5, B6, . . .) is "Report Total" then stop - - - the row count could be from one to a couple hundred.
Here is what I have so far but I know that with each copy the name will change and as it goes down the source file each row will change and I also need help with that.
Sheets("ee template").Copy After:=Sheets(3)
ActiveSheet.Unprotect
Selection.ClearContents
Range("I7").Select
Selection.ClearContents
Range("C7").Select
Sheets("source").Select
ActiveCell.Offset(0, -11).Range("A1").Select
View 9 Replies
View Related
Oct 16, 2009
I have the following code that lists all the worksheets in workbook. I would like to have this list show only the worksheets that end in "SD". (For example, it would show "MainSD" or "CantonSD" but not "Main".)
View 14 Replies
View Related
Jun 3, 2012
I would like to use VBA to combine specific sheets. I have a workbook containing about 15 sheets but want to combine 6 specific sheets. All the sheets have the exact same headers and number of columns but the number of rows are different.
I found this VBA code that works but it combines ALL of the sheets in the workbook. How to modify this to combine specific sheets. How to combine all visible sheets if that's possible.
Here is the code I found...
Sub CopyFromWorksheets()
Dim wrk As Workbook 'Workbook object - Always good to work with object variables
Dim sht As Worksheet 'Object for handling worksheets in loop
Dim trg As Worksheet 'Master Worksheet
Dim rng As Range 'Range object
[Code] .........
View 1 Replies
View Related
Apr 16, 2013
I have tried Sheets("").Select to just have the macro work on any worksheet in a workbook not just on the specific worksheet (i.e.Sheets("4474-60-2").Select . Get an error though.
View 9 Replies
View Related
Oct 9, 2009
Here is what i am trying to achieve. If the date 2/20/2010 is located at F53 & the cell next to it at H53 is populated with a number between 1 & 16, then i want the cell at J11 (42 rows further up) to auto populate with the number 1. When this occurs the cells beneath this, from J12 to J52 should also auto populate with the with consecutive numbers from 2 to 42. Would also like to see the cells with numbers 1 to 28, automatically format to orange & the cells containing numbers 29 to 42 automatically format to yellow. I plan to have this condition repeat several times later in the year, at dates that are to be decided. When these dates are decided i want to be able to enter a number from 1 to 16 & next to the date & all of the above automatically occurs.
View 14 Replies
View Related
Dec 31, 2009
I have two worksheets. On one worksheet under column G I have hundreds of random numbers, but I only want to extract a specific half. In the second worksheet I have two columns A and B. Column A has the exact numbers that I want to extract from Column G. I would like to get the numbers from Sheet 1 under column G listed under Sheet 2 Column B in reference to Column A.
View 9 Replies
View Related
Mar 12, 2014
I have five worksheets hidden in my workbook. All of these worksheets have either "Yes" or "No" written in cell C4. (3-yes, 2-no)
I also have a search bar in the form of a textbox and a command button.
I want to be able to type "No" in the textbox, click the command button and have the pages with "No" in cell C4 to unhide.
Some screenshots if necessary:
Sheet1 (yes): Gyazo - ce85d71bc03cd2ceaa1030e22445800f.png
Sheet2 (no): Gyazo - 65f876d6e7d749bd1ca25ad957fbda99.png
Sheet3 (yes): Gyazo - 0cb442f1d34e7d744884f73e1afe2646.png
Sheet4 (yes): Gyazo - 6f0ce150322208fd93dc5fc6f4f66481.png
Sheet5 (no): Gyazo - 772f48f8a82e53713794e6655fe56fca.png
master: Gyazo - 1b71470f3de47cef347050588bca819c.png
View 4 Replies
View Related
Oct 27, 2007
Looking for For Loop to loop through 5 specific worksheets in a work book.
Something like this, but can't find right syntax:
For iCounter = 1 to 5
If worksheet.name = "Recap" & iCounter Then
'do stuff
End If
Next iCounter
View 4 Replies
View Related
Jun 22, 2006
I'm building a spreadsheet which has a formula which performs a vlookup on a sheet. Now I want to leave the vlookup formula so that you don't need to change it. What I want to achieve is that the vlookup table array references a cell in the current worksheet which is a name of another worksheet.
What this will enable me to do is to keep the forumla unchanged by by changing the cell with the worksheet name in it points the vlookup to a different sheet. This is because I have multiple sheets which have similar data on and I don;t want to edit the forumla to point to the different sheets.
View 5 Replies
View Related