VBA-Copy and paste columns under different headers

mrpengify

New Member
Joined
Mar 5, 2023
Messages
3
Office Version
  1. 2021
Platform
  1. Windows
Hi all, as a newbie, I got a copy-paste problem using VBA. And here is the snapshot of my minimum working environment.

1678069876351.png


At the end of each month, we will get data for column C and we were supposed to paste Column C data to the green area in order to get the total number.

By using a little bit of Google, what we currently have is:

VBA Code:
Sub MonthlyPaste()
    On Error Resume Next
    Dim xRg As Range
    Set xRg = Application.Selection
    Range("C6:C30").Copy Range("G6:G30")
    xRg.Select
End Sub

Notice this is the code for Month 3(M3) button since cell A3 is telling us the date. And we put 12 similar buttons under 12 columns -_-+

That’s quite dumb, isn’t it? What makes things worse is if we clicked the wrong button, the total number will be messed up.

So I am wondering if there is a way to set up one button instead of 12, and copy & paste data from column C to corresponding green columns based on Month(A3)?

Thank you in advance.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Book1
ABCDEFGHIJKLMNOP
1
2
331-Mar
4ItemsTotalThis month
5Month 1Month 2Month 3Month 4Month 5Month 6Month 7Month 8Month 9Month 10Month 11Month 12
6Item 115010050100
7Item 215220051101
8Item 315430052102
9Item 415640053103
10Item 515850054104
11Item 616060055105
Sheet1
Cell Formulas
RangeFormula
B6:B11B6=SUM(E6:P6)


With above data, use below single code:
VBA Code:
Option Explicit
Sub AddMonth()
Dim ThisMonth
ThisMonth = Range("C6:C" & Cells(Rows.Count, "A").End(xlUp).Row).Value
If IsDate(Range("A3")) Then
    Range("D6").Offset(0, Month(Range("A3"))).Resize(UBound(ThisMonth), 1).Value = ThisMonth
End If
End Sub
 
Upvote 0
Solution
That’s quite dumb, isn’t it?
Certainly not the best way in my view.

Assuming that value in A3 is a 'real' date (number) and not text then try this with a copy of your workbook.
Use just a single button for it.

VBA Code:
Sub Monthly_Paste()
  Range("C6:C30").Copy Destination:=Range("E6:P6").Cells(Month(Range("A3").Value))
End Sub
 
Upvote 0
Book1
ABCDEFGHIJKLMNOP
1
2
331-Mar
4ItemsTotalThis month
5Month 1Month 2Month 3Month 4Month 5Month 6Month 7Month 8Month 9Month 10Month 11Month 12
6Item 115010050100
7Item 215220051101
8Item 315430052102
9Item 415640053103
10Item 515850054104
11Item 616060055105
Sheet1
Cell Formulas
RangeFormula
B6:B11B6=SUM(E6:P6)


With above data, use below single code:
VBA Code:
Option Explicit
Sub AddMonth()
Dim ThisMonth
ThisMonth = Range("C6:C" & Cells(Rows.Count, "A").End(xlUp).Row).Value
If IsDate(Range("A3")) Then
    Range("D6").Offset(0, Month(Range("A3"))).Resize(UBound(ThisMonth), 1).Value = ThisMonth
End If
End Sub
Works like a magic! Thanks bebo.
 
Upvote 0
Certainly not the best way in my view.

Assuming that value in A3 is a 'real' date (number) and not text then try this with a copy of your workbook.
Use just a single button for it.

VBA Code:
Sub Monthly_Paste()
  Range("C6:C30").Copy Destination:=Range("E6:P6").Cells(Month(Range("A3").Value))
End Sub
Hi Peter, thanks a lot, that's what exactly I am looking for.

I will modify it a little bit to fit my excel sheet :)
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

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