Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making different sheet from the original sheets data
I am a newbie in areas of the macro and VBA. But I am comfortable with
the normal formulas used in excel. The problem : I am getting the report from all the area offices every week. and I do not have any control over the format they are using. These reports are in a central compiled sheet where all the offices are adding a raw every day with date and other data (32 columns) which makes the sheet with around 1800 raws of data as of now. I need to saperate and copy the records (raws) of all the offices in another sheet every week. Presently I am doing it with the auto filter and copy , paste in new sheet and then renaming the sheet with the office name. There are 48 area offices for which I have to make 48 different sheet in the work book in addition of the original sheet. This is a time consuming and very tedius method. I am sure there must be some other ways easy and time efficient in terms of a macro or VB Code. Request help from the group please. Thaks in advance. shetty |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making different sheet from the original sheets data
I am sure there must be some other ways easy and time efficient in
terms of a macro or VB Code. Request help from the group please. I am sure, too, but your being far more specific will likely get a much better response here. You could also try automating by turning on the macro recorder while doing your usual work. HTH, Merjet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making different sheet from the original sheets data
I have tried the macro recorder but it works only for the specified
raws. If turn on the absolute referance, it messed up the things and gives unexpected results. Now to be more specific : The central compiled sheet has got following columns. Date, Office, mat size, run length, contractor, rate, amount, paid in bill no . . . . . etc upto 32 columns. Every day new raws of records are added by the area offices. I have to compile the same and prepare the report every week. What I have to to is to copy all the records (raws) of one office and past it to a new sheet in the same workbook and rename the sheet with the office name. These needs to be done for all the 48 offices. Hance the total no of sheets in a file will be 48 plus one original central sheet. What I need is a macro or VB code that copies the raws of each office, create a new sheet with the office name and paste the copied raws to the same. This action cycle repeats for all the different 48 offices. I hope this is clear. I am also willing to share the workbook if required. Regards, Shetty "merjet" wrote in message .net... I am sure there must be some other ways easy and time efficient in terms of a macro or VB Code. Request help from the group please. I am sure, too, but your being far more specific will likely get a much better response here. You could also try automating by turning on the macro recorder while doing your usual work. HTH, Merjet |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making different sheet from the original sheets data
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making different sheet from the original sheets data
Hi Shetty,
Do the 48 offices have internet connections? Servers or Co. LAN? Emailed the workbooks? You can use dataget external data to make a connection to each workbook, add a worksheet, and pull the data from each office's workbook. You have to know the name and location for each workbook. You can do this by having each office save their workbook with a predefined name and/or location. Ex: "office1 10-23-03.xls" stored on shareddrive \\servername\shareddrivename\path or folder (or the only file in that folder) Now you can create a loop using the servernames/sharenames stored in an string array. The filename can be calculated within the loop. (you'll need the sheetname also) You can record a macro while doing dataget external data and the revise the code by adding the loop. A pain to setup but once it's working it will only take a few minutes to complete. -- John johnf202 at hotmail dot com "Shetty" wrote in message om... Can anyone help please? Regards, Shetty (Shetty) wrote in message . com... I have tried the macro recorder but it works only for the specified raws. If turn on the absolute referance, it messed up the things and gives unexpected results. Now to be more specific : The central compiled sheet has got following columns. Date, Office, mat size, run length, contractor, rate, amount, paid in bill no . . . . . etc upto 32 columns. Every day new raws of records are added by the area offices. I have to compile the same and prepare the report every week. What I have to to is to copy all the records (raws) of one office and past it to a new sheet in the same workbook and rename the sheet with the office name. These needs to be done for all the 48 offices. Hance the total no of sheets in a file will be 48 plus one original central sheet. What I need is a macro or VB code that copies the raws of each office, create a new sheet with the office name and paste the copied raws to the same. This action cycle repeats for all the different 48 offices. I hope this is clear. I am also willing to share the workbook if required. Regards, Shetty |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making different sheet from the original sheets data
Hi Jaf,
This is not the case. It is exactly reverse. All these 48 offices are updating the same sheet, file kept on the server. I have got only one sheet updated by all the office. I have to saperate out the records of each office to new sheet with respective office name as sheet name. Hope this is clear now. Anyway, Thanks for your reply but need some other solution. Regards, "jaf" wrote in message ... Hi Shetty, Do the 48 offices have internet connections? Servers or Co. LAN? Emailed the workbooks? You can use dataget external data to make a connection to each workbook, add a worksheet, and pull the data from each office's workbook. You have to know the name and location for each workbook. You can do this by having each office save their workbook with a predefined name and/or location. Ex: "office1 10-23-03.xls" stored on shareddrive \\servername\shareddrivename\path or folder (or the only file in that folder) Now you can create a loop using the servernames/sharenames stored in an string array. The filename can be calculated within the loop. (you'll need the sheetname also) You can record a macro while doing dataget external data and the revise the code by adding the loop. A pain to setup but once it's working it will only take a few minutes to complete. John johnf202 at hotmail dot com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to copy sheet with charts without link to original data | Charts and Charting in Excel | |||
Making a new XL file form the Original XL data - Misho | Excel Discussion (Misc queries) | |||
Copy Sheet to new Sheet and clear cells on original sheets | Excel Discussion (Misc queries) | |||
Renaming sheets with part of original name | Excel Worksheet Functions | |||
how do you delete sheets in excel but keep your original? | Excel Discussion (Misc queries) |