How to store a cell value in a variable, delete the same cell value and then paste it in another wor-CodePudding

I have to copy a cell value in a workbook (AD1 in the Riepilogo_Selezioni2.xlsm), clear it and then paste the value in the workbook where I made the macro (Presa_in_Carico.xlsm). My idea is to set a variable and then use it in the other workbook, but I searched for a solution, without success. This is my whole code:

Range("A2:B2").Select
Selection.Copy
Workbooks.Open Filename:="F:\SCN\Riepilogo_Selezioni2.xlsm"
Application.DisplayAlerts = False
Range("AD1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False
'here I want to declare AD1.value as range
Range("AE1").Select
Selection.Copy
Range("a3:a65535").Find(What:=Range("AD1").Value).Select
ActiveCell.Offset(0, 30).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=True
            
Range("AD1:AE1").Select
Selection.ClearContents

Range("A2").Select
Selection.End(xlDown).Offset(1, 0).Select

ActiveWorkbook.Save
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
'Fine Trova e compila su Selezioni2

Range("A65536").Select
Selection.End(xlUp).Offset(1, 0).Select

' here i want to paste the AD1.value
MsgBox "Richiesta registrata correttamente!"

Can anyone help me?

CodePudding user response：

Here are a few lines of code that do what you're asking for with a breakdown of what each does.

'declare variable to use
Dim storedvalue

'store value of cell in variable
storedvalue = Workbooks("Riepilogo_Selezioni2.xlsm").Worksheets("name_of_worksheet_AD1_is_on").Range("AD1").Value

'clear contents of cell AD1
Workbooks("Riepilogo_Selezioni2.xlsm").Worksheets("name_of_worksheet_AD1_is_on").Range("AD1").ClearContents

'write value of variable to another location
Workbooks("Presa_in_Carico.xlsm").Worksheets("name_of_destination_worksheet").Range("address_to_write_to").Value = storedvalue

Note: I am not using copy/paste buffer at all.

Note: Don't forget to qualify your ranges with a worksheet object - otherwise Excel has to guess which worksheet AD1 is on.

A tidier approach is to store references to the worksheets:

'declare variable to use
Dim storedvalue

'declare worksheet object references
Dim sourceSh As Worksheet
Dim destinationSh As Worksheet

'set those references to the workbook-worksheet for each
Set sourceSh = Workbooks("Riepilogo_Selezioni2.xlsm").Worksheets("name_of_worksheet_AD1_is_on")
Set destinationSh = Workbooks("Presa_in_Carico.xlsm").Worksheets("name_of_destination_worksheet")

'store value of cell in variable
storedvalue = sourceSh.Range("AD1").Value

'clear contents of cell AD1
sourceSh.Range("AD1").ClearContents

'write value of variable to another location
destinationSh.Range("address_to_write_to").Value = storedvalue