Code to cut cell from one workbook, close same workbook, and paste in different workbook

Thanks but how do I incorporate your code into mine?

Thanks for your response.
I'm not sure why but when the code gets up to the part of pasting the cut cells there is nothing on the clipboard to be pasted. Looks like once the code saves the workbook all data on the clipboard is erased if we use 'cut and paste', however, the code will work if we use 'copy and paste' but I am trying to avoid copy and paste. Any other suggestions?

Thanks!

Here is the full code.
Let me know if you want me to explain in detail what the code does.
Please note that the active workbook is called "CUSTOMER DATABASE.xlsm"

Sub Button1_Click()
'
' Button1_Click Macro

Sheets("Pro-Forma Invoice").Select

ActiveSheet.Copy ' Copies active sheet to a new workbook
ActiveWorkbook.SaveAs Filename:="" & Range("H10") & Format$(Date, "YYYY-MM-DD") & " PF INV " & Range("H13").Value & " " & Range("H11").Value & ".xlsm", FileFormat:=52

Sheets("Pro-Forma Invoice").Select

Dim ws As Worksheet:    Set ws = Sheets("Pro-Forma Invoice")
Dim LR As Long, rCell As Long

ws.Range("A1:Z100").Value = ws.Range("A1:Z100").Value

ActiveWorkbook.Save

ActiveSheet.Hyperlinks.Add Anchor:=ActiveSheet.Range("H14"), Address:=ActiveWorkbook.FullName, TextToDisplay:=ActiveWorkbook.Name
    Dim HL As Hyperlink
    For Each HL In ActiveSheet.Hyperlinks
        HL.Range.Offset(-13, 0).Value = HL.Address
    Next

Range("H1").Select
    Selection.Cut
    ActiveWorkbook.Save
    ActiveWindow.Close
    Windows("ORDERS 2014.xlsm").Activate
    Cells(Selection.Cells(1).Row, "O").Select
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
    DisplayAsIcon:=False, NoHTMLFormatting:=True


ActiveCell.Offset(0, 100).Select
ActiveCell.FormulaR1C1 = "=Last2words(RC[-100])"


With ActiveCell
    'Remove last 5 characters
    .Value = Left(.Text, Len(.Text) - 5)
End With


With ActiveCell
  ActiveSheet.Hyperlinks.Add Anchor:=.Offset(0, -100), Address:=.Offset(0, -100), TextToDisplay:=.Value
End With

ActiveCell.Select
Selection.ClearContents

ActiveCell.Offset(0, -100).Select

ActiveWorkbook.Save

Windows("CUSTOMER DATABASE.xlsm").Activate
Sheets("Start").Select
        
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[26]C[3]+1"
    Range("A1").Select
    Selection.Copy
    Range("D27").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = ""
    
    Range("B36:Z36").Select
    Selection.ClearContents
    Range("D27").Select

ActiveWorkbook.Save

End Sub

I'm having issues with highlighted line.

Sub Button1_Click() 
Dim ws As Worksheet, wa As Workbook, wh As Worksheet
Dim LR As Long, rCell As Long, HL As Hyperlink
Set ws = Sheets("Pro-Forma Invoice"): ws.Copy ' Copies active sheet to a new workbook

ActiveWorkbook.SaveAs FileName:="" & _
Range("H10") & Format$(Date, "YYYY-MM-DD") & " PF INV " & _
Range("H13").Value & " " & Range("H11").Value & ".xlsm", FileFormat:=52
                Set wa = ActiveWorkbook: Set wh = ActiveSheet
wh.Hyperlinks.Add Anchor:=wht.Range("H14"), Address:=wa.FullName, TextToDisplay:=wa.Name
   
    For Each HL In wh.Hyperlinks
        HL.Range.Offset(-13, 0).Value = HL.Address
    Next


    wh.Range("H1").Cut
    Windows("ORDERS 2014.xlsm").Activate    
    Cells(Selection.Cells(1).row, "O").Select
    ActiveSheet.PasteSpecial Format:="Unicode Text", link:=False, _
    DisplayAsIcon:=False, NoHTMLFormatting:=True
                        wa.Close True

