Copy Paste Between workbooks, Sort, Formulas Filling.

Hi
I am looking for a macro to automate my work now done manually. I use WB2 sheet "Pasteboard" to paste data from WB1, clean it up, format it, then paste into sheets 1-4, sort them and fill down formulas.

Specifically I would like the macro to:

In WB2 sheet1 and sheet2 INSERT indicated columns of records whose FROM column in WB1 match "1234" in A19 (shift cells down).
Then in sheet1 sort the inserted records per columns BCD.
Then in sheet1 copy down formula in I18 to the dotted line row, not included.
Then in sheet 2 copy down formulas i17:n17 to the dotted line row, not included.
Then in sheet 2 sort inserted records (between rows with dotted lines) per JBC
Columns G and H entries should be numbers, not numbers entered as text.

Repeat for sheet3 and sheet4, BUT for records whose FROM column in WB1 match "5678"


Note: There is nothing I can do for the structure of WB1. I get it as is. It may contain hundreds of records.

Hello,

macro "test" in WB1 L2
extra hidden sheet "blad1" in WB2L

the sort part is not done, i dont see the BDC or JBC


Kind regards
Leo

1 way is as follow

copy the compleet sheet from wb1 to wb2 sheet "PasteBoard"
now from wb2 run macro "test2"


Kind regards
Leo

copy sheet to PasteBoard with headers

for the sort part i can not help, it is working here (version 2007)
so that part i leave to someone with experiance in excel 2002

and see if other items are ok now


Kind regards and happy Xmas

Leo

best thing to do

post exemple before with formula (not text "formula") and exemple after

Kind regards
Leo

Before and after uploaded.
Check comments on every sheet.

Hope you had a nice Christmas day.

TIP (if of any help)
When I manually INSERT, to ensure that in sheets 2 and 4 J19:N19 also shift down, in "Pasteboard" I select / copy 6 empty columns to the right of usefull data. (See selection in "Pasteboard")
I insert those in sheet 1 and 2, then do the same for the other data whose "FROM" = "5678" and insert in sheets 3 and 4.

maybe it is ok now
gridded lines fixed in wb1

Kind regards
Leo

Almost...

1. Records whose "FROM" field = "1234" should be inserted in sheets 1 and 2, not 1 and 3. Records whose "FROM" field = "5678" should be inserted in sheets 3 and 4, not 2 and 4.
2. If I pick ONLY records whose "FROM" field is "1234" OR "5678", I get Run time error 1004: Application or object defined error. Sheets("sheet1").Range("A19").Resize(at, 9).Insert shift:=xlDown
The difference is that the "1234" records get inserted while the "5678" records do not.

See comments in each sheet.

I put my 2 cents in code --> .ClearFormats

some day we get there

next try


Kind regards
Leo

I am sure you 'll nail it!

The RECEIVER in all 4 sheets should be pasted as text, otherwise leading zeros may get lost or x.xxxE+11 show up.

I am loosing the bottom dotted lines when I paste more than the sample records shown in WB1, but the formulas seem to update ok.

Up to 10 or not
next try

Kind regards
Leo

Here is an issue.
Like I said before, when I insert more records, for some reason the bottom dotted lines are lost BUT ONLY for colums A through H.
In other words in sheet 2 A452:H452 are blank but I452:N452 is dotted lines.

In sheet 2 there was an array formula {=SUM(1/COUNTIF(D18:D19;D18:D19))-1} which correctly changes to {=SUM(1/COUNTIF(D18:D452;D18:D452))-1}. D18 is dotted lines, D452 is blank. Because of this the formula value turns to #DIV/0!. As soon as I put dotted lines in D452, it gives me the correct result.

What do you think?

No idea wy that happend
in this one for each sheet 1 copy order more in code for dotted line, hope that will do it


Kind regards
Leo

In this sample, dotted lines are not applied in:
Sheet1: I452
Sheet2: I452:J452
Sheet3: I165
Sheet4: I165:J165

Run it

It is a hard one, nice


Kind regards
Leo

But not hard enough for LEOOOOO!!!!!!
Bravooooo!

The only thing left is the sorting of data between the dotted lines: Sheets 1 and 3 per columns BCD, sheets 2 and 4 per columns JBC, all ascending, data contains no headings.

It can be a different macro to run after this one.

hope this time the sort works on your version
here it does

Kind regards
Leo

Unfortunately, I get error 438 --> "ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear". Does this help?

I told you what to sort BY but not exaxtly WHAT.

Sheets 1,3 sort data in columns A thtough H
Sheets 2,4 sort data in columns A through J
(Sheets 1 and 3 have no data in I and J below row 18, so I guess you can use A through J for all to reduce coding).

As you know in all 4 sheets data starts on A19.
Last row is dotted lines, so when I manually do it, I hit shift - end - down and back one row up.

Think this way it works also in Excel 2002

Kind regards
Leo

It sure does!!!
Excellent!!!
You saved me lots of key strokes!
THANK YOU VERY MUCH

P.S. Where do I start learning this?

best place to learn is here
the practise to find solutions for questions from other users is a very good school

thats the way i learn it

Kind regards
Leo

Columns G, H are coming in as text when I tried it on another machine running office 2003 in Greek.

Cannot figure out why in 2002 english are coming in as numbers but in 2003 Greek are coming in as text. I checked the decimal separator setting in tools options, is set ok as per WB1 (comma for decimal).

this tread is marked as solved, so better start a new one like i suggest before

Kind regards
Leo

Marked it Unsolved, this way we keep all in one place rather than repeating...