• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

How to Copy filtered subtotal to another sheet|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / How to Copy filtered subtotal to another sheet|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosHow to Copy filtered subtotal to an…
sp_PrintTopic sp_TopicIcon
How to Copy filtered subtotal to another sheet
Avatar
Andy KA
Member
Members
Level 0
Forum Posts: 16
Member Since:
February 15, 2021
sp_UserOfflineSmall Offline
1
September 21, 2021 - 7:05 pm
sp_Permalink sp_Print

Hi VBA Experts.

I am filtering column B based on two colors which than populates subtotal of each columns.

In column B I am filtering via Grey and Dark grey color and each time I change the filter the values at range V4:AG4 changes based on filter via color in column B.

All I want is to copy and paste the Results from range V4:AG4 to another sheet at B7 for grey color and B10 for Dark grey color.

Please see the attached.

Thank you

Andy

Avatar
Jessica Stewart
Northern USA
Member
Members


Trusted Members
Level 0
Forum Posts: 202
Member Since:
February 13, 2021
sp_UserOfflineSmall Offline
2
September 22, 2021 - 12:07 am
sp_Permalink sp_Print

Hi Andy, I found this video to help from Mr Excel. You should be able to adapt it to fit your needs. Hope it helps! 🙂

Avatar
Andy KA
Member
Members
Level 0
Forum Posts: 16
Member Since:
February 15, 2021
sp_UserOfflineSmall Offline
3
September 23, 2021 - 1:25 am
sp_Permalink sp_Print

Hi JESSICA

Thank you ever so much for the clip. However, I don't wants to extract any list. For this I could use index match but what I want is range of data from V4:AG4 which changes as I change the filter and I want both values to be copied over to another sheet. For eg: If i apply grey color filter values changes at range V4:AG4 and if I change filter to dark grey the values at above range changes again and I want both values copy paste to another sheet.

Any advise! Solution!

Thank you

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
4
September 23, 2021 - 7:24 am
sp_Permalink sp_Print sp_EditHistory

Hello,

Have you tried to record a macro? It is good start to get the code created by Excel for each step.

How to record a macro in Excel

Br,
Anders

Avatar
Andy KA
Member
Members
Level 0
Forum Posts: 16
Member Since:
February 15, 2021
sp_UserOfflineSmall Offline
5
September 24, 2021 - 3:49 am
sp_Permalink sp_Print

Hi Anders

Thank you ever so much it worked however, it works when both workbooks are open.

Is there a way to tidy up the code below and make the macro work file another workbook is closed.

Sub Transfertoanotherworkbook()
'
' Transfertoanotherworkbook Macro
'

'
ActiveSheet.Range("$B$8:$CG$1570").AutoFilter Field:=1, Criteria1:=RGB(211 _
, 245, 248), Operator:=xlFilterCellColor
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
Range("X4:AI4").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-51
Windows("AMP7 Capex Aug 2021 WD4.xlsx").Activate
End Sub
Sub Amit()
'
' Amit Macro
'

'
ActiveSheet.Range("$B$8:$CG$1570").AutoFilter Field:=1, Criteria1:=RGB(211 _
, 245, 248), Operator:=xlFilterCellColor
Range("X4:AI4").Select
Selection.Copy
Windows("Major Projects MPR DATA FOR SLIDES aug.xlsb.xlsx").Activate
Range("D39").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("AMP7 Capex Aug 2021 WD4.xlsx").Activate
ActiveSheet.Range("$B$8:$CG$1570").AutoFilter Field:=1, Criteria1:=RGB(128 _
, 128, 128), Operator:=xlFilterCellColor
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
Range("X4:AI4").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Major Projects MPR DATA FOR SLIDES aug.xlsb.xlsx").Activate
Range("D19").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 43
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 46
Windows("AMP7 Capex Aug 2021 WD4.xlsx").Activate
End Sub

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
6
September 25, 2021 - 3:31 am
sp_Permalink sp_Print sp_EditHistory

Hello Andy,

I am no coder, but what you are trying to do requires only basic knowledge of VBA. I do think you want to spend some time and learn the basics by taking a course, at least search for related information that is out there in this space we call Internet.

Articles worth to read —> https://www.myonlinetraininghu...../excel-vba

Have a great weekend!

/Anders

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online:
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Naomi Rumble
Uwe von Gostomski
Jonathan Jones
drsven
Forum Stats:
Groups: 3
Forums: 24
Topics: 6212
Posts: 27236

 

Member Stats:
Guest Posters: 49
Members: 31888
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.