Excel Forum

Posted: **Mon Sep 04, 2023 12:01 pm**

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

Posted: **Tue Sep 05, 2023 3:44 pm**

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

Posted: **Tue Sep 12, 2023 7:29 pm**

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

Posted: **Wed Sep 13, 2023 6:29 am**

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")

Posted: **Wed Sep 13, 2023 7:13 pm**

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

Posted: **Thu Sep 14, 2023 2:45 am**

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

Excel Forum

copied and paste

copied and paste

Re: copied and paste

Re: copied and paste

Re: copied and paste

Re: copied and paste

Re: copied and paste