VBA - copy values based on month from one set of cells to another and then auto sort chronologically

psaycolo

New Member
Joined
Feb 23, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Hello all. I am looking for a formula for a button that will take the date values that are in column K and reference whether they are the current month, then copy the ones that are for the current month into cells B17:B33, and if they are the next month into cells B36:B65. These dates also have text that needs to stay with it in column I that would need to copy over to column C. Once everything is copied over, it needs to sort itself chronologically while still keeping the dates and text together. I am new to VBA and have been trying to figure this out for about 2 weeks and have honestly gotten nowhere. PLEASE HELP!
 
Ok so it runs and transfers the first date, but then the 2nd last line of code with the
VBA Code:
.Range("B17", .Cells(34, 3).End(xlUp)).Sort .Range("C17"), xlAscending, Header:=xlNo
pops and says all merged cells need to be the same size, but we aren't referencing any merged cells so I'm confused as to why it would say that.
 
Upvote 0

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
I found something that says something about using an "On Error Resume Next" to ignore the merged cells.
VBA Code:
A couple of ways todo this;
Use On Error resume next OR try and handle the
error condition eg.

Sub clearUnlockedcells()
Dim ocell As Range
Dim datarg As Range

On Error Resume Next
Set datarg = [a1].SpecialCells(xlCellTypeConstants, 23)
If Err Then Exit Sub
For Each ocell In datarg
If ocell.MergeCells = False Then
ocell.Clear
End If
Next
End Sub

Sub clearUnlockedcells_2()
Dim ocell As Range
Dim datarg As Range

On Error Resume Next
Set datarg = [a1].SpecialCells(xlCellTypeConstants, 23)
For Each ocell In datarg
ocell.Clear
Next
End Sub

I know this is for clearing unlocked cells but do you think that any of this would work?
 
Upvote 0
Completely counterintuitively whenever I get a "Next without For" error I look for a missing "End If".

Sorting has issues when there are merged cells involved. I'll often copy the data to a hidden sheet in blocks, sort it, and return it to the original location via VBA (triggered by a button).
 
Upvote 0
I know this is for clearing unlocked cells but do you think that any of this would work?

It might, but I don't usually spend a lot of time reseaching and rewriting code. I leave that up to the user. I just try to resolve the immediate issue of the OP.
 
Upvote 0
Completely counterintuitively whenever I get a "Next without For" error I look for a missing "End If".

Sorting has issues when there are merged cells involved. I'll often copy the data to a hidden sheet in blocks, sort it, and return it to the original location via VBA (triggered by a button).
Can you put the cells exactly in the cells that you want them to go in?
 
Upvote 0
It might, but I don't usually spend a lot of time reseaching and rewriting code. I leave that up to the user. I just try to resolve the immediate issue of the OP.
I really appreciate your help. I'll play with it and try to figure out the rest. Thank you very much
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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
Back
Top