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

[RobMolyneux]

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.

 

[JLGWhiz]

See if you can use this.

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]

See if you can use this.

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]

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.

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