Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Collating date from a number of worksheets into one
Hi Guys,
Im trying to do something that i think should be really simple. I have a spreadsheet with 4 worksheets. I want to collate all the information from the 2nd, 3rd and 4th worksheet into the first worksheet. Is there an easy way i can do this with VB code or a macro? Selecting all the information into a recordset and then putting it in the first worksheet? Any help would be appreciated. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Collating date from a number of worksheets into one
Try this
http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Rooster" wrote in message ... Hi Guys, Im trying to do something that i think should be really simple. I have a spreadsheet with 4 worksheets. I want to collate all the information from the 2nd, 3rd and 4th worksheet into the first worksheet. Is there an easy way i can do this with VB code or a macro? Selecting all the information into a recordset and then putting it in the first worksheet? Any help would be appreciated. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Collating date from a number of worksheets into one
Thanks for the Link Rob. Just one quick question...rather than creating a
new worksheet to import the data into, I want to put all the data into a worksheet that already exists. How would i go about doing this? Thanks "Ron de Bruin" wrote: Try this http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Rooster" wrote in message ... Hi Guys, Im trying to do something that i think should be really simple. I have a spreadsheet with 4 worksheets. I want to collate all the information from the 2nd, 3rd and 4th worksheet into the first worksheet. Is there an easy way i can do this with VB code or a macro? Selecting all the information into a recordset and then putting it in the first worksheet? Any help would be appreciated. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Collating date from a number of worksheets into one
Use this then
If the sheet master not exist it will be created else in clear all cells on it before it merge all the data Sub Test3() Dim sh As Worksheet Dim DestSh As Worksheet Dim Last As Long On Error Resume Next If Len(ThisWorkbook.Worksheets.Item("Master").Name) = 0 Then On Error GoTo 0 Application.ScreenUpdating = False Set DestSh = ThisWorkbook.Worksheets.Add DestSh.Name = "Master" Else Set DestSh = Sheets("Master") End If DestSh.Cells.Clear For Each sh In ThisWorkbook.Worksheets If sh.Name < DestSh.Name Then Last = LastRow(DestSh) sh.UsedRange.Copy DestSh.Cells(Last + 1, "A") 'Instead of this line you can use the code below to copy only the values 'or use the PasteSpecial option to paste the format also. 'With sh.UsedRange 'DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _ '.Columns.Count).Value = .Value 'End With 'sh.UsedRange.Copy 'With DestSh.Cells(Last + 1, "A") ' .PasteSpecial xlPasteValues, , False, False ' .PasteSpecial xlPasteFormats, , False, False ' Application.CutCopyMode = False 'End With End If Next DestSh.Cells(1).Select Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Rooster" wrote in message ... Thanks for the Link Rob. Just one quick question...rather than creating a new worksheet to import the data into, I want to put all the data into a worksheet that already exists. How would i go about doing this? Thanks "Ron de Bruin" wrote: Try this http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Rooster" wrote in message ... Hi Guys, Im trying to do something that i think should be really simple. I have a spreadsheet with 4 worksheets. I want to collate all the information from the 2nd, 3rd and 4th worksheet into the first worksheet. Is there an easy way i can do this with VB code or a macro? Selecting all the information into a recordset and then putting it in the first worksheet? Any help would be appreciated. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CALCULATE NUMBER OF DAYS BETWEEN A DATE AND TODAY | Excel Worksheet Functions | |||
date number separation | Excel Discussion (Misc queries) | |||
Excel: I enter date and format for date, but shows as number | Excel Discussion (Misc queries) | |||
Number of days between 2 dates, if end date is blank assume todays | New Users to Excel | |||
Where is DateDiff function in Excel 2002 ? | Excel Worksheet Functions |