• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 725
  • Last Modified:

Excel macro to copy data from one sheet to another and create borders.

Hi,

Excel macro to copy data from one sheet to another and create borders.
Attached is a workbook that has 2 sheets.


Match colum B Unc path with Other sheet or sheets colum A
And update Colum D name into colum E on all sheets
Color the cells in the UNC is not found in 1 or all sheets
Create borders in all sheets as shown in the sheet1.
Sheet names can be any
No of sheets can be 1 or more
Index naming for this sheet will be the same


REgards
Sharath
Copy---Border.xls
0
bsharath
Asked:
bsharath
  • 24
  • 21
1 Solution
 
dandrakaCommented:
Could you be a little more specific about this?

ie.  Do you want the value in column B  the INDEX sheet be matched to any value in column A in the Sheet1 sheet?  And then what do you wnat to happen?
0
 
bsharathAuthor Commented:
Colum to match any sheets colum A if match found copy colum D data to colum E
And then create all Bold borders as per the attachment..
0
 
gowflowCommented:
Well your explanation is a bit confusing as we don't know the state in which the data is prior to transformation. This is my understanding so far:
1) I get it that Sheet1 has the final layout that you want
2) You want to position yourself in sheet Index and lookup information and then update sheet1 as per all the instructions that you laid out

If both above reasonning correct, then I need you to provide state in which the data exist prior to transformation. Like I need a copy of the data in sheet1 prior to executing all these tasks (I presume index sheet is filled manually)

Pls correct my reasonning if wrong
rgds/gowflow
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
bsharathAuthor Commented:
Sheet1 is the actual data. Just the borders are the changes and the Colum "E" data is got from the Index sheet colum "D"
The index sheet is manually done and the sheet1 is already available. When macro run has to set the borders as shown in sheet 1
and then colum "D" data from Index sheet to Sheet1 colum "E"
If any data not found in Index color it to Red
The bordering partition has to be if 2 rows blank data in sheet1 then leave that and create borders just from there.
You can see 2 different blocks of data
0
 
gowflowCommented:
ok clear. 1 more, is Sheet1 brought to be changed coz you said many sheets in your original post like you will need this formating in any sheet except Index and regardless what sheet name it is provided all sheets in this workbook on the same principle discribed right ?
rgds/gowflow
0
 
bsharathAuthor Commented:
All sheets except Index...The sheet names can be anything. if sheet names has to be correct then we can have them as
Sheet1,Sheet2,Sheet3 and so on
0
 
gowflowCommented:
No its ok sheet names can be anything I don't hv a problem. Pls confirm they are all within SAME workbook and not also in an external workbook or else this would imply diffrent approach.
rgds/gowflow
0
 
bsharathAuthor Commented:
All are in the same workbook
0
 
gowflowCommented:
ok will work on it pls allow me till tomorrow as a bit caught into something at hand. For sure if no one is faster and give u the solution !!
rgds/gowflow
0
 
bsharathAuthor Commented:
Any views on this...
0
 
gowflowCommented:
Sorry was very busy and overlooked this one. Sorry again I will work on it
gowflow
0
 
gowflowCommented:
I am working on it but it would help me a lot if you can post the sheet prior to it beeing corrected with borders to see what need to be done. As when you say:
When there is no data color it ...
and in your example sheet you colored part
\\SRV01\Training Videos       BUILTIN\Administrators
although under it there is Data
Dev1\can90
Dev1\can91
Dev1\can92
Dev1\can93
Dev1\can94
Dev1\can95
Dev1\can96
Dev1\can97
Dev1\can98
Dev1\can99
Dev1\can100
...

True that this data comes after more than 1 blank line ... but this is why I need to get copy of the file Sheet1 or any other sheet BEFORE it is beeing colored and border applied and manipulated.

Does these sheets that you want to convert are manually updated or created automatically by some sort of program ??

rgds/gowflow
0
 
bsharathAuthor Commented:
Gowflow

I can give you a sample but the only  difference would be with no borders at all...

One sheet is done manually if data does not match color Red

Yes the other sheet is done by a script that pulls all data from the File servers for permissions records
0
 
