Copy data from one sheet to another with dynamic range selection

**joseaugustin** · 02-21-2011, 01:29 PM

The CRM application that we use provides output in an excel sheet "Table View 1". I want to be able to copy the data that is dumped to a sheet to another sheet. The requirements:

The data output range is different. Sometimes it is 4 rows and sometimes it is 25 rows. I want a mechanism where based on the output, the formula/macro automatically copies the information to the new sheet.
The first 2 rows need to be excluded when the copy is being made.

I am not sure if this can be accomplished using a macro or a formula, but any help will be appreciated.

I am attaching a sample of the application output with this thread.

**Paul** · 02-21-2011, 01:33 PM

Rather than copying rows, are these alternatives possible?

1) Simply delete the first two rows from the current sheet, leaving the data from rows 3:xx.

2) Create a copy of the worksheet, and on that copy delete rows 1 and 2.

If neither is an option, it is pretty simple to copy rows 3:xx to another sheet.

**joseaugustin** · 02-21-2011, 02:38 PM

Hi Paul,

I cannot make any changes to the sheet where the data is displayed as I will loose changes everytime the data refreshes. That is why I am looking for a method to copy the data onto another sheet and work on the data there.

Hence deleting the first 2 rows is not an option.

I want to do further operations on the data once the copy has been made. If I make a copy after every refresh then, it will be a tedious process. I am looking for some formula/macro that can copy the data over to the new sheet without me manually doing it everytime. Please let me know if you can think of any other option.

Thanks for the prompt response.

Regards,
Jose

Originally Posted by Paul

Rather than copying rows, are these alternatives possible?

1) Simply delete the first two rows from the current sheet, leaving the data from rows 3:xx.

2) Create a copy of the worksheet, and on that copy delete rows 1 and 2.

If neither is an option, it is pretty simple to copy rows 3:xx to another sheet.

**Paul** · 02-21-2011, 02:45 PM

Perhaps something as simple as this will suffice. Whenever you run the macro it will create a new worksheet and copy the values from A3:Dxx to that new sheet.

Please Login or Register  to view this content.

**joseaugustin** · 02-21-2011, 02:58 PM

Awesome. That does the job. Thanks a ton Paul.

Is there any beginners guide to VBA available online that you are aware of? I am interested in learning how to create VBA scripts and am looking for help.

**Paul** · 02-21-2011, 03:16 PM

At the top of the General forum there are two posts with links to useful sites including formulas, charts and vba (amongst others). I don't own any VBA books, so I can't recommend any - I just learned from participating in forums like this.

http://www.excelforum.com/excel-general/

Prior to joining in 2007 I could hardly write macros at all, but picked it up quickly from the knowledge shared here, as well as surfing the web to find solutions to my issues and, last but not least, from the Excel Help screens within Excel and the VBA editor. In programming circles I'd still probably be considered a novice, but it gets me through my usual tasks and I'm able to help others.

**Davidlau** · 07-16-2013, 05:47 AM

Hi guys,

I am new to this forum. Anyway, I need some help.

If I want to copy data from one worksheet to another dynamically, what is the best way?

Say I have a few worksheets named as "Jan13", "Feb13", etc. I have another worksheet named as "Result". At "Result", I want to be able to copy the data from the other worksheet. I need to let the user to choose which worksheet (or month) that he wanted. So, in the "Result" worksheet, I want to put in a formula that can copy the data from the correct worksheet. How can I do this? I don't want the manual way of use =Jan13!A1 as it cannot do the dynamic selection of the user input.

Can someone suggest something? Thanks.