Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-14-2022, 02:46 AM
CemHyur CemHyur is offline Copy Data by month to table using Inputbox with Add or Delete Row Windows 10 Copy Data by month to table using Inputbox with Add or Delete Row Office 2010
Novice
Copy Data by month to table using Inputbox with Add or Delete Row
 
Join Date: Nov 2022
Posts: 7
CemHyur is on a distinguished road
Question Copy Data by month to table using Inputbox with Add or Delete Row

Hello,



Please help to copy data from my Sheet (Sheet Name: WrkingSheet) and Paste to TablebyMonth in another sheet (Sheet Name: Diff) (Book12.zip attached here). Month name will provide in Inputbox, and acording to month provided, data of that particular month will go in the Table of that Month.

I have added the code in workbook but this is pasting data in month name sheet.

I have tried code but it is paste data into sheet with month name. (copy data using inputbox based on month) I want to paste data in same sheet but different tables. And require to overwrite data if any old/other data present in that cell with add or delete row as per entries.

Please check.

Thank You.



This question also posted here:
Copy Data by month to table using Inputbox with Add or Delete Row | MrExcel Message Board
Copy Data by month to table using Inputbox with Add or Delete Row - Excel VBA / Macros - OzGrid Free Excel/VBA Help Forum
Copy Data by month to table using Inputbox with Add or Delete Row
Copy Data by month to table using Inputbox with Add or Delete Row | Chandoo.org Excel Forums - Become Awesome in Excel
Attached Files
File Type: zip Book12.zip (25.2 KB, 3 views)
Reply With Quote
  #2  
Old 11-14-2022, 06:19 AM
p45cal's Avatar
p45cal p45cal is offline Copy Data by month to table using Inputbox with Add or Delete Row Windows 10 Copy Data by month to table using Inputbox with Add or Delete Row Office 2019
Expert
 
Join Date: Apr 2014
Posts: 674
p45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud of
Default

It seems a lot of work; there is built-in functionality to take the effort out of this sort of thing. A pivot table for example (with or without slicer on the right).
If you really wanted to add columns for comments and the like you could have a short macro to copy the pivot table's values elsewhere.
2022-11-14_131545.png
Attached Files
File Type: xlsx msofficeforums49985Book12.xlsx (27.5 KB, 3 views)
Reply With Quote
  #3  
Old 11-15-2022, 12:36 AM
CemHyur CemHyur is offline Copy Data by month to table using Inputbox with Add or Delete Row Windows 10 Copy Data by month to table using Inputbox with Add or Delete Row Office 2010
Novice
Copy Data by month to table using Inputbox with Add or Delete Row
 
Join Date: Nov 2022
Posts: 7
CemHyur is on a distinguished road
Default

Thank you for Reply...


This is also good option, but don't very much about pivot Table, So please elaborate little. This is not showing Des. Value, It will show after double clicking on cell, Si i have to click on every cell of that row?


This is not updating automatic or manual, Means I have change data in Wrking Sheet it is not updating in Pivot Table. So is there any way to refresh or regenerate to do after changes made in data?


Remark and other things will type manually, so can add extra column next to table, will make any effect to table?




Thank You.
Reply With Quote
  #4  
Old 11-15-2022, 03:00 AM
p45cal's Avatar
p45cal p45cal is offline Copy Data by month to table using Inputbox with Add or Delete Row Windows 10 Copy Data by month to table using Inputbox with Add or Delete Row Office 2019
Expert
 
Join Date: Apr 2014
Posts: 674
p45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud of
Default

Quote:
Originally Posted by CemHyur View Post
This is not showing Des. Value, It will show after double clicking on cell, Si i have to click on every cell of that row?
Either: right-click on the cell you're double-clicking on, then:


2022-11-15_094254.png


Or: merely select the cell you're currently double-clicking on, then:

2022-11-15_094752.png

Quote:
Originally Posted by CemHyur View Post
This is not updating automatic or manual, Means I have change data in Wrking Sheet it is not updating in Pivot Table. So is there any way to refresh or regenerate to do after changes made in data?
Either:

2022-11-15_095141.png

Or right-click anywhere in the pivot and choose Refresh.

Quote:
Originally Posted by CemHyur View Post
Remark and other things will type manually, so can add extra column next to table, will make any effect to table?
No except that it might stop it expanding to the right if you want to add more columns to it, but more importantly, the comments will not move with rows added/removed when the source data changes and you refresh the pivot. That's why I suggested you copy/paste|Values the pivot to another location which then can't be refreshed, then you can add your comments there.
You could, instead of adding the comments to the pivot table, add them to the source data and include the comments in the pivot table.
Reply With Quote
  #5  
Old 11-15-2022, 03:41 AM
CemHyur CemHyur is offline Copy Data by month to table using Inputbox with Add or Delete Row Windows 10 Copy Data by month to table using Inputbox with Add or Delete Row Office 2010
Novice
Copy Data by month to table using Inputbox with Add or Delete Row
 
Join Date: Nov 2022
Posts: 7
CemHyur is on a distinguished road
Default

Ok... Got it...means I Have to refresh data whenever new data will be added.





Quote:
Originally Posted by p45cal View Post
it might stop it expanding to the right if you want to add more columns to it

There is no more Column Excel 3 Main Columns (Des., Date & Value) and 4 Comment Columns (Blank, Diff in Value, Remark, Blank). Except this there is no any other columns.


