Auto save of data from sheet 1 to sheet 2 regularly when i run macro

[bmbalamurali]

Hi All,

Have attached my sample workbook.

What i tried is selected a range of data in sheet 1 and pasted in sheet 2, row 2

next time i enter a different data in sheet 1 in the same range and when i run the macro it needs to paste the data in sheet 2 below the data i pasted earlier. ( pasted in row 3)

each time i enter new data and that data must be stored in sheet 2 below the row i have pasted earlier.

I tried by recording macro and edited it... but failed .. please help what wrong i did in this code.

Please Login or Register  to view this content.

[daffodil11]

Please Login or Register  to view this content.

You don't even have to select Sheet2. This will instantly paste the selection to sheet 2 directly below the last line.

[bmbalamurali]

Hi daffodil,

Thanks.

Had done both happy making activity you mentioned in your signature.

Can you please explain how the above code works....

Till now i thought (xlup) means it refers the rows up and (xldown) means it refers rows down.

Also please explain ("A" & rows.count)

i'm confused.

[daffodil11]

Sure, no problem.

If you put a space after Copy, the next thing you put is the place it will copy to.


So, Selection.Copy Range("A1") will copy something and paste it in A1. In this range, we are specifically saying paste to Column A, Row 1.

Selection.Copy ActiveSheet.Next is pasting to the next sheet.

Range("A" & Rows.Count).End(xlUp).Offset(1) only declares Column A, and then counts the total numbers of row in the worksheet. There are 1.048 Million.

So right now we are at Range("A1048576").

Next, we go from A1048576 Up, and End at the last cell that's populated. In your example, it stops at A6.

Now we offset 1 row, down to A7, the blank cell and this is where it pastes to. I prefer to use this over Down, just in case there is a blank row between A1 and your data.

In your case, since A1:A6 contains no blanks you could also use: Selection.Copy ActiveSheet.Next.Range("A1").End(xlDown).Offset(1)

[bmbalamurali]

Hi daffodil,

Thanks for the explanation and it get straight to my mind...

Still i have one question? there is no code says VBA to paste what it copied, how its pasting it????

i need a modification, i tried but it not working???

instead of selecting the range and pasting, it can dirctly copy and paste a range( range is unique only)

so i tried below code, but as usual return debug... please help...

Please Login or Register  to view this content.

what is wrong in this i cant able to note????

[daffodil11]

When you are typing in the code, after you type Range.Copy if you hit Space, Excel asks for Paste destination. Excel = smart, knows you want to paste

If you go to the next line, it doesn't know you want to Paste. Then you must tell Excel that you are pasting.

These are both the same:

Option1

Please Login or Register  to view this content.

Option2

Please Login or Register  to view this content.

[bmbalamurali]

Hi Daffodil,

sorry to ask question in solved thread...

Kindly help me in this...

with above your code, after pasting the range from sheet1 to sheet2 . I want excel to come back to a range in sheet1 C5.

Please Login or Register  to view this content.

in this i f run this code staying i sheet1 it works good.. but if go to sheet2 and run.. it showing debug in below code.

Please Login or Register  to view this content.

How to solve this... What am doing wrong....


Also please help me in screenupdating = false

i tried putting this code in first and last line of the code but its not working...

[daffodil11]

Your syntax is just a little off.

Targeting sheet = Sheets("Sheet1")

Screen updating = Application.Screenupdating = False, Application.Screenupdating = True

---

Please Login or Register  to view this content.