gowflowCommented:
Fine JUST POST IT if you want results !!! I know now why I droped this question I asked you to post the file since my first comment and you never did !
Sorry cannot assist you until I get the file.
rgds/gowflow
0
 
bsharathAuthor Commented:
Hi,,

in the post i have the Example excel attached... After that i guess i could not find that you asked me for a file...Sorry

Attached the file now...Please have a look....

A new sheet called raw file is there. The looks like before the macro is run...
Copy---Border.xls
0
 
gowflowCommented:
I clearly asked for the file in ID: 26276637
quote
If both above reasonning correct, then I need you to provide state in which the data exist prior to transformation. Like I need a copy of the data in sheet1 prior to executing all these tasks (I presume index sheet is filled manually)
unquote

I'll be looking at the file
rgds/gowflow
0
 
bsharathAuthor Commented:
Hi when time permits please have a look....
0
 
gowflowCommented:
yes sure hv what I need now. Don't worry will get there but pls be patient as beeing bombarded by work
rgds/gowflow
0
 
gowflowCommented:
by the way you did not answerr me fro the ID 26321540 when there is data and still it is in the block that has no data is it becoz you have more than 2 blank lines ?? if yest can you state what is the purpose behind the block coloring and why there is data that is ignored ?
gowflow
0
 
bsharathAuthor Commented:
Block color was by mistake plz ignore that
0
 
gowflowCommented:
Then what do you mean by mistake ??? What should it be then ? Sorry your confusing me !
I asked you about the block that had data but was grouped with a block that did not have data so what is the right from wrong ?

Do you realise that each time you change something it is taking trumendous lines of code ???
PLs get it right and what you want exactly so I can help you
gowflow
0
 
bsharathAuthor Commented:
There should be no color...

There are 3 sheets
Index sheet ( Is the Data manually entered) Its all set)
Sheet1 or any sheet name other than Index (Will have the Raw data)
Raw
Copy---Border.xls
0
 
gowflowCommented:
Well look at what you just send me !!!!! Only 2 sheets: Index and Sheet1
NO RAW sheets !!!

And still you haven't answered:
WHAT DO I DO WHEN THERE IS DATA THAT IS MORE THAN 2 LINES BLANK ?????????????????????????????????????????????????????????????? do you concider it data or no data ? should it be in a block by itself or with the block that preceed it
I am talking about rows 54 to 82 !!!

Sorry but this is getting no where !
gowflow
0
 
bsharathAuthor Commented:
:-(

Sorry i dont know why its going so wrong...
Attached the new file

There are 3 sheets
Index sheet ( Is the Data manually entered) Its all set)
Sheet1 or any sheet name other than Index
Raw Sheet (Will have the Raw data)

Explained a bit in the sheets too

Colum A data in the sheet for example row 54 to 82 has this
\\SRV01\Training Videos

So if its a match then its 1 block

If you mean blank rows more than 2. Then stop the code...
The only time there will be more than 2 rows blank is when its end of sheet data

Hope this answers all the Questions...


Border.xls
0
 
gowflowCommented:
ok still 1 thing:

In sheet1 you have 2 blocks right? by block I mean border closed
First block from line 1 to line 47
then you have 2 blank lines 48, 49
then you have 1 block from line 50 till 82

What I have hard time understanding is the pattern Why the first block omit line 48,49 and why the second block is all together ?
The only thing that make sense is the fact that the first block has Bak in it then they are all grouped together and the second has Nil and they are grouped together ?

Is this the pattern you want ? like all Bak together and all Nil together like shown in Sheet1 ?

And case it is correct is it possible to have in the output some bak that are mixed with some Nil ?

rgds/gowflow
0
 
bsharathAuthor Commented:
You can see those are 2 different sets
\\SRV01\Pat
&
\\SRV01\Training Videos

We need to match the colum A
If Colum A has the same data then they are 1 block
0
 
bsharathAuthor Commented:
Row 3 to 47 is one set
50 to 82 is another

We need to see 2 full rows blank as seperators
Colum A data to be matched in same colum if different consider as another
0
 
gowflowCommented:
Fine clear.

1) ok but is it possible to have a mix like \\SRV01\Pat then some \\SRV01\Training Videos then some \\SRV01\Pat or all grouped together?

