Copy and paste

[Darren Smith]

Hi All

Can someone tell me why this code below is not working.
I think it copies ok but won't paste into merged cells.
It's copying from merged cells to merged cells in separate worksheets.

If ListBox4 = "Add Data to Jobcard Analysis Sheet" Then
   
   Dim wsSource    As Worksheet
   Dim wsDest      As Worksheet
   
   Set wsSource = ThisWorkbook.Sheets("Job Card Master")
   Set Dest = ThisWorkbook.Sheets("Job Card with Time Analysis")
   
        wsSource.Range("G2").Copy
        wsDest.Range("G2").PasteSpecial xlPasteValues
        
       
    End If
End Sub

 

[Peter_SSs]

Could it be that

Set Dest = ThisWorkbook.Sheets("Job Card with Time Analysis")

should be

Set wsDest = ThisWorkbook.Sheets("Job Card with Time Analysis")

 

[Darren Smith]

I`ve done the alteration but still no luck. Sorry a bit thick of me?

wsDest.Range("G2").PasteSpecial xlPasteValues

The code above is failing

 

[Peter_SSs]

The code above is failing

That gives us nothing to go on.

In what way is it failing?
- error message? (what is the error message?)
- pastes the right thing in the wrong place? (details?)
- pastes the wrong thing in the right place? (details?)
- does nothing?
- etc

If you have merged cells in either sheet involving cell G2, please give details of the merged cells.

 

[Darren Smith]

- error message? (what is the error message?) It says xlpastes values =-4163 which I don`t understand
- pastes the right thing in the wrong place? (details?) Does not paste any data
- pastes the wrong thing in the right place? (details?) Same as above
- does nothing? Yes that is right
- If you have merged cells in either sheet involving cell G2, please give details of the merged cells. Both sheets are G2:H2 cells merged

 

[Darren Smith]

Please can someone help starting to get a bit desperate?
The code below will not copy & paste a merged cell from one Sheet to another Sheet
Merged cells are ("G2:H3") on both sheets

If ListBox4 = "Add Data to Jobcard Analysis Sheet" Then
 
   Dim wsSource    As Worksheet
   Dim wsDest      As Worksheet
 
   Set wsSource = ThisWorkbook.Sheets("Job Card Master")
   Set wsDest = ThisWorkbook.Sheets("Job Card with Time Analysis")
 
        With Worksheets(wsSource).Range("G2").MergeCells_
        .Copy
      
        End With
          
        With Worksheets(wsDest).Range("G2")
            .MergeCells
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .PasteSpecial xlPasteValues
          
            End With
    End If
End Sub

 

[Peter_SSs]

Both sheets are G2:H2 cells merged

Merged cells are ("G2:H3") on both sheets

Please be very careful with your descriptions as all we have to go on is what you tell us so when we see contradictions like the above, it can get confusing.

Never-the-less assuming both sheets have the same cells merged, try this

If ListBox4 = "Add Data to Jobcard Analysis Sheet" Then
 
  Dim wsSource    As Worksheet
  Dim wsDest      As Worksheet

  Set wsSource = ThisWorkbook.Sheets("Job Card Master")
  Set wsDest = ThisWorkbook.Sheets("Job Card with Time Analysis")
 
  wsSource.Range("G2").MergeArea.Copy
  wsDest.Range("G2").MergeArea.PasteSpecial xlPasteValues
       
End If

 

[Darren Smith]

Thanks for your advice. It`s working fine now thank you very much

 

[Peter_SSs]

Cheers. Thanks for the follow-up.

 

[Darren Smith]

Hi sorry, me again this below is copying A13 down but does not seem to work.
Please help

If ListBox4 = "Add Data to Jobcard Analysis Sheet" Then
 
  Dim wsSource    As Worksheet
  Dim wsDest      As Worksheet

 
  Set wsSource = ThisWorkbook.Sheets("Job Card Master")
  Set wsDest = ThisWorkbook.Sheets("Job Card with Time Analysis")

 
[U]    wsSource.Range("A13" & Rows.Count).End(xlDown).Copy
   wsDest.Range("A13" & Rows.Count).End(xlDown).PasteSpecial xlPasteValues[/U]
 
    wsSource.Range("G2").MergeArea.Copy
  wsDest.Range("G2").MergeArea.PasteSpecial xlPasteValues

End Sub