How do I get this VBA to paste values?

[NHagedorn]

How do I adjust this VBA to paste values?


Sub CDRCombine()
Application.ScreenUpdating = False
Dim wsCDR As Worksheet
Dim LastRowWs As Long
Dim LastRowCDR As Long
Dim StartRowCDR As Long

Set wsCDR = ThisWorkbook.Worksheets("CDR")
LastRowCDR = wsCDR.Cells(wsCDR.Rows.Count, "A").End(xlUp).Row + 1
wsCDR.Range("A2:R" & LastRowCDR).Clear

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Acct Setup" And ws.Name <> "Pres Setup" And ws.Name <> "Data" And ws.Name <> "Stuff" And ws.Name <> "Next Injection" And ws.Name <> "Pull Through" And ws.Name <> "CDR" And ws.Name <> "Acct-W-Syr-Prolia(spec)" And ws.Name <> "Pres-W-Syr-Prolia(spec)" Then
LastRowWs = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
StartRowCDR = wsCDR.Cells(wsCDR.Rows.Count, "A").End(xlUp).Row + 1 'first empty row

ws.Range("A2:R" & LastRowWs).Copy Destination:=wsCDR.Range("A" & StartRowCDR)
LastRowCDR = wsCDR.Cells(wsCDR.Rows.Count, "A").End(xlUp).Row

wsCDR.Range("E" & StartRowCDR & ":E" & LastRowCDR) = ws.Name
End If
Next
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

Try something like this:

Sub Sample()
wsCDR.Range("A" & StartRowCDR).Value = ws.Range("A2:R" & LastRowWs).Value
End Sub

 

[Fluff]

How about

wsCDR.Range("A" & StartRowCDR).Resize(LastRowWs - 1, 18).Value = Ws.Range("A2:R" & LastRowWs).Value

 

[NHagedorn]

Thank you for the quick response but I'm sorry.... I'm not sure where to insert it.

 

[NHagedorn]

Fluff .... replaced your code with the copy destination line.... works great! Thank you

Other code..... only posted 8 cells?

 

[Fluff]

Glad we could help & thanks for the feedback

 

[NHagedorn]

Fluff,

Any idea how do I adjust the code further so that it posts in A3 rather than A2? I changed the -1 to -2 but that gave me the A3 but also an additional space between the pastings of the sheets.

Thank you

 

[Fluff]

Try adding this line

StartRowCDR = wsCDR.Cells(wsCDR.Rows.Count, "A").End(xlUp).Row + 1
[COLOR=#0000ff]If StartRowCDR = 2 Then StartRowCDR = 3[/COLOR]
wsCDR.Range("A" & StartRowCDR).Resize(LastRowWs - 1, 18).Value = ws.Range("A2:R" & LastRowWs).Value