2) Is it possible to have a file that hgas more data not a problem if it is big. The bigger the better !!!

rgds/gowflow
0
 
bsharathAuthor Commented:
1) No all will be in a series always
2) Its very big the rows are 18,000
But have a lot of Confidential names and addresses in them...
I can copy and paste the same and make them with different names if you want
0
 
gowflowCommented:
Hi
Everything is fine I only have a problem with the blank lines in
Line 55,56,57 !!! becoz below it is still the same group \\SRV01\Training Videos

Are you sure they exist it is not 3 lines by mistake that you added coz it does not make sense you said 2 blank lines start a new section and here in hte same section we have 3 blank lines !!

The whole program is done and ready to be sent to you except this small issue that is very weired.

Can you pls chk the data that you hv raw that comes directly without manipulation and confirm if there is 3 blank lines or it is there by mistake ?

rgds/gowflow
0
 
bsharathAuthor Commented:
Hi
That was colum D blanks
We can cosider 2 rows blanks the whole row and where colum A data is no where else below it
0
 
gowflowCommented:
Sorry I don't understand ! line 55,56,57 are all totally blank !!!
gowflow
0
 
gowflowCommented:
I need more data. Pls send or else the pgm will not work !
rgds/gowflow
0
 
bsharathAuthor Commented:
yes but
\\SRV01\Training Videos
Is still available in colum A
it needs to considers 1 set of box until Colum A data does not match with 2 rows blank after them
if needed can even consider last data in Colum D (If you want)
0
 
gowflowCommented:
ok do you hv occurences where this can happen and more than 3 blank lines ?
gowflow
0
 
bsharathAuthor Commented:
I have emailed you the actual file but will lesser data than actual

Please see if my method helps. Or you change change the code to any way that may achieve the requirment

Thank u for the patient help
0
 
gowflowCommented:
Its ok ! Pls try this version of the file and let me know if it does what you expect. Pls feel free to let me know of any change you may need. Sorry it took long but I guess we had some mis-communications

To make it run:
1) Start Empty workbook in Excel
2) Make sure your Ecel macro security is set at Medium (Tools/Macro/Security chk Medium then OK then close Excel)
3) Start MakeBorders.xls and when prompted Enable Macro
4) Press Play on Macro or (Tools/Macro/Macros Run and choose MakeBorders and run
5) Check results.

Pls advise
rgds/gowflow
MakeBorders.xls
0
 
bsharathAuthor Commented:
Thanks

It did work for 1 part i mean i set of data and then got this error
Run time error 6
Overflow
When debug goes here
 RowBlank = RowBlank + 1
0
 
gowflowCommented:
well this is why I need more data !!!
Coz what you hv in Sheet1 is not representative ! I still did not receive the file you sent
gowflow
0
 
gowflowCommented:
Well if you had sent this file ever since I asked you to do  you would have spared me much WASTED TIME !!! Sorry are you testing my intelligence here or what ? You hav ein index not the same pattern not the same sorting a total diffrent matter !!! that the small 2 lines sample you provided.

For next time, if you want a quick response give the max info so people can help you faster. Am surprised you are Master in Exchange and hv good points accumulated !!!

Anyway will work on the sample you provided and will revert.
gowflow
0
 
bsharathAuthor Commented:
Thanks...
0
 
gowflowCommented:
Here it is. Pls make sure you don't waste time of pepole helping you next time by providing full samples when requested.
gowflow

<Original file replaced with sanitized version 1/3/10 LadyModiva>

Open in new window

MakeBorders.xls
0
 
bsharathAuthor Commented:
Thanks...I am checking on it...

I have already requested moderator to remove my email id.... Will post another request for the email excel file you posted which has real names.

Can you post excel with scrambled data please...
0
 
gowflowCommented:
I'll remove the sheet from there and the Index sheet as well and keep the old sheets is it ok like this ?
Sorry for the email !
gowflow
0
 
gowflowCommented:
Here is the file with the ammended VBA as per your request. Pls chk it and let me know if all ok
rgds/gowflow
MakeBorders.xls
0
 
bsharathAuthor Commented:
Thank U
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 24
  • 21
Tackle projects and never again get stuck behind a technical roadblock.
Join Now