Making different sheet from the original sheets data

  • Thread starter Thread starter Shetty
  • Start date Start date
S

Shetty

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
 
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.
Click to expand...

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
 
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
 
Hi Shetty,
Do the 48 offices have internet connections? Servers or Co. LAN? Emailed the
workbooks?
You can use data>get 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 data>get 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.
 
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,
 
Back
Top