Copy cell column range based on another cell value

[JLouis]

Hello again.

I have a sheet listing values on 5 separate columns in a helper column. Shown below. Depending on the cell value in N14, I would like to copy the range associated with the cell values in columns 1-5. For example, if the cell value in cell N14 is 5, I would like to copy range F15-F25 to another worksheet in the range B5-B15.

END-OF-MONTH-PROCESSES.xlsm
	C	D	E	F
6	1/25/2021	2/22/2021	3/29/2021	5/24/2021
7	2/21/2021	3/28/2021	4/25/2021	5/30/2021
8	1/25/2021	2/22/2021	3/29/2021	5/24/2021
9	2/21/2021	3/28/2021	4/25/2021	5/30/2021
10	1/25/2021	2/22/2021	3/29/2021	5/24/2021
11	2/21/2021	3/28/2021	4/25/2021	5/30/2021
12
13	2	3	4	5
14	21	21	21	21
15	234.69	-453.34	271.72	309.22
16	30.16	-70.71	77.12	-40.13
17	-113.06	23.14	37.61	72.07
18	3.02	-13.98	44.62	62.81
19	-57.86	35.78	43.80	-16.50
20	202.68	-321.26	64.16	172.49
21	-57.22	-19.56	0.25	21.11
22	-23.72	-29.92	86.64	422.46
23	-137.54	-38.75	-67.21	233.08
24	-7.88	-19.70	11.92	7.88
25	29.54	-8.57	22.86	-8.57
helper

Cell Formulas
Range	Formula
C6:F6	C6	=LEFT(C2,LEN(C2)-SEARCH("-", C2))
C7:F7	C7	=RIGHT(C2,LEN(C2)-SEARCH("-", C2))
C8:F8	C8	=LEFT(C3,LEN(C3)-SEARCH("-", C3))
C9:F9	C9	=RIGHT(C3,LEN(C3)-SEARCH("-", C3))
C10:F10	C10	=LEFT(C4,LEN(C4)-SEARCH("-", C4))
C11:F11	C11	=RIGHT(C4,LEN(C4)-SEARCH("-", C4))
C13:F13	C13	=B13+1
C14:F14	C14	=LEN(C2)
C15:F25	C15	=WORKSHEET!H5-WORKSHEET!G5

END-OF-MONTH-PROCESSES.xlsm
	B
5	309.22
6	-40.13
7	72.07
8	62.81
9	-16.50
10	172.49
11	21.11
12	422.46
13	233.08
14	7.88
15	-8.57
WORKSHEET

Cell Formulas
Range	Formula
B5:B15	B5	=K5-J5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
1:1048576	Cell	contains an error	text	NO

Thanks for looking!

 

[rollis13]

Have a try with this macro. Be aware that B15 of copied area in column 1 overlaps B15 of paste area B5:B15.

Option Explicit
Sub CopyByCriteria()
    Dim col
    col = Range("N14").Value + 1                  'detect value in N14 and adjust number to columns 1-5 (B-F)
    Range(Cells(15, col), Cells(25, col)).Copy    'copy area from your chosen column
    Range("B5").PasteSpecial Paste:=xlPasteValues 'paste values starting from B5 and down to B15
    Application.CutCopyMode = False
End Sub

 

[JLouis]

Have a try with this macro. Be aware that B15 of copied area in column 1 overlaps B15 of paste area B5:B15.

Option Explicit
Sub CopyByCriteria()
    Dim col
    col = Range("N14").Value + 1                  'detect value in N14 and adjust number to columns 1-5 (B-F)
    Range(Cells(15, col), Cells(25, col)).Copy    'copy area from your chosen column
    Range("B5").PasteSpecial Paste:=xlPasteValues 'paste values starting from B5 and down to B15
    Application.CutCopyMode = False
End Sub

Ho rollis13. That's a nifty little snippet. It doesn't fit exactly my application,(not your fault) but it looks like it can be modified to fit. I ended up recording a copy/paste macro with about 50 lines of code to get it to do what i wanted. I knew there was something a lot easier. If I get it to work I'll report back the final code.

Thanks a bunch.

 

[JLouis]

A little tweaking and it executed perfectly! Thank you for the concise code. I learned something from your post and really appreciate it.

 

[rollis13]

Glad having been of some help .
Even for sharing reasons, I wouldn't mind taking a look at your final macro just to figure out what I didn't 'guess' from your first post.

 

[JLouis]

Sure. I have 3 different entries to enter so I adjusted for that. Originally the data was on 1 sheet and the paste was on another. So I just modified the one sheet and linked to that on the other. The macro ended up looking like this:

Sub CopyByCriteria276()
Dim col
col = Range("N14").Value + 1 'detect value in N14 and adjust number to columns 1-5 (B-F)
Range(Cells(15, col), Cells(25, col)).Copy 'copy area from your chosen column
Range("N16").PasteSpecial Paste:=xlPasteValues 'paste values starting from B5 and down to B15
Application.CutCopyMode = False
End Sub

Sub CopyByCriteria397()
Dim col
col = Range("N14").Value + 1 'detect value in N14 and adjust number to columns 1-5 (B-F)
Range(Cells(32, col), Cells(42, col)).Copy 'copy area from your chosen column
Range("N29").PasteSpecial Paste:=xlPasteValues 'paste values starting from B5 and down to B15
Application.CutCopyMode = False
End Sub

Sub CopyByCriteria1829()
Dim col
col = Range("N14").Value + 1 'detect value in N14 and adjust number to columns 1-5 (B-F)
Range(Cells(49, col), Cells(59, col)).Copy 'copy area from your chosen column
Range("N42").PasteSpecial Paste:=xlPasteValues 'paste values starting from B5 and down to B15
Application.CutCopyMode = False
End Sub

And the execution code:
Sub PeriodDebitCredits()
Dim path As String
path = ThisWorkbook.path
Application.ScreenUpdating = False
On Error GoTo errorhandler2
UserForm1.Show
UnprotectMe
Sheets("helper").Select
CopyByCriteria276
CopyByCriteria397
CopyByCriteria1829

finish:
Sheets("worksheet").Select
ActiveSheet.Unprotect
Range("A1").Select
ActiveCell.FormulaR1C1 = "Debits/Credits Imported from Period:"
ProtectMe
ActiveWindow.SmallScroll Down:=-80
Range("f1").Select
ProtectMe
Exit Sub

errorhandler2:
MsgBox "ERROR"

End Sub

i know it's rudimentary but as long as it works right? I won't embarrass myself and show what I was using prior to your help, but it DID work.

Thanks again.

 

[rollis13]

Thank you.

I won't embarrass myself and show what I was using prior to your help, but it DID work.

It's okay, I believe you .