CODEWITHSUNDEEP
vbaexcel
user7650088
user7650088

Reputation:

VBA Copy+Paste Code

I have a subset of a macro I'm writing that needs a bit of modification & help from all you geniuses.

Sheet "Regions!A6:R6 contain formulas pulling data out of a table. I want "Regions!A6:R6 copied as values, then pasted in "Temp!A1""

Would you be able to help modify this code to accomplish this end goal?

Sub Testing()

' Gets Number of Row in Regions Sheet
Sheets("Regions").Activate
LR1 = Cells(Rows.Count, "A").End(xlUp).Row


'Creates a New Worksheet and copy's the data from regions, interst the copied data into the
'New Temp Sheet and then removes the duplicates to create your list of unique items to filter on

'Creating the New WorkSheet
Sheets.Add.Name = "Temp"

'Copy the data
Sheets("Regions").Activate
Range("A6:R" & LR1).Select
Selection.Copy

'Paste the data into Temp Sheet
Sheets("Temp").Activate
Range("A1").Select
ActiveSheet.Paste

Upvotes: 0

Views: 447

Answers (2)

user3598756
user3598756

Reputation: 29421

use this

Sub Testing()
    'Creating the New WorkSheet
    Sheets.Add.Name = "Temp"

    'Copy the data
    With Sheets("Regions")
        With .Range("R6", .cells(.Rows.Count, "A").End(xlUp))
            Sheets("Temp").Range("A1").Resize(.Rows.Count, .Columns.Count).Value = .Value
        End With
    End With
End Sub

Upvotes: 1

A.S.H
A.S.H

Reputation: 29352

I'm not a genius, but I'm someone who always tries to apply good coding practices. First thing I'd get rid of those Select and Activate stuff, simplifying the code, and then I assign Value instead of using copy/paste, which will get you rid of the formulas and copy values only.

Sub Testing()
    Sheets.Add.Name = "Temp"
    With Sheets("Regions").Range("A6:R" & Sheets("Regions").Cells(Rows.Count, "A").End(xlUp).Row)
        Sheets("Temp").Range("A1").Resize(.Rows.Count, .Columns.Count).value = .value
    End With
End Sub

Upvotes: 1

Related Questions