ActiveCell.Offset(0, 100).Select
ActiveCell.FormulaR1C1 = "=Last2words(RC[-100])"

With ActiveCell
    'Remove last 5 characters
    .Value = Left(.text, Len(.text) - 5)
End With

With ActiveCell
  ActiveSheet.Hyperlinks.Add Anchor:=.Offset(0, -100), _
  Address:=.Offset(0, -100), TextToDisplay:=.Value
End With

ActiveCell.ClearContents

ActiveCell.Offset(0, -100).Select

ActiveWorkbook.Save

Windows("CUSTOMER DATABASE.xlsm").Activate
Sheets("Start").Select
        
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[26]C[3]+1"
    Range("A1").Select
    Selection.Copy
    Range("D27").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = ""
    
    Range("B36:Z36").Select
    Selection.ClearContents
    Range("D27").Select

ActiveWorkbook.Save

End Sub

Hi xladept. Thanks again for your response. it seems that we are back to square one, the same 2 rows got highlighted by excel - see font in red.

Hope to hear from you again

Sub Button1_Click()
Dim ws As Worksheet, wa As Workbook, wh As Worksheet
Dim LR As Long, rCell As Long, HL As Hyperlink
Set ws = Sheets("Pro-Forma Invoice"): ws.Copy ' Copies active sheet to a new workbook

ActiveWorkbook.SaveAs Filename:="" & _
Range("H10") & Format$(Date, "YYYY-MM-DD") & " PF INV " & _
Range("H13").Value & " " & Range("H11").Value & ".xlsm", FileFormat:=52
                Set wa = ActiveWorkbook: Set wh = ActiveSheet
wh.Hyperlinks.Add Anchor:=wh.Range("H14"), Address:=wa.FullName, TextToDisplay:=wa.Name
   
    For Each HL In wh.Hyperlinks
        HL.Range.Offset(-13, 0).Value = HL.Address
    Next


    wh.Range("H1").Cut
    Windows("ORDERS 2014.xlsm").Activate
    Cells(Selection.Cells(1).Row, "O").Select
    ActiveSheet.PasteSpecial Format:="Unicode Text", link:=False, _
    DisplayAsIcon:=False, NoHTMLFormatting:=True
                        wa.Close True

ActiveCell.Offset(0, 100).Select
ActiveCell.FormulaR1C1 = "=Last2words(RC[-100])"

With ActiveCell
    'Remove last 5 characters
    .Value = Left(.Text, Len(.Text) - 5)
End With

With ActiveCell
  ActiveSheet.Hyperlinks.Add Anchor:=.Offset(0, -100), _
  Address:=.Offset(0, -100), TextToDisplay:=.Value
End With

ActiveCell.ClearContents

ActiveCell.Offset(0, -100).Select

ActiveWorkbook.Save

Windows("CUSTOMER DATABASE.xlsm").Activate
Sheets("Start").Select
        
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[26]C[3]+1"
    Range("A1").Select
    Selection.Copy
    Range("D27").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = ""
    
    Range("B36:Z36").Select
    Selection.ClearContents
    Range("D27").Select

ActiveWorkbook.Save

End Sub

How about going a different direction.
I tried recording the process but it didn't work.
Here is what I changed, instead of cutting I will now resort to copying, however the code is still not working. Can you see if you can modify it for me?

Range("H16").Select
Selection.Copy
ActiveWorkbook.Save
ActiveWindow.Close
Windows("ORDERS 2014.xlsm").Activate
Cells(Selection.Cells(1).Row, "O").Select
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False, NoHTMLFormatting:=True

Thanks.

Originally Posted by kosherboy

How about going a different direction.
I tried recording the process but it didn't work.
Here is what I changed, instead of cutting I will now resort to copying, however the code is still not working. Can you see if you can modify it for me?

Range("H16").Select
Selection.Copy
ActiveWorkbook.Save
ActiveWindow.Close
Windows("ORDERS 2014.xlsm").Activate
Cells(Selection.Cells(1).Row, "O").Select
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False, NoHTMLFormatting:=True

