Forum Discussion
Mike___
Mar 09, 2023Copper Contributor
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
- ecovonreinIron ContributorJust 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")".
- Arnaud1995Copper ContributorComment from Cangkir might help here (although that macro seems to be for filtered rows and you may want something for filtered columns).
Good luck!