Hi there,

So I've spent most of this evening learning VBA to expand my knowledge. I am trying to do a simple everyday task and not sure if there is a quicker way of doing this...

1) Data is in Cell A2 onwards
2) Formula is in B1 and C1
3) Formula is to concatenate B1 and A1 to go down to the bottom of the data set in Cell A
4) Column C is to do the same
5) Copy Column C data into Column B (start at the end of the data set) and then pastevalues
6) Copy and paste the data into a notepad and then save.

I think I'm using to much LastRow / FindLastRow etc... but I can't seem to re-use them as it doesn't seem to re-look at the data set again to copy the new information or view the next lastrow...
Also I've noticed when it copies and pastes it repeats the paste of B2 - B6 (as an example) but in the notepad it copies fine... The issue is that I will probably have around 30-40 thousand rows of data in cell B before I even copy the data in cell C so it could take a little bit of time...

I've blocked out the copy/paste and save to note pad as was testing, this is working fine, although I am trying to read up how to actually save it to a desktop. Eventually I will be saving it to a shared location but will also want to label it to the date (YYYYMMDD - Name of file.txt)

Is there any help on this please or any way to simplify it. 

 Sub FormulaCopyPasteAndSave()
Dim LastRow As Long
Dim FindLastRow As Long
Dim CopyAllRows As Long
Dim CopyCellB As Long

 ' STEP 1 - FIND LastRow OF CELL A FOR STEP 2
    LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
    
 ' STEP 2- COPY FORMULAS TO BOTTOM OF DATASET
    
 'Look at the formula in B1/C1 and copy down to last row of column B/C
 'Copy Data column C until lastrow of dataset
    Range("$B$1:B" & LastRow).FillDown
    Range("$C$1:C" & LastRow).FillDown
    Range("$C$2:C" & LastRow).copy
        
 ' STEP 3 - FIND 'FindLastRow' OF CELL 'B' FOR STEP 4
               
 ' Find last row in column B
    FindLastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
    
 ' STEP 4 - PASTE DATA
 
 'Paste Values from Cell C2 to bottom of DataSet
    ActiveSheet.Cells(FindLastRow + 1, "B").PasteSpecial xlPasteValues

 ' STEP 5 - DELETING DATA FROM CELL C2

 ' Delete value from Cell C2 to end of data set
    Range("$C$2:C" & LastRow).ClearContents

 ' STEP 6 - COPY DATA IN CELL B & PASTE VALUE. COPY VALUES
    CopyAllRows = Cells(Cells.Rows.Count, "B").End(xlUp).Row
    Range("$B$2:B" & CopyAllRows).copy
    Selection.PasteSpecial xlPasteValues
    CopyCellB = Cells(Cells.Rows.Count, "B").End(xlUp).Row
    Range("$B$2:B" & CopyCellB).Select
    Selection.copy

 ' COPY INFORMATION TO NOTEPAD

 '  With Application
 ' Command to open Notepad
 '   Shell "Notepad.exe", 3
 ' Uses the CTRL+V to paste into Notepad
 '   SendKeys "^v"
 '   SendKeys "^s"
 '   VBA.AppActivate .Caption
 '   .CutCopyMode = False
 ' End With

End Sub
Thanks