• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy Data by month to table using Inputbox with Add or Delete Row

CemHyur

Member
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
Attached Files
 

Attachments

  • Book12.zip
    25.2 KB · Views: 7
Hello, are all the necessary tables already created or the VBA procedure should start from 'blank' ?​
 
Yes all created.

Plese check "RequireLikeThis" Sheet, Require Data as per that sheet in "Diff" Sheet.

VBA also added but it is pasting data on "MonthName" Sheet and I require to paste data in "Name Ranges" in "Diff" Sheet.
 
So the VBA procedure will crash if any missing month range !
And should be easier to code a VBA procedure creating all necessary month ranges …
 
As it seems to be already solved on another forum with a more complicated oversized way than necesssary …​
 
CemHyur
Please remember this step with Cross-Posting:
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
 
Got it...
It is on excel forum,
here is link, if other Members find useful:

But didn't understand codes,

please If you (Marc L) can help to done in some simple way, Those code seem it's creating table also, but not creating month table which have no dates. Tried to modified code but not working.
 
So - to be sure to not waste any time without any Excel table in your attachment - you do not want to create any month range​
(the easy simple way !) but just first clear the existing month ranges then update them according to the working sheet source range ?​
Hoping your workbook attachment well reflects your real workbook as my VBA demonstration should work only with your attachment​
so you will have to fit it yourself for what you misexplained / forgot and so on …​
As a reminder : the better elaboration and accordingly the better attachment, the better solution.​
 
you do not want to create any month range(the easy simple way !)
Name range will get easy i guess, but ok if without this, work will go in easy way.

I have attached book here, Please check "Diff" Sheet in this attached file. I have deleted VBA code which I had tried. So you can start with fresh if you want.
All I have mention there.
This is details that want to do.

I am very Thankful for your time and efforts.
 

Attachments

  • Book12.xlsx
    19.5 KB · Views: 4
I thought having some ways with your initial post attachment but with your last attachment that's just confusing !​
If in your last attachment the Diff worksheet is the expected result according to WrkingSheet source data​
so you must detail each step, why some source rows are missing in this result ?​
And so on … In order all is crystal clear, nothin' to guess as guessing can't be coding …​
If not, just add a worksheet for the exact expected result so without any error neither any missing item …​
 
Dear Friend,

I thought having some ways with your initial post attachment but with your last attachment that's just confusing !
Both post attachments are same, Difference is just that Initial post is just example so I have provided Apr/May/Jun Month Tables only, but in Last Attachment I added All Month Tables and End of the Last Table added some Plus Minus Formula, that's it... I thought that if data will paste into Month Table then I'll do manually Plus Minus in end of the Last Table.

If in your last attachment the Diff worksheet is the expected result according to WrkingSheet source data
so you must detail each step, why some source rows are missing in this result ?
That is just to show you that require like this, so after pasting data (3 Column only, Other Column will be Blank to add data Manually), I can add Diff Value and Comments manually from other books.

In order all is crystal clear, nothin' to guess as guessing can't be coding …
I think that you didn't get what I was trying to explain you. So here I'll explain you once again with Images. Sorry for lengthy post and please try to understand my little complicated work.

In my work, I get a Data with Date, Description and Values with 100 of lines or more, as below. (This is Raw Data on "Wrking Sheet"). Here I have taken some lines only to explanation) and I have created Button to copy Data in Tables in "Diff" Sheet.

81705


In "Diff" Sheet, I have created Blank Tables by Month (12 Tables) and added Total formula after end of the All Tables, as below.
81698
81701
81702


So above all I have/ created.

Now, from "WrkingSheet" (Raw Data) I want to copy 3 Columns in Tables of the Months in "Diff" Sheet after pressing "Cp_Data" Button, and will leave cells/Table untouched if any month have no any data, Require result like this.

81704

and
Code will count Rows in Month in "WrkingSheet" (Raw Data) and according to it, will Add or Delete Row in Tables if necessary. (This part is optional, If possible then ok else leave it.)

