Mar 08 2023 10:10 PM - edited Mar 09 2023 06:06 PM
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
Mar 08 2023 11:09 PM
Mar 09 2023 10:49 PM