Forum Discussion

Mike___'s avatar
Mike___
Copper Contributor
Mar 09, 2023

Opening another excel file and copy the "specific" visible cell to the current active workbook

Im trying to open another workbook with a filtered columns and copy a visible cell on a specific columns but the code keeps pasting the value to the "first" last row used by the first visible cell that were pasted. So if it paste 1,2,3,4,5 to range b1, and it copies another value like 4,5,6 the result will be 4,5,6,4,6.I think the problem is from the curr_lrow but I really don't know exactly why. I hope this makes sense.

here's the code that I use

Sub SelectAfile()
'Select a file Macro
    Dim FileLocation As String
    Dim LastRow As Long, wsPaste As Worksheet, curr_lrow As Long
    Dim wb As Workbook, ImportWorkbook As Workbook, wsImport As Worksheet
    
    'Open File
    FileLocation = Application.GetOpenFilename
    If FileLocation = "False" Then
        MsgBox "Please select a file.", vbCritical
        Exit Sub
    End If
    
    'Set variables for copy and destination sheets
    Set wb = ActiveWorkbook         'Copy
    Set wsPaste = wb.Worksheets(1)  'Paste
    
    Application.ScreenUpdating = False
    Set ImportWorkbook = Workbooks.Open(Filename:=FileLocation)
    Set wsImport = ImportWorkbook.Worksheets(2)
    
    '1. Find last used row in the copy range based on data in column A
    LastRow = wsImport.Cells(Rows.Count, "A").End(xlUp).Row + 1
    '2. Find first blank row in the destination range based on data in column A 
    curr_lrow = wsPaste.Cells(Rows.Count, "A").End(xlUp).Row + 1 'safer than .End(xlDown)...
    
    CopyValues wsImport.Range("B2:B" & LastRow).SpecialCells(xlCellTypeVisible), wsPaste.Range("A" & curr_lrow)
    CopyValues wsImport.Range("C2:C" & LastRow).SpecialCells(xlCellTypeVisible), wsPaste.Range("b" & curr_lrow)
    
End Sub
Sub CopyValues(rngFrom As Range, rngTo As Range) 'Continuation from SelectAfile 
    Dim x As Variant
    With rngFrom
        For Each x In rngFrom.Areas
            rngTo.Resize(x.Rows.Count, x.Columns.Count).Value = x.Value
        Next
    End With
End Sub

Edit: Corrected some typo's

  • ecovonrein's avatar
    ecovonrein
    Iron Contributor
    Just guessing because I ended up with a lot of egg on my face recently doing something similar. This kind of code is a scoping nightmare. When you write " wsImport.Cells(Rows.Count, "B")..." what Rows.Count do you think you gonna get? I think you would have to write "wsImport.Cells.Rows.Count" and since that is tiresome, may be you do "With wsImport.Cells(Rows.Count, "B")".
  • Arnaud1995's avatar
    Arnaud1995
    Copper Contributor
    Comment from Cangkir might help here (although that macro seems to be for filtered rows and you may want something for filtered columns).
    Good luck!

Resources