copying and pasting multiple ranges with formatting issue in VBA

Question

Hi all: I wrote a code in VBA. Although it works, I am having some issues with the copy-paste and formatting and want to make it more optimized.

I have 3 sheets: "Launchpad", "Member_check", "Printer"

The Member check has Column A to J with 300 rows each

The **'Launchpad' has a cell value G83 **. It can be one of 3 options: Partial sister, Full Sister, Asymmetric Sister.

What I am trying to do is: if a user specify cell 'G83' on Launchpad as 'Full sister':

The macro copies a range of rows A7:J27, A78:J107 and A127:J137 from the checker and pastes it to the Printer.

My Question is:

1. How can I make this code to take care of multiple ranges at once, rather than repeating copy and paste three times.

2. Some range of cells have formulas which do not copy over and gives "#REF" symbol unless I use paste special with values, However they lose their formatting and fonts by doing so. Is their a way around this to copy the values with formatting and fonts?

    Sub PrintMembers()
   
    If Sheets("LAUNCHPAD").Cells(82, "G").Value = "NO" And Sheets("LAUNCHPAD").Cells(83, "G").Value = "" Then
    Sheets("MEMBER_CHECK").Range("A7:J74").Copy
    Sheets("PRINTER").Range("A7").PasteSpecial xlPasteFormats
    Sheets("PRINTER").Range("A7").PasteSpecial xlPasteValues
   
    ElseIf Sheets("LAUNCHPAD").Cells(83, "G").Value = "PARTIAL SISTER" Then
    Sheets("MEMBER_CHECK").Range("A7:J27").Copy
    Sheets("PRINTER").Range("A7").PasteSpecial xlPasteAllUsingSourceTheme
    Sheets("MEMBER_CHECK").Range("A78:J107").Copy
    Sheets("PRINTER").Range("A28").PasteSpecial xlPasteFormats
    Sheets("PRINTER").Range("A28").PasteSpecial xlPasteValuesAndNumberFormats
    Sheets("MEMBER_CHECK").Range("A112:H124").Copy
    Sheets("PRINTER").Range("A59").PasteSpecial xlPasteFormats
    Sheets("PRINTER").Range("A59").PasteSpecial xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
   
    ElseIf Sheets("LAUNCHPAD").Cells(82, "G").Value = "YES" And Sheets("LAUNCHPAD").Cells(83, "G").Value = "FULL SISTER" Then
    Sheets("MEMBER_CHECK").Range("A7:J27").Copy
    Sheets("PRINTER").Range("A7").PasteSpecial Paste:=xlPasteAllUsingSourceTheme
    Sheets("MEMBER_CHECK").Range("A78:J107").Copy
    Sheets("PRINTER").Range("A28").PasteSpecial Paste:=xlPasteFormats
    Sheets("PRINTER").Range("A28").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Sheets("MEMBER_CHECK").Range("A127:H137").Copy
    Sheets("PRINTER").Range("A59").PasteSpecial Paste:=xlPasteFormats
    Sheets("PRINTER").Range("A59").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
   
    ElseIf Sheets("LAUNCHPAD").Cells(82, "G").Value = "YES" And Sheets("LAUNCHPAD").Cells(83, "G").Value = "ASYMMETRIC FULL SISTER" Then
    Sheets("MEMBER_CHECK").Range("A141:J256").Copy
    Sheets("PRINTER").Range("A8").PasteSpecial xlPasteFormats
    Sheets("PRINTER").Range("A8").PasteSpecial xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
    End If
   
    End Sub
   

Answer 1

Please remember that you don't need to Select anything. Excel knows perfectly well where its cells, worksheets and workbooks are if you mention them by name and address. The last ElseIf in your code could be replaced with the code below.

 Dim Rng As Range

 With Worksheets("MEMBER_CHECK")
    Set Rng = .Range("A7:J27,A78:J107,A127:H137")
 End With
 Rng.Copy Destination:=Worksheets("PRINTER").Range("A7")
 Application.CutCopyMode = False

This would copy the 3 ranges in one operation and avoid PasteSpecial(xlPasteValues) which leaves the formats behind. In Excel 365 there is a constant xlPasteAll which I presume to also paste everything.

However, the loop For i = 80 To 80 is superfluous and all your code really does is to check the value of G82 and evaluate the result. That leaves you with a classic case to demonstrate the Select statement. Whatever is found in the cell there will be something to copy and paste. The only difference is in what it will be. Hence my suggestion below to replace your entire code.

Dim Rng As String

Select Case Worksheets("LAUNCHPAD").Cells(83, "G").Value
    Case "PARTIAL SISTER"
        Rng = "A7:J27,A78:J107,A112:H124"
    Case "FULL SISTER"
        Rng = "A7:J27,A78:J107,A127:H137"
    Case Else
        Rng = "A7:J74"
End Select

 Worksheets("MEMBER_CHECK").Range(Rng).Copy Destination:=Worksheets("PRINTER").Range("A7")
 Application.CutCopyMode = False