macro to copy data from sheet3to sheet4 using paste special values

[howard]

I have the following code which I have written to copy data from sheet3 to Sheet 4 using paste special values


I get invalid qualified when running the macro

 PasteSpecial Paste:=xlPasteValues.Sheet(4).Range("A1")

It would be appreciated if someone could amend me code

 Sub Copy_raw_JNLDATa()
Dim lr As Long
  Sheets(4).UsedRange.ClearContents
  
  
 
  With Sheets(3)
  
    lr = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("A1:I" & lr).Copy
    PasteSpecial Paste:=xlPasteValues.Sheet(4).Range("A1")
      End With
  Sheets(4).UsedRange.EntireColumn.AutoFit
  
End Sub

 

[jmacleary]

Try:

PasteSpecial Paste:=xlPasteValues.Sheet[COLOR=#ff0000]s[/COLOR](4).Range("A1")

 

[howard]

Thanks for I get "compile error-Invalid qualifier" when using code below

 PasteSpecial Paste:=xlPasteValues.Sheets(4).Range("A1")

 

[vcoolio]

Hi Howard,

Just a little change around should do it:-

Sub Copy_raw_JNLDATa()
 
Dim lr As Long

Sheets(4).UsedRange.ClearContents
  
With Sheets(3)
      lr = .Range("A" & .Rows.Count).End(xlUp).Row
      .Range("A1:I" & lr).Copy
     [COLOR=#ff0000] Sheets(4).Range("A1")[/COLOR].PasteSpecial Paste:=xlPasteValues
End With
      
Sheets(4).UsedRange.Columns.AutoFit
  
End Sub

Cheerio,
vcoolio.

 

[howard]

Thanks for the help

 

[vcoolio]

You're welcome Howard.
Glad we could help.

Cheerio,
vcoolio.