Hope you get all the thing what I am trying to create. I will attach WorkBook in Message (Because site won't allow above 5 attachments) as per above Images.
Please ask, If you have still any "Not Understand" or "Not Possible" part in this code.

Thank You once again for giving your valuable time and helping me in this.
 
I think that you didn't get what I was trying to explain you.
No, you just misread and did not answer to some specific questions …​
Anyway according to your both last posts a beginner starter Excel basics VBA procedure to paste only to the Sheet1 (WrkingSheet) module :​
Code:
Private Sub Cp_Data_Click()
        Dim V, L&, F&, W
    With [Raw_Data]
           .Sort .Cells(1), 1, Header:=1
            V = Evaluate(Replace("IF(#,""Diff_""&TEXT(#,""mmmm""))", "#", .Columns(1).Offset(1).Address))
        For L = 1 To UBound(V) - 1
                F = L + 1
                While V(L, 1) = V(L + 1, 1):  L = L + 1:  Wend
                W = .Rows(F & ":" & L + 1)
            With Sheet2.Range(V(L, 1)).Rows
                If UBound(W) > .Count - 2 Then .Item(3).Resize(UBound(W) - .Count + 2).Insert
               .Item(2).Resize(UBound(W), UBound(W, 2)) = W
            End With
        Next
    End With
        Application.Goto Sheet2.[A1], True
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Thank You for your efforts.
Code is working fine, Superb!!!

but after I have Add Two New Dates and Change One Date (15/07 to 15/08) in Raw Data (to check that if Duplicating date will work or not) and it is giving Error... (Workbook Attached)

Giving error in this line:
Code:
While V(L, 1) = V(L + 1, 1)

and other thing is that Which I have Change Date from 15/07 to 15/08, The entry in July Month Table is still there except delete it. (Means Raw Data have no 15/07 Date or changed), So it will update accordingly???

and last one
This will add/delete Row if Data is more/less in any month?

Sorry for the trouble....
 

Attachments

  • Book12.xlsm
    27.7 KB · Views: 3
and other thing is that Which I have Change Date from 15/07 to 15/08, The entry in July Month Table is still there except delete it. (Means Raw Data have no 15/07 Date or changed), So it will update accordingly???
Your bad 'cause according to your post #15 the initial state is blank month ranges !​
So just erase data before to launch the VBA procedure, easy just creating a named range …​
 
but after I have Add Two New Dates and Change One Date (15/07 to 15/08) in Raw Data (to check that if Duplicating date will work or not) and it is giving Error... (Workbook Attached)
Your bad 'cause you have badly modified the Raw_Data named range which must not contain any blank row !​
Just compare your both attachments …​
 
Yes, just delete the entire blank row …​
As there is no Excel table - smart way for smart worksheet - in any of your attachments …​
I asked in post #11 for deleting data month ranges but you did not answer !​
As you asked for 'a simple way' so as I wrote the easy way is to create a named range including all month data​
then add a ClearContents codeline for that named range.​
As a reminder when the initial post is at the level of what any Excel forum expects for​
the solution comes in the next post ! (So obviously you have more chance without cross posting …)​
Proceeding like you did in every Excel forums just means​
you are enough confident with your Excel / VBA skills to fit yourself any code any helper can share !​
If you are not such confident so for your next thread whatever the Excel forum​
your initial post should - must ! - contain an accurate & crystal clear elaboration so without anything to guess​
and accordingly a well representative workbook showing the before state and the exact expected result​
so far better than any picture !​
 
As there is no Excel table - smart way for smart worksheet - in any of your attachments
Sorry for that, that I misunderstood in Tables you said, I thought that Tables and Name Ranges both nearly same, So that's way I have given Name Ranges in Workbook, but now I got your point.

As a reminder when the initial post is at the level of what any Excel forum expects for the solution comes in the next post ! (So obviously you have more chance without cross posting …)
Actually this my first post, never posted anything before, So this is my fault that I didn't understand you well in my initial post. Sorry for that and Thanks for Guiding me and teaching me something new.

you are enough confident with your Excel / VBA skills
I am noob in VBA except some basic commands. I have Array Formula for the same, but this is slowing down my worksheet. So I post this to looking for VBA.

So if we convert Month from Name Ranges to Tables, then it will go Easily? So let me know, I'll Convert All Ranges to Tables and upload workbook here again.

Thank You.
 
Last edited:
As you'll have to change the layout so if the actual state well fit your need so Excel tables are not necessary …​
 
Hi Marc L,
I have made some minor changes in sheet (Changes mentioned in attached book) and added some lines for ClearContents in Code. Please check that the code (which I have entered) are ok and need not to add anything extra and It is Keeping First line Blank on Every Months Named Ranges, can do anything for it, else it is very very good and Working like charm. You made my so much work in very very Easy method.

Thank You Very Much.
 

Attachments

  • Book12.xlsm
    30.6 KB · Views: 4
Last edited:
As the VBA procedure is made on the original month ranges containing name & total rows,​
as your new month ranges do not contain them so you must update accordingly each row index within the inner With block …​
As I advised the easy way is creating a specific named range for all ranges to clear rather than changing any month range address.​
As Raw_Data range name is now useless you can delete it in the Names Manager and use Raw_Table instead …​
 
Back
Top