Menu
Ask a question Report

How to copy data from sheet1 to sheet2 [Solved/Closed]

- Sep 8, 2009 at 05:11 AM - Latest reply:  nisa
- May 19, 2017 at 09:32 AM
Excel Ask a question Report
Hello,
I have a worksheet, there are 2 sheets. What I need is to do is copy all the column and rows from sheet1 and paste it to sheet2. The copy button 'Macro' should not be fixed to copy only constant, it should be dynamic means if I add some more rows in sheet1 and click copy button then it should paste all the rows and column in sheet2.

Please help me to understand looping concept in VBA, I also bought Bill Jelen book VBA and Macros for MS office Excel 2007. I am try to understand as I am new to VBA


Deepak~
See more 

10 replies

Best answer
Report Respond to venkat1926
Posts
1865
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 - Sep 8, 2009 at 08:43 PM
17
Thank you
why not use a simple macro(no need for looping etc ) 

Sub test()
Worksheets("sheet1").Cells.Copy
Worksheets("sheet2").Range("a1").PasteSpecial
End Sub


first check whether this macro works.

insert a button in sheet 1 from from toollbar and assign this macro to the button.

Thank you, venkat1926 17

Something to say? Add comment

CCM has helped 1634 users this month

Report
Lakshmi - Oct 19, 2010 at 12:48 AM
nice sir
Report
nisa - May 19, 2017 at 09:32 AM
Thank you.
I have a question .i want to copy from one workbook to another workbook.how it will be done .kindly reply.
Report Respond to venkat1926
Posts
1865
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 - Sep 8, 2009 at 06:18 AM
2
Thank you
you want ot copy complete sheet1 to sheet2. It depends upon whether sheet 2 is blank or does it contain alrready some data. sp,e tomes macro may not be necessary.
plese claiify.
Report Respond to Deepak
- Sep 8, 2009 at 06:28 AM
2
Thank you
The sheet 2 is blank, I want a VBA code of abutton named 'Copy' on sheet1 once I click this it will copy alll the data from sheet1 and paste to sheet2 which is blank.
Report Respond to venkat1926
Posts
1865
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 - Sep 9, 2009 at 06:02 AM
2
Thank you
does it loop? I do not know. I thought it highlight the whole sheet and just coy ,does it take long time have you checked.
if you are intuitively unhappy about he macro try this macro 

worksheets("sheet1").usedrange.copy
worksheets("sheet2").range("a1").pastespecial
Report Respond to Deepak
- Sep 9, 2009 at 04:37 AM
1
Thank you
Its a easiest way to do this, but I think this is not a good idea because its copying all whole sheet1. Lets think in this way I have only 2 columns. I want to copy only these columns and rows which have data. So the loop should search column and row wise if its reach to empty cell (last row with no data) it exit by copying all data and paste to sheet2.



Deepak~
Report Respond to Deepak
- Sep 9, 2009 at 06:20 AM
0
Thank you
Yes it is copying whole sheet, let me try by loop myself, if I succced I will let you know or wait for others if they are alive they will respond.



Deepak~
Report Respond to venkat1926
Posts
1865
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 - Sep 9, 2009 at 06:57 AM
0
Thank you
see the definition of usedrange in excel help usedrange means only the data that are entered why should you loop.
Report Respond to Deepak
- Sep 10, 2009 at 01:55 AM
0
Thank you
Thanks all and Venkat, this really worked, actually I am new so not aware about inbuit function.


Cheers~
Ask a question