Search : in
By :

How to copy data from sheet1 to sheet2

Last answer on Sep 10, 2009 6:55:21 am BST Deepak, on Sep 8, 2009 10:11:08 am BST 
 Report this message to moderators

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~

Configuration: Windows XP
Firefox 3.0.14

Best answers for « How to copy data from sheet1 to sheet2 » in :
[MySQL]Loading a text file into ue table Show[MySQL]Loading a text file into ue table To load a text file as follows: $ tail /home/user1/test.txt 'name1',1,9 'name2',2,3 'name3',3,54 'name4',4,2 'name5',5,9 In a table defined as follows: CREATE TABLE...
Excel – Comparing cell A1 to entire A column in Sheet 2 ShowExcel – Comparing cell A1 to entire A column in Sheet 2 Issue Solution Note Issue I have been trying to compare sheet1 A2 to sheet2 A2 through A500 and if it exists somewhere in sheet2's a col then copy that entire row to a new sheet....
Backup Outlook 2003 data ShowBackup Outlook 2003 data All Outlook 2003 data ( Emails, Agenda, Contacts, Tasks...) are in one file with .pst extension The file is in the following default folder: C:\Documents and Settings\Login\Local Settings\Application...
Download Copy Database For SQL Server ShowDescription The application is designed by Ombelt Solutions. Copy Database for SQL Server is a tool that enables you to create copy of selected tables to various different servers or databases. Simple and easy to use, the application has been awarded...
Download Data Quik ShowData Quik allows you to copy data between two or several databases. This program can quickly copy data without using any other software and lets you select the fields or part of the tables to be copied. To accomplish that, it supports the files of...
Download Excel Viewer Show
BIOS ShowIntroduction to BIOS BIOS ("Basic Input/Output System" is an essential component in computers, which is used for controlling hardware. It is a small software program, part of which is loaded in ROM (read-only memory, which cannot be modified),...
Using FTP commands ShowThe FTP protocol FTP (File Transfer Protocol) is a protocol — meaning a standard language that lets two machines communicate — used so that computers of different types (or with different operating systems) can transfer files over a...
Ask your question Reply

1

venkat1926, on Sep 8, 2009 11:18:47 am BST
  • +2

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.

   
Reply to venkat1926

2

Deepak, on Sep 8, 2009 11:28:00 am BST

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.

   
Reply to Deepak

3

venkat1926, on Sep 9, 2009 1:43:07 am BST

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.

   
Reply to venkat1926

4

Deepak, on Sep 9, 2009 9:37:39 am BST

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~

   
Reply to Deepak

5

venkat1926, on Sep 9, 2009 11:02:21 am BST

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

   
Reply to venkat1926

6

Deepak, on Sep 9, 2009 11:20:55 am BST

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~

   
Reply to Deepak

7

venkat1926, on Sep 9, 2009 11:57:59 am BST

See the definition of usedrange in excel help usedrange means only the data that are entered why should you loop.

   
Reply to venkat1926

8

 Deepak, on Sep 10, 2009 6:55:21 am BST

Thanks all and Venkat, this really worked, actually I am new so not aware about inbuit function.


Cheers~

   
Reply to Deepak
Ask your question Reply
They need your help