| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
consolidating sheets - Ron de Bruin can you help me. Thank you
Thank you for the advice, have a great day
"Ron de Bruin" wrote: Hi Eduardo If you post it in this newsgroup with a good subject Anybody can use Google then to find you code example. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Eduardo" wrote in message ... Hi Ron, Thank you very much !!, that works perfect Ron, I have a question, a year ago I needed to change an excel form into an EDI format, I was told that was not possible, however I broke in part my problem ask differente questions to the comunity and put together all the answers, the result was what I was looking form, an order form in an excel format converted to an EDI format to be uploaded into the company's system. The comunity has helped me a lot so I'd like to know if there is any place where I can upload the macro and maybe somebody might use it as well. and thank you again for your help "Ron de Bruin" wrote: One way 'Set the range that you want to copy Set CopyRng = sh.Range("D" & StartRow & ":IV" & shLast) You can change the IV column if you want -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Eduardo" wrote in message ... Hi Ron, Yes correct "Ron de Bruin" wrote: Hi Eduardo You want to copy from column D till the last column with data Am I correct -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Eduardo" wrote in message ... I figure out what posted before but I have read all your page and I cannot find how to exclude the 2 first columns since that ones contain information for validation data in column B and C "Ron de Bruin" wrote: Read the information good http://www.rondebruin.nl/copy2.htm On top of the page you can read Important: The macro examples use the LastRow or LastCol function that you can find in the last section of this page. Copy the macro(s) and function(s) in a standard module of your workbook. If you have no idea where to paste the code then check out this page. http://www.rondebruin.nl/code.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Eduardo" wrote in message ... I am working with Excel 2007, What I need to do is to summarize all visible tabs into one starting in row 5 I tried to use Ron code but is giving me an error message Compile error - Sub or function not defined and highlighted is the sentence as follow Last = LastRow(DestSh). The code I'm using is Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long Dim shLast As Long Dim CopyRng As Range Dim StartRow As Long With Application .ScreenUpdating = False .EnableEvents = False End With 'Delete the sheet "RDBMergeSheet" if it exist Application.DisplayAlerts = False On Error Resume Next ActiveWorkbook.Worksheets("RDBMergeSheet").Delete On Error GoTo 0 Application.DisplayAlerts = True 'Add a worksheet with the name "RDBMergeSheet" Set DestSh = ActiveWorkbook.Worksheets.Add DestSh.Name = "RDBMergeSheet" 'Fill in the start row StartRow = 5 'loop through all worksheets and copy the data to the DestSh For Each sh In ActiveWorkbook.Worksheets 'Loop through all worksheets exept the RDBMerge worksheet and the 'Information worksheet, you can ad more sheets to the array if you want. If IsError(Application.Match(sh.Name, _ Array(DestSh.Name, "Information"), 0)) Then 'Find the last row with data on the DestSh and sh Last = LastRow(DestSh) shLast = LastRow(sh) 'If sh is not empty and if the last row = StartRow copy the CopyRng If shLast 0 And shLast = StartRow Then 'Set the range that you want to copy Set CopyRng = sh.Range(sh.Rows(StartRow), sh.Rows(shLast)) 'Test if there enough rows in the DestSh to copy all the data If Last + CopyRng.Rows.Count DestSh.Rows.Count Then MsgBox "There are not enough rows in the Destsh" GoTo ExitTheSub End If 'This example copies values/formats, if you only want to copy the 'values or want to copy everything look below example 1 on this page CopyRng.Copy With DestSh.Cells(Last + 1, "A") .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats Application.CutCopyMode = False End With End If End If Next ExitTheSub: Application.Goto DestSh.Cells(1) 'AutoFit the column width in the DestSh sheet DestSh.Columns.AutoFit With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Consolidating sheets | Excel Discussion (Misc queries) | |||
| Consolidating all sheets | Excel Discussion (Misc queries) | |||
| Consolidating data from 4 sheets to one! | Excel Worksheet Functions | |||
| Consolidating several sheets into one. | Excel Programming | |||
| Consolidating sheets | Excel Worksheet Functions | |||
Hybrid Mode
