Copying data from one sheet and pasting into different rows/columns in another sheet

[Palmyra]

I am sure this is super simple but I can't get my code to work and the site I was getting help from mentioned doing it this way but it just bugs. My coding abilities are minor. I had this set up originally with (for example) the Output cell (J8)=Input!M7 but I have a macro on my input tab to insert/delete rows and then it screws up my formulas on the output tab.

I want to copy data from a range on one sheet and paste to another sheet but in a different range. I used the below which works for the first two lines of copy and paste (in blue) because I want the data from the first sheet to the same cells on the second sheet but then the next copy paste I need them to be pasted into a different range and the red is where is bugs out.

Sub Copy_Paste()
Sheets("Input").Range("D7:I42").Copy
Sheets("Output").Paste

Sheets("Input").Range("M7:M42").Copy
Sheets("Output").Range("J8:J43").Paste

End Sub

Is it ok to just list them one after the next? or is there something that needs to go in between.

 

[Osvaldo Palmeiro]

Try:

Sheets("Input").Range("M7:M42").Copy Sheets("Output").Range("J8:J43")

or just

Sheets("Input").Range("M7:M42").Copy Sheets("Output").Range("J8")

 

[Palmyra]

That worked! Thank you!

 

[Woosha]

Try:

Sheets("Input").Range("M7:M42").Copy Sheets("Output").Range("J8:J43")

or just

Sheets("Input").Range("M7:M42").Copy Sheets("Output").Range("J8")

First of all many thanks for the code was spot on.

Was wondering how this code can be changed so when you copy and paste columns A-D it won't update rows that already have data in the destination worksheet.

Sheets("Patients").Range("A:B:C:D").copy Sheets("Summary").Range("A:B:C:D")
[CODE]

 

[Osvaldo Palmeiro]

Was wondering how this code can be changed so when you copy and paste columns A-D it won't update rows that already have data in the destination worksheet.

Try this code:

Sub ReplicateToBlanks()
 Dim BlkCel As Range, lgCol As Long, LR As Long
  For lgCol = 1 To 4
   With Sheets("Summary")
   LR = .Cells(Rows.Count, lgCol).End(3).Row
    For Each BlkCel In .Range(.Cells(1, lgCol), .Cells(LR, lgCol)).SpecialCells(xlCellTypeBlanks)
     BlkCel.Value = Sheets("Patients").Cells(BlkCel.Row, lgCol)
    Next BlkCel
   End With
  Next lgCol
End Sub

 

[Palmyra]

Another question on this - I know there has to be a better way to write this? It works for about the first 6 and then bugs. I also need it to paste just the values and not formulas. I tried a ".PasteSpecial xlPasteValues" at the end of each line but it did not like it...

Sub Copy_Paste()
Sheets("Input").Range("D7:I42").Copy Sheets("Output")
Sheets("Input").Range("M7:M42").Copy Sheets("Output").Range("J8:J43")
Sheets("Input").Range("O7:O42").Copy Sheets("Output").Range("K8:K43")
Sheets("Input").Range("Q7:Q42").Copy Sheets("Output").Range("L8:L43")
Sheets("Input").Range("S7:S42").Copy Sheets("Output").Range("M8:M43")
Sheets("Input").Range("U7:U42").Copy Sheets("Output").Range("N8:N43")
Sheets("Input").Range("AJ7:AJ42").Copy Sheets("Output").Range("O8:O43")
Sheets("Input").Range("AK7:AK42").Copy Sheets("Output").Range("P8:P43")
Sheets("Input").Range("Z7:Z42").Copy Sheets("Output").Range("Q8:Q43")
Sheets("Input").Range("AP7:AP42").Copy Sheets("Output").Range("R8:R43")
Sheets("Input").Range("AB7:AB42").Copy Sheets("Output").Range("S8:S43")
Sheets("Input").Range("AR7:AR42").Copy Sheets("Output").Range("T8:T43")
End Sub

thanks,

 

[Osvaldo Palmeiro]

Another question on this ...

Try:

Sub ReplicateValues()
 Dim itm, lngCol As Long
  lngCol = 9
   For Each itm In Array("D7:D42", "M7:M42", "O7:O42", "Q7:Q42", "S7:S42", "U7:U42", _
    "AJ7:AJ42", "AK7:AK42", "Z7:Z42", "AP7:AP42", "AB7:AB42", "AR7:AR42")
    Sheets("Output").Cells(8, lngCol).Resize(36).Value = Sheets("Input").Range(itm).Value
    lngCol = lngCol + 1
   Next itm
End Sub