copied and paste

[sna]

Hi there!
i need your help with vba macro to copy values from closed workbook named Book1.my code did not work properly.

Code: Select all

Sub CopyData()

    Dim wb As Workbook
    Set wb = Workbooks.Open("C:\Users\Desktop\Book1.xlsx")
    
    With ThisWorkbook.Sheets("HO_TB")
        ' Copy data from "Sheet HO_ TB"
        .Range("I6:J501").Value = .Range("F6:G501").Value
        .Range("I503:J998").Value = .Range("F503:G998").Value
        
        ' Copy data from "Sheet MBR_ TB"
        Sheets("MBR_TB").Range("I6:J501").Value = wb.Sheets("USD").Range("F6:G501").Value
        Sheets("MBR_TB").Range("I503:J998").Value = wb.Sheets("KHR").Range("F6:G501").Value
        
        ' Copy data from "Sheet KHR" to "Sheet HO_TB"
        wb.Sheets("USD").Range("F6:G501").Copy Destination:=.Range("I6:J501")
        wb.Sheets("KHR").Range("F6:G501").Copy Destination:=.Range("I503:J998")
        ' Copy specific cells from "Sheet HO_TB"
        .Range("I502").Copy Destination:=.Range("I291")
        .Range("I999").Copy Destination:=.Range("I788")
        
        ' Copy values only
        Sheets("MBR_TB").Range("I6:J501").Value = Sheets("MBR_TB").Range("I6:J501").Value
        Sheets("MBR_TB").Range("I503:J998").Value = Sheets("MBR_TB").Range("I503:J998").Value
        .Range("I502").Value = .Range("I502").Value
        .Range("I999").Value = .Range("I999").Value
    End With
    
    ' Close the  workbook
    wb.Close SaveChanges:=False
End Sub                                                                                                                                                                                      

[snasui]

The example code is below.

Code: Select all

Sub CopyData()

    Dim wb As Workbook
    Set wb = Workbooks.Open("C:\Users\Desktop\Working_TB.xlsx")
    With ThisWorkbook.Sheets("HO_TB")
        ' Copy data from "Sheet HO_ TB"
        .Range("I6:J501").Value = .Range("F6:G501").Value
        .Range("I503:J998").Value = .Range("F503:G998").Value
        
        ' Copy data from "Sheet MBR_ TB"
        .Parent.Sheets("MBR_TB").Range("I6:J501").Value = wb.Sheets("USD").Range("F6:G501").Value
        .Parent.Sheets("MBR_TB").Range("I503:J998").Value = wb.Sheets("KHR").Range("F6:G501").Value
        
        ' Copy data from "Sheet KHR" to "Sheet HO_TB"
        wb.Sheets("USD").Range("F6:G501").Copy Destination:=.Range("I6:J501")
        wb.Sheets("KHR").Range("F6:G501").Copy Destination:=.Range("I503:J998")
        ' Copy specific cells from "Sheet HO_TB"
        .Range("I502").Copy Destination:=.Range("I291")
        .Range("I999").Copy Destination:=.Range("I788")
        
        ' Copy values only
        .Parent.Sheets("MBR_TB").Range("I6:J501").Value = .Parent.Sheets("MBR_TB").Range("I6:J501").Value
        .Parent.Sheets("MBR_TB").Range("I503:J998").Value = .Parent.Sheets("MBR_TB").Range("I503:J998").Value
        .Range("I502").Value = .Range("I502").Value
        .Range("I999").Value = .Range("I999").Value
    End With
    
    ' Close the "Working_TB" workbook
    wb.Close SaveChanges:=False
End Sub

[sna]

The code works but this line .Range("I502").Copy Destination:=.Range("I291")
.Range("I999").Copy Destination:=.Range("I788")
I mean cell I291=-I502
I788=-I999

[snasui]

Sorry. I don't understand what you mean.

If you need reverse destination value you can add other code like this.

.Range("I502").Copy Destination:=.Range("I291"): .range("i291") = -.range("i291")

[sna]

Sorry for not clarifying of the problem.the code you gave it copied formula from I502 I291.I502 formula is deduct one cell from another.i want to refer I502 to I291 meant that I291=-I502 .hope it is clear.thanks

[snasui]

Please attach your updated file and point to your problem again.