VBA Copy data ignore formatting

[exo33]

Happy Monday everyone!

Is there a quick way to modify my code below to avoid Excel copying formatting from one sheet to another. Basically, I want to copy values only from "Sheet1" to "Template". I know how to do it using Select, Copy & Paste commands but I find my code much simpler and efficient. Thanks in advance!

Sub cpydata()Sheets("Sheet1").Select
Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Copy Worksheets("Template").Range("D8")
Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Copy Worksheets("Template").Range("H8")
'other columns to follow...


End Sub

 

[Fluff]

You can do it like

Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Copy
Worksheets("Template").Range("D8").PasteSpecial xlPasteValues

 

[mumps]

Try:

Sub cpydata()
    With Sheets("Sheet1")
        .Range("A1:A" & .Cells(Rows.Count, "A").End(xlUp).Row).Copy
        Worksheets("Template").Range("D8").PasteSpecial xlPasteValues
        .Range("B1:B" & .Cells(Rows.Count, "B").End(xlUp).Row).Copy
        Worksheets("Template").Range("H8").PasteSpecial xlPasteValues
    End With
    'other columns to follow...
End Sub

What other columns do you want to copy and where do you want to paste?

 

[MARK858]

Another option maybe...

Sub cpydata()
    With Sheets("Sheet1")
        With .Range("A1:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
            Worksheets("Template").Range("D8").Resize(.Rows.Count, .Columns.Count).Value = .Value
        End With
        With .Range("B1:B" & .Cells(Rows.Count, "B").End(xlUp).Row)
            Worksheets("Template").Range("H8").Resize(.Rows.Count, .Columns.Count).Value = .Value
        End With
        'other columns to follow...
    End With
End Sub

 

[exo33]

All of these tips will work! Thanks everyone!

Try:

Sub cpydata()
    With Sheets("Sheet1")
        .Range("A1:A" & .Cells(Rows.Count, "A").End(xlUp).Row).Copy
        Worksheets("Template").Range("D8").PasteSpecial xlPasteValues
        .Range("B1:B" & .Cells(Rows.Count, "B").End(xlUp).Row).Copy
        Worksheets("Template").Range("H8").PasteSpecial xlPasteValues
    End With
    'other columns to follow...
End Sub

What other columns do you want to copy and where do you want to paste?

I have a bunch of them. I didn't want to make my post too messy... I can use your tip and copy for the rest of my code. Thanks again!

 

[mumps]

Glad to help. Glad to help. I forgot to add this line of code:

Application.CutCopyMode = False

just above

End With

 

[exo33]

I just tested this code on the entire range and a strange thing happened. Two columns did not copy properly i.e. only 6 top values were copied to Template tab from columns L and N. There are a few blank cells but that doesn't seem to be the issue.... Any ideas? It makes no sense to me whatsoever.

    With Sheets("Sheet1")
    
        .Range("A1:A" & .Cells(Rows.Count, "A").End(xlUp).Row).Copy
        Worksheets("Template").Range("D8").PasteSpecial xlPasteValues
        .Range("B1:B" & .Cells(Rows.Count, "B").End(xlUp).Row).Copy
        Worksheets("Template").Range("H8").PasteSpecial xlPasteValues
        .Range("C1:C" & Cells(Rows.Count, "C").End(xlUp).Row).Copy
        Worksheets("Template").Range("E8").PasteSpecial xlPasteValues
        .Range("D1:D" & Cells(Rows.Count, "D").End(xlUp).Row).Copy
        Worksheets("Template").Range("F8").PasteSpecial xlPasteValues
        .Range("E1:E" & Cells(Rows.Count, "E").End(xlUp).Row).Copy
        Worksheets("Template").Range("G8").PasteSpecial xlPasteValues
        .Range("Q1:Q" & Cells(Rows.Count, "Q").End(xlUp).Row).Copy
        Worksheets("Template").Range("I8").PasteSpecial xlPasteValues
[B]        .Range("L1:L" & Cells(Rows.Count, "L").End(xlUp).Row).Copy[/B]
[B]        Worksheets("Template").Range("K8").PasteSpecial xlPasteValues[/B]
        .Range("M1:M" & Cells(Rows.Count, "M").End(xlUp).Row).Copy
        Worksheets("Template").Range("J8").PasteSpecial xlPasteValues
[B]        .Range("N1:N" & Cells(Rows.Count, "N").End(xlUp).Row).Copy[/B]
[B]        Worksheets("Template").Range("L8").PasteSpecial xlPasteValues[/B]
        .Range("O1:O" & Cells(Rows.Count, "O").End(xlUp).Row).Copy
        Worksheets("Template").Range("N8").PasteSpecial xlPasteValues
        .Range("F1:F" & Cells(Rows.Count, "F").End(xlUp).Row).Copy
        Worksheets("Template").Range("R8").PasteSpecial xlPasteValues
        .Range("H1:H" & Cells(Rows.Count, "H").End(xlUp).Row).Copy
        Worksheets("Template").Range("S8").PasteSpecial xlPasteValues
            
   Application.CutCopyMode = False
    End With


End Sub

When i change above code (for column L or N) to a more simplistic form it works properly :

Sub Macro2()    Sheets("Sheet1").Select
    Range("L1:L" & Cells(Rows.Count, "L").End(xlUp).Row).Select
    Selection.Copy
    Sheets("Template").Select
    Range("K8").Select
    ActiveSheet.Paste
End Sub

Update:

Just tested again (same code) and column N copied properly. Column L is still experiencing the same issue..... Is it just Excel going crazy?

 

[Fluff]

You are lucky that all the other columns are copying correctly.
If you look at the first two copy lines you have

.Range("A1:A" & [COLOR=#ff0000].[/COLOR]Cells(Rows.Count, "A").End(xlUp).Row).Copy

note the . infront of Cells.
That is missing from the other lines.

 

[exo33]

You are lucky that all the other columns are copying correctly.
If you look at the first two copy lines you have

.Range("A1:A" & [COLOR=#ff0000].[/COLOR]Cells(Rows.Count, "A").End(xlUp).Row).Copy

note the . infront of Cells.
That is missing from the other lines.

Duh! Thanks! This fixed the issue.

 

[Fluff]

You're welcome & thanks for the feedback.
If you're interested you can also write the code like

Sub exo33()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("A1", "D8", "B1", "H8", "C1", "E8", "D1", "F8", _
               "E1", "G8", "Q1", "I8", "L1", "K8", "M1", "J8", _
               "N1", "L8", "O1", "N8", "F1", "R8", "H1", "S8")
   
   With Sheets("sheet1")
      For i = 0 To UBound(Ary) Step 2
         .Range(Ary(i), .Cells(Rows.count, Left(Ary(i), 1)).End(xlUp)).Copy
         Worksheets("Template").Range(Ary(i + 1)).PasteSpecial xlPasteValues
      Next i
   Application.CutCopyMode = False
   End With
End Sub