VBA for copy record to sellected 2 sheet & clear entry sheet for new record

**santosh226001** · 09-22-2012, 01:03 PM

Hello Friends
I' making a excel record entry sheet… (file enclosed). I wanna 1 VBA for my project When Entry sheet I press Y for Yes in Cell (I 21) then follwing process work through vba

1. Row 24 Copy to (record sheet)
2. Row 26 to 35 copy to Work Sheet (but between 26 to 35 copy only those rows where record show.. if row is blank don't copy that row)

Friends I wanna 1 more help for my entry sheet I wanna when in ENTRY Sheet Work Entry (YES/NO) COLUM when we choose no no then automatically clear all data from between colum B to I ( actually if it include in VBA when we press yes record move above 2 condition & in entry sheet Work Entry (Yes/No) colum all row show NO & between Colum B to I show Blank for new entry space..)

Friends if u ppl help me then I will really very grate full to u..

my file enclosed

**patel45** · 09-23-2012, 05:01 AM

try this, if works fine we can assign the macro to cell I21 change event

Please Login or Register  to view this content.

**santosh226001** · 09-23-2012, 07:19 AM

Patel45 Sir.
ur provided VBA 1st time run working as per my requirement... but

when we entry new record in entry sheet... i.e. district name file number go number road name, length senction cost etc various information & again run VBA .. then code not working as per 1st time process...
in 2nd time use VBA
copy row number 22 in place of row number 21 in record sheet

but if we not change in any record means same as previous entry.. & run vba 2nd time road copy in WORK sheet but IF we change in road name etc in 2nd time & run then not copy record as per my requirement...i.e.

1. Row 24 Copy to (record sheet)
2. Row 26 to 35 copy to Work Sheet (but between 26 to 35 copy only those rows where record show.. if row is blank don't copy that row)

hope my feedback help u change in above code..

**patel45** · 09-23-2012, 08:05 AM

Please Login or Register  to view this content.

**santosh226001** · 09-23-2012, 08:24 AM

sir in modify code CALL DELETEROW show error so i disable that function... but this modify code run 1 time as per my problem but show a error 400
after that again i run macro not working any type...

**patel45** · 09-23-2012, 10:50 AM

Maybe you missed Sub deleterows() already attached ? you need both sub, the new Sub a() and the old Sub deleterows()

**santosh226001** · 09-23-2012, 12:00 PM

Sir ur provide vba working almost as per my requirement.. but... only few following point not full fill my requirement

1. VBA copy RECORD sheet 100% as per my requirement..
2. VBA copy WORK Sheet between Row 23 to Row 32 all row.. but i wanna only those row which have data.. blank row NOT COPY TO WORK Sheet..

3. i wanna when i press Y ON CELL I19 (MODIFIED PREVIOUSLY TELL U CELL I 24) RUN VBA & ALSO COPY RECORD AS PER REQUIREMENT ... DATA BETWEEN CELL VALUE B8 TO I17 CLEAR (NOT ROW DELETE) & CELL VALUE A8 TO A17 change NO... (sir i wanna tell u in Cell value A8:a17 & F8 to F17 we use data validation commend for condition & Pre define Value)

We wanna clear value between b8 to 117 because here we want to entry new data.. if data not clear then mistakely data can move to record sheet..

I am enclosing here with again my sheet with VBA use...

**patel45** · 09-24-2012, 05:21 AM

you need both sub, the new Sub a() and the old Sub deleterows()

Please Login or Register  to view this content.

**santosh226001** · 09-24-2012, 10:54 PM

Sir ur VBA working as per my requirement but

ur VBA copy all row between Row No. 26 to 35 (but i required copy only those row which have data)

ur another macro deleterows() not delete blank row in WORK Sheet but this macro delete blank row between Row no 26 to 35 in Entry sheet... (we never want to delete any row between 26 to 35.. whether it's data or not data coz these row link with Row 8 to Row 17)

In Entry Sheet DATA BETWEEN CELL VALUE B8 TO I17 NOT CLEAR .... i wanna after copying record Cell Value a8 to a17 change automatically with NO (with Data validation YES/NO)?

(NOT ROW DELETE) & CELL VALUE A8 TO A17 change NO... (sir i wanna tell u in Cell value A8:a17 & F8 to F17 we use data validation commend for condition & Pre define Value)

in my mid 1 thing.. for copying only data row.... we have put a varaiable where we store how may Cell Value C18 (total works).. for example if total works 6 then it means only 6 row move..... .. 26 +6 = 32 means we define a condition for move row 26 to 32 ...but how can i implement this condion i don't know coz i don't know VB or VBA langauge...

u ppl already VBA expert ..but i think .. i m not clearfy .. what i m saying..