Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.
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
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
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
uppps, Norie you are right, thank you for bringing this to my attention.
I have edited the initial question accordingly.
Thank you
I have edited the initial question accordingly.
Thank you
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.
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.
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.
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.
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.
Andreas
When you say this
When you say this
if there is already data on the 'my_custom_sets' worksheetdo you mean if there is any data on 'my_custom_sets.
@Roy
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.
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.
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
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
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.
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
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
I'm not clear what you are doing. You seem to just be copying the contents of sheet 1 to sheet 2. If that's correct then why not simply copy the sheet?
I've tidied your code
I've tidied your code
Sub copy_visible_cells()
Dim Wss As Worksheet, Wst As Worksheet
Dim pRng As Range
Dim i As Integer
Set Wss = ThisWorkbook.Worksheets("overview")
Set Wst = ThisWorkbook.Worksheets("my_custom_sets")
'Wss.UsedRange.Copy Destinaton:=Wst("A1").Paste
Wst.Cells.Clear
Wss.UsedRange.SpecialCells(xlCellTypeVisible).Copy
Wst.Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = True
End Sub
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
Dear all, thank you very much for your valuable help. I really appreciate it.
Special thanks to you Roy.
Regards, Andreas
Special thanks to you Roy.
Regards, Andreas
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA
From novice to tech pro — start learning today.
-
Microsoft ApplicationsCertification: IC3 GS4 Internet Core Competency Global Standard 4… 135 lessons
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
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'.