Copy & Paste as Values if criteria in another cell is met

tigerdel

Board Regular
Joined
Oct 13, 2015
Messages
101
Office Version
  1. 365
Platform
  1. Windows
Good afternoon experts

What I am trying to achieve is:

Sheet named Period Summary cell C11 contains the period [1, 2, 3 or 4]

I want to copy cells: H13:H26 and L15 & M15 in Period Summary
I then it to open the tab YTD Summary and paste as values as follows:

If C11 = 1 then H13:H26 paste as value in H13:H26 and L15 paste as values in H30 and M15 paste as values in H31
If C11 = 1 then H13:H26 paste as value in I13:I26 and L15 paste as values in I30 and M15 paste as values in I31
If C11 = 1 then H13:H26 paste as value in J13:J26 and L15 paste as values in J30 and M15 paste as values in J31
If C11 = 1 then H13:H26 paste as value in K13:K26 and L15 paste as values in K30 and M15 paste as values in K31

Images of the 2 sheets attached

For the life of me I can't get this to happen

Your help would be greatly appreciated
Period Summary.png
YTD Summary.jpg
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

VBAPyro

New Member
Joined
May 4, 2020
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
you basically have this written out already
here is an example of the first item

VBA Code:
sub pastedata

If worksheets("Period Summary").range("C11").value = 1 then 
worksheets("YTD Summary").range("H13:H26").value =  worksheets("Period Summary").range("H13:H26").value
worksheets("YTD Summary").range("H30","M15").value = worksheets("Period Summary").range("L15").value
worksheets("YTD Summary").range("H31").value = worksheets("Period Summary").range("M15").value
end if

end sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,470
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub tigerdel()
   Dim YTD As Worksheet
   Dim c As Long
   
   Set YTD = Sheets("YTD Summary")
   With Sheets("Period Summary")
      c = .Range("C11") + 7
      YTD.Cells(13, c).Resize(14).Value = .Range("H13:H26").Value
      YTD.Cells(30, c).Resize(14).Value = .Range("L15").Value
      YTD.Cells(31, c).Resize(14).Value = .Range("M15").Value
   End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,450
Messages
5,624,831
Members
416,058
Latest member
SEJTurner

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