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

tigerdel

Board Regular
Joined
Oct 13, 2015
Messages
145
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,214,606
Messages
6,120,483
Members
448,967
Latest member
visheshkotha

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