VBA Help

[BarneyLTD]

Hi all, i'm hoping someone can help me write a little code to perform the below action?

So, I have 2 workbooks. I need to copy cells A-E from the active sheet to a closed workbook and paste the values in to the first sheet (named Original Order) from cell A1.
The destination workbook resides in different locations so I need to be prompted to open the file. I also need this file to remain open so a copy can be saved in a shared folder.

Is this something that can be done?

I'm overhauling some processes for work so I might be back with a few more questions

Thanks in advance

Tom

 

[gallen]

Ensure to change the filter and the default folder to suit your needs.

Sub CopyCells()
    Dim wbTo As Workbook
    Dim wsTo As Worksheet, wsFrom As Worksheet
    Dim sDefaultFolder As String
    Dim sPath As String
    Dim sfilter As String
    
    'Set the default folder for the prmpt to open to
    sDefaultFolder = "C:\Test" 'change as necessary
    ChDir sDefaultFolder
    
    'set filter, change as required
    sfilter = "Excel Files (*.xlsx; *.xlsm),*.xlsx;*.xlsm"
    
    'get the file's full path
    sPath = Application.GetOpenFilename(FileFilter:=sfilter)
    
    If sPath = "False" Then Exit Sub 'user clicked cancel
    
    'get the active sheet as this will change when workbook is open
    Set wsFrom = ActiveSheet
    
    'open the file. Ensure it isn't open
    On Error Resume Next
    Set wbTo = Workbooks.Open(Filename:=sPath)
    If Err.Number > 0 Then MsgBox "Error opening File": Exit Sub
    On Error GoTo 0
    
    'get the worksheet
    Set wsTo = wbTo.Worksheets(1)
    
    'copy A:E to A1
    wsFrom.Range("A:E").Copy wsTo.Range("A1")
    
    ActiveSheet.Cells(1, 1).Select 'select first cell
    
End Sub

 

[BarneyLTD]

Gallen, you're a genius! This works perfectly
I would like to paste in the values only if this is possible? The sheet it pastes to has formatting I wish to keep.

Ensure to change the filter and the default folder to suit your needs.

Sub CopyCells()
    Dim wbTo As Workbook
    Dim wsTo As Worksheet, wsFrom As Worksheet
    Dim sDefaultFolder As String
    Dim sPath As String
    Dim sfilter As String
    
    'Set the default folder for the prmpt to open to
    sDefaultFolder = "C:\Test" 'change as necessary
    ChDir sDefaultFolder
    
    'set filter, change as required
    sfilter = "Excel Files (*.xlsx; *.xlsm),*.xlsx;*.xlsm"
    
    'get the file's full path
    sPath = Application.GetOpenFilename(FileFilter:=sfilter)
    
    If sPath = "False" Then Exit Sub 'user clicked cancel
    
    'get the active sheet as this will change when workbook is open
    Set wsFrom = ActiveSheet
    
    'open the file. Ensure it isn't open
    On Error Resume Next
    Set wbTo = Workbooks.Open(Filename:=sPath)
    If Err.Number > 0 Then MsgBox "Error opening File": Exit Sub
    On Error GoTo 0
    
    'get the worksheet
    Set wsTo = wbTo.Worksheets(1)
    
    'copy A:E to A1
    wsFrom.Range("A:E").Copy wsTo.Range("A1")
    
    ActiveSheet.Cells(1, 1).Select 'select first cell
    
End Sub

 

[gallen]

Change the copy line to:

    'copy A:E to A1
    wsFrom.Range("A:E").Copy
    wsTo.Range("A1").PasteSpecial xlPasteValues

 

[BarneyLTD]

gallen, works a treat! thanks for the quick response!

 

[gallen]

Very welcome, happy it worked first time.. (almost)

 

[BarneyLTD]

Gallen, another favour to ask of you

is it possible to alter this so it will only copy rows where the value in column C is greater than 0?
i tried to add in a filter but it isn't working for me.

 

[RockandGrohl]

Gallen, another favour to ask of you

is it possible to alter this so it will only copy rows where the value in column C is greater than 0?
i tried to add in a filter but it isn't working for me.

If you add in a filter, this is perfectly acceptable, but you will need to change the copy to "only visible cells"

To learn how to do this, I suggest creating a dummy sheet, turning macro recorder on, filter down to a set of values, then highlight what you want and in the home tab press "Find and Select > Go To Special > Visible Cells Only"

Then copy that, and press stop recording.

Look in the code in the VB Editor window and alter to suit your needs, but this will give you the correct syntax to copy only visible cells when you filter.

As an example, if you filter a list of 100 results and only show 4, if you tell excel to copy that range, it will filter those cells plus any in between, whereas if you do it manually you do only copy what's visible to you.

 

[gallen]

To filter in code use this (amendments in red)

Sub CopyCells()
    Dim wbTo As Workbook
    Dim wsTo As Worksheet, wsFrom As Worksheet
    Dim sDefaultFolder As String
    Dim sPath As String
    Dim sfilter As String
    
    'Set the default folder for the prmpt to open to
    sDefaultFolder = "C:\Test" 'change as necessary
    ChDir sDefaultFolder
    
    'set filter, change as required
    sfilter = "Excel Files (*.xlsx; *.xlsm),*.xlsx;*.xlsm"
    
    'get the file's full path
    sPath = Application.GetOpenFilename(FileFilter:=sfilter)
    
    If sPath = "False" Then Exit Sub 'user clicked cancel
    
    'get the active sheet as this will change when workbook is open
    Set wsFrom = ActiveSheet
    
    'open the file. Ensure it isn't open
    On Error Resume Next
    Set wbTo = Workbooks.Open(Filename:=sPath)
    If Err.Number > 0 Then MsgBox "Error opening File": Exit Sub
    On Error GoTo 0
    
    'get the worksheet
    Set wsTo = wbTo.Worksheets(1)
    
    [COLOR=#ff0000]'Apply filter
    wsFrom.Range("A:E").AutoFilter Field:=3, Criteria1:=">0"[/COLOR]   
    'copy visible A:E to A1
    wsFrom.Range("A:E")[COLOR=#ff0000].SpecialCells(xlCellTypeVisible)[/COLOR].Copy
    wsTo.Range("A1").PasteSpecial xlPasteValues
    
[COLOR=#ff0000]    'Clear Filter
    wsFrom.Range("A:E").AutoFilter[/COLOR]
    
    'select first cell
    ActiveSheet.Cells(1, 1).Select
    
End Sub

 

[BarneyLTD]

Thanks RockandGrohl, Gallen.

apologies but i should have said the data to filter is in a table.

i get a Runtime error 1004 with that code Gallen, autofilter method of range class failed.

I have data in cells A:E
the table starts in A9 (the headers, data from A10)
Column C is called Order and it is this cell i need to filter before copying.

i tried recording a macro for the filter of a table and pasted that code in to what you provided Gallen but i had no luck.

Cheers