Data will updated (Added) Every month in Raw Sheet, only Changes (Delete Old and Start with New Data) will come in New Year.


And Also I have formula below that table that Total Value Less Diff Value, So this will stay as it is or I have to Add when refresh data?




Sorry for trouble, I know that pivot is something in excel but never use it. So i have lots of question about this.


Thank You.
Reply With Quote
  #6  
Old 11-15-2022, 04:03 AM
p45cal's Avatar
p45cal p45cal is offline Copy Data by month to table using Inputbox with Add or Delete Row Windows 10 Copy Data by month to table using Inputbox with Add or Delete Row Office 2019
Expert
 
Join Date: Apr 2014
Posts: 674
p45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud of
Default

I would put the comments with the source data, then include the comments in the pivot. See attached. The comments will remain associated (move) with the data on refreshing.

re:"Also I have formula below that table that Total Value Less Diff Value, So this will stay as it is or I have to Add when refresh data?"
I see no Diff Value anywhere! Attach a file where we can see this calculation. It's likely, that if the Diff Value is in the source data, it too can also be included in the pivot table.
Attached Files
File Type: xlsx msofficeforums49985Book12b.xlsx (27.7 KB, 3 views)
Reply With Quote
  #7  
Old 11-15-2022, 05:59 AM
CemHyur CemHyur is offline Copy Data by month to table using Inputbox with Add or Delete Row Windows 10 Copy Data by month to table using Inputbox with Add or Delete Row Office 2010
Novice
Copy Data by month to table using Inputbox with Add or Delete Row
 
Join Date: Nov 2022
Posts: 7
CemHyur is on a distinguished road
Default

Please check Diff sheet in this attached file.


I have all mention in Diff Sheet.


This is total of what I want to do.




Thank You very much for you time.
Attached Files
File Type: xlsx msofficeforums49985Book12b.xlsx (30.4 KB, 3 views)
Reply With Quote
  #8  
Old 11-15-2022, 08:35 AM
p45cal's Avatar
p45cal p45cal is offline Copy Data by month to table using Inputbox with Add or Delete Row Windows 10 Copy Data by month to table using Inputbox with Add or Delete Row Office 2019
Expert
 
Join Date: Apr 2014
Posts: 674
p45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud of
Default

If you can get your Diff values into the WrkingSheet as I have done, then the pivot table only needs to be refreshed.
You may be able to get a formula to fetch the Diff values from the other books, or a macro or a Power Query query, it all depends on where you're getting the data from.
Note that I've changed the data on the WrkingSheet to a proper Excel table (called Table1) which means that you won't have to change the pivot table's source data reference at all when updating, you just have to make sure the extent of the table covers the data you want to pivot (see the grab handle at bottom right of cell F14).
Attached Files
File Type: xlsx msofficeforums49985Book12c.xlsx (21.2 KB, 3 views)
Reply With Quote
  #9  
Old 11-17-2022, 12:14 AM
CemHyur CemHyur is offline Copy Data by month to table using Inputbox with Add or Delete Row Windows 10 Copy Data by month to table using Inputbox with Add or Delete Row Office 2010
Novice
Copy Data by month to table using Inputbox with Add or Delete Row
 
Join Date: Nov 2022
Posts: 7
CemHyur is on a distinguished road
Default

Hi p45cal
Thank you for efforts and sorry for late reply.


I have check file which you attached. But the Diff Values I have to enter manually as it need to calculate from Physical papers(Statements of 3 Banks). So I can not create fetch or automated work. So for that I'll convert this data in to Month Wise and easily update Diff value and Comments month by month and after that check that Total value and Total of Diff Value of that month.


There for I require to convert data directly into separated months, so i need not to convert and totaling to do manually in each month and every time when get new data. So if pivot table work in that way then please help me.



By the way this is great tool Pivot Table (I never use it before) which I learned from you, I'll sure use it into my presentation sheet.


Thank You.
Reply With Quote
  #10  
Old 11-19-2022, 01:55 PM
p45cal's Avatar
p45cal p45cal is offline Copy Data by month to table using Inputbox with Add or Delete Row Windows 10 Copy Data by month to table using Inputbox with Add or Delete Row Office 2019
Expert
 
Join Date: Apr 2014
Posts: 674
p45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud ofp45cal has much to be proud of
Default

All I can suggest to make entry easier is to add a column (G) to your WrkingSheet data being a copy of column B but formatted to show Month Year, and sort that table by column B. This should make it easier to place the Diff values.
Attached Files
File Type: xlsx msofficeforums49985Book12d.xlsx (22.2 KB, 2 views)
Reply With Quote
Reply

Tags
copy data by month, copy data to name ranges, copy data usinf inputbox

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy Data by month to table using Inputbox with Add or Delete Row Table Merge _ Auto Delete Blank Lines (without Data) Alex1s85 Mail Merge 7 05-04-2020 05:12 AM
Copy Data from different areas in a worksheet into a Table Josh1012 Excel Programming 2 09-20-2018 01:51 AM
copy data table from pdf to excel DIMI Excel 1 07-10-2018 11:40 PM
Copy Data by month to table using Inputbox with Add or Delete Row Delete table if cell has data So New2 This Word VBA 1 05-06-2013 09:00 PM
Copy Data by month to table using Inputbox with Add or Delete Row Copy/Paste/Delete Table & Section etc. flds Word VBA 40 07-16-2011 07:34 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:52 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft