LinkBack URL
About LinkBacks
I want a macro that will pull data from one master sheet based on dates into worksheets labelled by Months. Any help would be greatly appreciated. Thank you in advance. I attached a workbook for reference.
I want a macro that will pull data from one master sheet based on dates into worksheets labelled by Months. Any help would be greatly appreciated. Thank you in advance. I attached a workbook for reference.
Last edited by Ajaxs87; 04-17-2022 at 07:25 PM.
Hi Ajaxs,
If you create a pivot table with your data and use Pivot Options it might just do what you want without any VBA.
Watch this for an example:
https://www.youtube.com/watch?v=Tp0Bn3g-Z34
Here is another example of what I'm suggesting
https://www.youtube.com/watch?v=5yj1a2VWpQ4
Last edited by MarvinP; 04-15-2022 at 07:42 PM.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Thank you for your input, but the file I will be using will have empty slots that someone else will be entering additional information at a later time.
I've made progress in my goal, but need help.
I currently have the files in the worksheet (rawData) able to go to worksheet (April_2022) through a macro.
What I need help with is, when I do move the data, it doesn't go to the next blank row in the table. It goes to a row below everything in the worksheet. Any ideas how to get it into the desired area?
Thank you in advance for any tips.
Re: Pull Data to Months Worksheet from Master Worksheet
Hi,
as the UsedRange property concerns all cells formatted (easy to check via its Address property !)
so you should use instead [A4].CurrentRegion and according to its address start to read data from row #5 …
Hi Marc L!
I appreciate the advise you gave, it was definitely a step in the right direction. I am running into one problem and I think its due to the cells having borders. The rows are going to the end where the last bordered row is at, instead of the first row, that is blank in column B. Any idea how to make it work with without clearing all the borders? Thank you in advanceScreenshot 2022-04-17 121952.png
Last edited by Ajaxs87; 04-17-2022 at 01:21 PM.
Re: Pull Data to Months Worksheet from Master Worksheet
Originally Posted by Ajaxs87
Yes as yet written in my previous post …
I attempted the changes you suggested and the above photo was the results I got. I am still very new to VBA, so I might have misunderstood how to input the changes. I will post below how I did it. Thank you
Please Login or Register to view this content.
Re: Pull Data to Months Worksheet from Master Worksheet
You misread my post so some logic errors in your code but :
1) Why the April tab is 'APRIL_2022' and other month tabs have not any year in their names, seriously ?
2) According to the 'Master' tab row #2, which cells go where in April tab ?
3) Once the 'Master' rows are updated to their relative months tabs they should be deleted ?
Last edited by Marc L; 04-17-2022 at 02:34 PM.
1. April is a copy of the exactly worksheet I will be using it on. The workbook started off as practice to see if I could do this technique of using a Master sheet and moving rows through other worksheets. I didn't think to delete the old months files before asking the questions, though I can see how it could create confusion.
2. The Master sheet was practice. The only real sheets I will using is the rawData worksheet and the APRIL_2022 worksheet. That is why the macro only mentions those two worksheets.
3. Yes, on the rawData worksheet, I want it to delete the old date once it moves the data.
More or less, the information from the rawData is coming from a form submitted in another workbook. I am using the rawData worksheet to collect the data and then distribute it through the worksheet by the months.
Re: Pull Data to Months Worksheet from Master Worksheet
So weird as 'rawData' tab has no data ‼
Is it so difficult to well elaborate your need in the initial post with an attachment well reflecting it ?
We are expecting now an attachment with only the necessary sheets aka 'rawData' with data
like the famous 'APRIL_2022' tab as it is before the execution and, to avoid any surprise,
an 'Expected' tab for the result after the execution and obviously your best elaboration in order there is nothin' to guess.
If the VBA procedure must be located within the workbook so save it as Excel file binary format (.xlsb) rather than .xlsx …
With such attachment I could give it a try in order to share an one shot code,
meaning I won't amend it for all you may forget / misexplain.
Your accuracy should be the way to solve your need …
Last edited by Marc L; 04-17-2022 at 03:02 PM.
Thank you for the input. In my initial post, I was asking for help to even attempt this. The file was made for an idea of what I wanted. The secondary attachment was after research and trial and error. I had a lapse in judgment about cleaning out the old worksheets and I apologize for that confusion. Still new to this forum and left out my agenda, as I didn't want to make a too lengthy post. Thank you once again for putting efforts and time with this issue. I believe the attached is more in line with your suggestions.
Try this …
Mea culpa, culpa mia, my bad, sorry, etc …
Maybe I did not see column A so can't work neither with CurrentRegion.
According to the Macro Recorder - like any Excel beginner can operate manually ! - and
according to your last attachment where the expected result is still missing,
an one shot Excel basics VBA demonstration as a starting point you should mod for all I won't guess :
PHP Code:Sub Demo1_2022_04()
Dim L&
With Sheet24
.[_FilterDatabase].AutoFilter 2, Array("="), 7, Array(1, "4/30/2022")
L = .Cells(.Rows.Count, 2).End(xlUp).Row
If L > 4 Then
With .Range("B5:AL" & L)
.Copy Sheet5.Cells(Rows.Count, 2).End(xlUp)(2)
.EntireRow.Delete
End With
End If
.ShowAllData
End With
End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Last edited by Marc L; 04-17-2022 at 06:33 PM. Reason: typo …
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks