Avatar of Andreas Hermle
Andreas HermleFlag for Germany

asked on 

copy used range of a worksheet to another worksheet using VBA

Dear Experts:

I would like to run a macro on an excel worbook with the following requirements:

1. copy the used range of worksheet 'overview' to a worksheet named 'my_custom_sets'. There are filtered rows in the 'overview' worksheet as well as blank rows. Only the visible cells are to be copied.
2. if there is already data on the 'my_custom_sets' worksheet (i.e. the macro has already been previously activated) a macro has to tell the user: "there is already data on the 'my_custom_sets' worksheet! If you continue, this data will be fully overwritten. Do you really want to do this? If the user presses 'YES' the copied and pasted data replaces the current data in the 'overview' worksheet. If the user presses 'NO' the macro is to abort.

Help is very much appreciated. Thank you very much in advance.

Regards, Andreas
VBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Roy Cox
Avatar of Norie
Norie

Andreas

Can you clarify which sheet you are copying from and which sheet you are copying to?

In the first part of your question it appears you want to copy from 'overview' to 'my_custom_sets' but in the second part it appears you want to copy to 'overview'.
Avatar of Andreas Hermle

ASKER

uppps, Norie you are right, thank you for bringing this to my attention.

I have edited the initial question accordingly.

Thank you
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Sounds like you may be able to use Advanced Filter function.

Advanced Filter can be used to extract data from a data-set based on a set of criteria and copy that extracted data to another sheet.

I have also done something similar in the past using slicers. The slicers were used to apply the filters and then a routine to extract the results.
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

You would need VBA for AdvancedFilter to copy the result to another page. I've posted several example and it also depends on the data layout.
Avatar of Andreas Hermle

ASKER

Hi Rob,
thank you very much for your help. I know what you are talking about, but in my case I would like to have a macro code

Hi Roy,
thank you very much for your post. I have searched thru your posted examples but regrettably was not able to find an appropriate one. Do you think you could help me with that?

Thank you very much to both of you for your valuable help.
Avatar of Norie
Norie

Andreas

When you say this
if there is already data on the 'my_custom_sets' worksheet
do you mean if there is any data on 'my_custom_sets.
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

@Roy
You would need VBA for AdvancedFilter to copy the result to another page.
Not strictly true; before starting the Adv Filter wizard if you put the cursor on a different sheet to the data you can specify that sheet as the destination for the filtered result.

Admittedly, in order to give the option of clearing the existing data it would need VBA. The Advanced Filter will overwrite the data but only to the extent of the replacement data; if the new filter result is smaller than the previous result it will only partially overwrite the previous set.
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

As always, some sample data would be helpful. Please show the source data and the result required.
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Rob

I'm sure I used that method many years ago, but it fails for me now.

Here's a simple demo workbook
AdvancedFilter_Example.xlsm
Avatar of Andreas Hermle

ASKER

Dear all, thank you very much for the overwhelming support and I am very sorry for the delay in getting back to you. I will now see to it during the day. Thank you very much.
Avatar of Andreas Hermle

ASKER

Hi Roy,

very nice piece of coding which I could come in handy under other circumstances.

In the meantime I came up with my own solution but this is really crude coding, since I used 'selection'.

If anyone of you could amend this code and give it a professional touch by using the range object, please.

Help is very much appreciated. Thank you very much in advance.

Regards, Andreas

copy_visible_cells_to_another_sheet.xlsm
ASKER CERTIFIED SOLUTION
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Andreas Hermle

ASKER

Dear all, thank you very much for your valuable help.  I really appreciate it.

Special thanks to you Roy.

Regards, Andreas
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Pleased to help
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo