VBA code to copy values from a cell range into a specific range in another sheet, based on a variable...

RobMolyneux

New Member
Joined
Sep 14, 2016
Messages
18
Hi, I'm sorry for the thread title, i wish i could have conveyed what i need more succinctly, but hopefully someone can assist.

I have created a report of sales values, which is updated manually by a number of parties at the end of each week within a month, but then effectively resets at the start of the next month, and I want the previous month data to be retained and used.

I've written 6 very basic macros to copy data (which changes each time the sheet is manually updated at the end of each week) into a specific location as values, based on the pressing of a "button" - one for each of 5 possible fridays within a month, and a final one for the month end date; as below...

Sub MONTH_END_PASTE()

'Copy A Range of Data
Worksheets("Report").Range("E6:E11").Copy


'PasteSpecial Values Only
Worksheets("Report").Range("Q6").PasteSpecial Paste:=xlPasteValues


'Clear Clipboard (removes "marching ants" around your original data set)
Application.CutCopyMode = False

End Sub


What I would like to do, is to add to the "Month End" code, that it also copies this data to a table within another sheet (named "Targets") but the paste location will vary, dependent on the month in question.

I have a table for each month of the coming FY in B23:M28, where column A is the summary headings for each line, and April-18 is in column B, through to Mar-19 in Column M.

I would like, when clicking on the "Month End" button, put pull the data from range E6:E11, to be copied into the current destination cells (Q6:Q11) but also into the table within the "Targets" sheet, but how do i do this so that it pastes into the correct column for the corresponding month?

If it helps to answer the question, cell D2 of the active sheet (named "Report") holds the first day of the current month (entered as DD/MM/YYYY ; showing as MMM-YY) and Row 4 of the "Target" sheet has the first day of each month (again, as DD/MM/YYYY, showing as MMM-YY format) from B4:M4

There's a chance this is all very confusing, but hopefully you'll get enough to understand the gist of what I'm after, and if you need any clarification I'll be happy to provide it as best I can.

Thanks in advance.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
See if you can use this.

Code:
Sub MONTH_END_PASTE2()
Dim col As Long
 'Copy A Range of Data
 Worksheets("Report").Range("E6:E11").Copy
 'PasteSpecial Values Only
 Worksheets("Report").Range("Q6").PasteSpecial Paste:=xlPasteValues
 'Clear Clipboard (removes "marching ants" around your original data set)
 Application.CutCopyMode = False
    Select Case Month(Worksheets("Report").Range("D2").Value)
        Case 4: col = 2
        Case 5: col = 3
        Case 6: col = 4
        Case 7: col = 5
        Case 8: col = 6
        Case 9: col = 7
        Case 10: col = 8
        Case 11: col = 9
        Case 12: col = 10
        Case 1: col = 11
        Case 2: col = 12
        Case 3: col = 13
    End Select
Worksheets("Report").Range("E6:E11").Copy
Sheets("Targets").Cells(Rows.Count, col).End(xlUp)(2).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub
 

RobMolyneux

New Member
Joined
Sep 14, 2016
Messages
18
See if you can use this.

Code:
Sub MONTH_END_PASTE2()
Dim col As Long
 'Copy A Range of Data
 Worksheets("Report").Range("E6:E11").Copy
 'PasteSpecial Values Only
 Worksheets("Report").Range("Q6").PasteSpecial Paste:=xlPasteValues
 'Clear Clipboard (removes "marching ants" around your original data set)
 Application.CutCopyMode = False
    Select Case Month(Worksheets("Report").Range("D2").Value)
        Case 4: col = 2
        Case 5: col = 3
        Case 6: col = 4
        Case 7: col = 5
        Case 8: col = 6
        Case 9: col = 7
        Case 10: col = 8
        Case 11: col = 9
        Case 12: col = 10
        Case 1: col = 11
        Case 2: col = 12
        Case 3: col = 13
    End Select
Worksheets("Report").Range("E6:E11").Copy
Sheets("Targets").Cells(Rows.Count, col).End(xlUp)(2).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

Thanks JLGWhiz, that code seems to have almost worked, and I've managed to fix the one issue that was stopping it from working completely as i needed it to (essentially I realised it was placing the data below the last entry in the required column in the "target" sheet, but I had something else on a lower row, so have moved that and it now works as I need it to, so that's great.

One side note, though, it seems to take a very long time to run the macro (c.30 seconds); would you expect that?
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
You can try inserting these lines in the macro. I don't know if you have volatile formulas or how many rows of data you are dealing with, but both could affect the time it takes.
Code:
Sub MONTH_END_PASTE2()
Dim col As Long
[COLOR=#DAA520]Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual[/COLOR]
'Code body here
[COLOR=#DAA520]Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic[/COLOR]
End Sub
 

Forum statistics

Threads
1,148,190
Messages
5,745,246
Members
423,938
Latest member
manilaphilips

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