Thanks.

I'm wondering why you are activating a Windows object rather than the specific worksheet.
You have to activate the window to do things like remove gridlines or set the zoom level, but for specificity in moving data, you need
to direct things to the particular worksheet(s) involved.

Because i'm working with 3 different workbooks and I thought the only way to select it is by activating it?

If I recall correctly, you don't need to actually select or activate in order to use either the Copy method with a target parameter, or the PasteSpecial Method.

But you do need to provide specificity.

Something like this:

set RangeToCopyFrom = workbooks("ORDERS 2014.xlsm").Worksheets("The name of this worksheet").range("H16")
RangeToCopyFrom.Copy
set RangeToPasteInto = workbooks("Some other workbook").Worksheets("The name of that worksheet").range("O16")
RangeToPasteInto.PasteSpecial Paste:=xlPasteValues

You can keep all workbooks open while doing this (which speeds it up a bit) and close them afterward.
The main thing is to understand your PasteSpecial parameters (so you don't end up transferring format info or
formulas, if you don't want them) and to be very specific about where you want to grab data from and where
you want it to go. You can do it without using "Select" or "Activate", and you can also turn Screenupdating" off,
using:

Application.ScreenUpdating = False

When you stated you have to have several files open at once, I assumed you'd need a loop to go through them all.
This looks like you are really only processing them one at a time, based on a button click.

You have several minor problems I can't take time to address, but it starts with reading what I wrote about activating a "window" object, versus getting more specific with your worksheet identifiers. Relying on a window object is not as direct as you need to make sure you can rely on the code (Workbooks have Windows, not the other way around.)

But right where you highlighted the code, you should insert the PasteSpecial to ThisWorkbook that I showed you, but use a Clear on the range first.

Then you can close your workbook like you planned. When you are ready to use that data again, you have to do another Copy method to get it from
ThisWorkbook.

I see no reason why you have to close the workbook before finishing your Copy/PasteSpecial, however. Unless I'm missing something, you can close it after you've finished with that part of the code. In that case, you shouldn't need to store the cell in ThisWorkbook, and your Clipboard should still have data when you need it.

And by the way, the clipboard behavior in Excel differs from other Microsoft Applications.
Here's an article describing why

That is why using an intermediate store is sometimes necessary.

One other thing: I used the word "specificity". The usual term is "fully qualified". Try searching the help for "Avoiding Naming Conflicts" or "fully qualified range" to see what I mean.

Sorry I can't do more!

Can you please incorporate it into the full code?
Sorry I keep asking but i'm still new to VBA and a lot of your VBA scripts are brand new to me.

Good news and bad news.The good news is that the code closed the workbook but the bad news is it didn't complete the rest of my code by adding the hyperlink as is the purpose of the code.

Hi xladept. To be honest, it would take a long time to simplify all the workbooks I have and attach them. I would have to make numerous adjustments as the workbooks are programmed to work in my office with our network if you know what I mean. And of course there is a lot of personal company info as well.
Anyway, I played around a bit with the code (and I also resorted to a different alternative which I came to terms with) so here is my code which takes care of every issue.

ActiveWorkbook.Save '*Note how the workbook gets saved before I do anything to itRange("H1").Select
Selection.Copy
ActiveWindow.Close
Windows("ORDERS 2014.xlsm").Activate
Cells(Selection.Cells(1).Row, "O").Select
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False, NoHTMLFormatting:=True

It's true that when I originally posted this problem I wanted the cell to be cut out of the workbook entirely (so the workbook will look clean to the customer)
You're probably going to ask "so how do you 'clean up' your workbook from the cell that you originally wanted to cut out?" Well, that's simple and I don't know why I didn't think of it in the beginning. I have several buttons on the (newly created) workbook that I use when processing the Pro-Forma Invoice, so basically all I have to do is make one of the buttons clear the contents of the cell that I don't want!

To conclude, I am very appreciative to you for taking your time in helping me out. I learnt a lot of new stuff in VBA from your posts

Take care and best of luck to you

I realized and I corrected the issues but it still didn't work.