Compile data from columns on different sheets into a column in a separate sheet (VBA)

Allan91

New Member
Joined
Dec 17, 2020
Messages
33
Office Version
  1. 2019
Platform
  1. Windows
Hi Guys,

Absolute VBA beginner here. I have been researching to find out how to do the following but to no avail.

I have created a book with 12 months which has columns containing data.

Please see snapshots I have added. New entries are being added to these lists via index match formulas (Figure 1).

I have been trying to write a VBA code with a command button in order to stack all of the respective data and update the table whenever a new entry is made in a separate sheet (i.e. all months' items in C:C and all months' expenses in D:D in 2 separate columns) and with their relative months in the next column (Figure 2). I need this to create a compiled raw data for my dynamic charts. I have been playing with if statements where I have failed horribly, I mean what I have tried is not even worth to share here unfortunately. I need to have each respective column pasted and with no spaces in between.

This is my first time posting here, I hope I have explained what I have been trying to do clearly enough. I honestly don't know how hard to write a code this requires but I'd be over the moon if you guys could help me with this task.
 

Attachments

  • Mr. Excel Query.PNG
    Mr. Excel Query.PNG
    27.6 KB · Views: 5
  • Mr. Excel Query 2.PNG
    Mr. Excel Query 2.PNG
    24.1 KB · Views: 5

Allan91

New Member
Joined
Dec 17, 2020
Messages
33
Office Version
  1. 2019
Platform
  1. Windows
Can you post an example of the formulas in F and G?
I have been trying to fix the problems since but could not. Yet 2 unlikely solutions came to mind. Maybe you could devise something out of these.

1. Maybe pasting the cells with paste special xlpastevalues could help?
2. Or trying to embed "isblank" somehow, as yes, there are formulas but I'm using "" within them so the cells are essentially blank

Maybe this could help?
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
1 Not sure that would work.

2 Or that.

I do have an idea though but don't have time right now to look into it.

I'll have a look tomorrow and, hopefully, post some code.
 

Allan91

New Member
Joined
Dec 17, 2020
Messages
33
Office Version
  1. 2019
Platform
  1. Windows
1 Not sure that would work.

2 Or that.

I do have an idea though but don't have time right now to look into it.

I'll have a look tomorrow and, hopefully, post some code.
I'd love that. I'll probably stay awake till morning to despairingly solve it although quite unlikely. Thank you very much in any case.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,261
Messages
5,595,126
Members
413,971
Latest member
User786

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top