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

[CemHyur]

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

Microsoft Office Forums - View Single Post - Copy Data by month to table using Inputbox with Add or Delete Row

Discussion forum on Microsoft office software such as Word, Excel, Outlook, PowerPoint and Project.

www.msofficeforums.com

Attached Files

 

[Marc L]

Hello, are all the necessary tables already created or the VBA procedure should start from 'blank' ?​

​

 

[CemHyur]

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.

 

[Marc L]

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 …
​

 

[CemHyur]

Ok. All Month Range will be there, so crashing not require.

 

[CemHyur]

But if no data present in some month then there put blank cells, don't delete that month range.

 

[Marc L]

As it seems to be already solved on another forum with a more complicated oversized way than necesssary …​

​

 

[vletm]

CemHyur
Please remember this step with Cross-Posting:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• 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.

 

[CemHyur]

As it seems to be already solved on another forum with a more complicated oversized way than necesssary …​

​

Can you please share link of that forum or name of forum.

 

[CemHyur]

Got it...
It is on excel forum,
here is link, if other Members find useful:

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

Hello, Please help me 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...

www.excelforum.com

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.

 

[Marc L]

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.​

​

 

[CemHyur]

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.

 

[Marc L]

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 …​

​

 

[CemHyur]

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.




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





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.



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.

 

[CemHyur]

WorkBook Attached as per Images in Last post.

 

[Marc L]

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 :​

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 !​

​

 

[CemHyur]

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:

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....

 

[Marc L]

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 …​

​

 

[Marc L]

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 …​

​

 

[CemHyur]

Raw_Data named range which must not contain any blank row

Ok Got it...
So I have to set Range without Blanks,

initial state is blank month ranges

Can we do like this that that before launch the VBA procedure , It will Replace old Data in Tables or Clear all Tables, Is it possible?

 

[Marc L]

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 !​

​

 

[CemHyur]

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.

 

[Marc L]

As you'll have to change the layout so if the actual state well fit your need so Excel tables are not necessary …​

​

 

[CemHyur]

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.

 

[Marc L]

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 …​

​