How to copy and paste only filtered cells in excel using vba

Question

I've been trying to copy and paste a range of filtered cells in a specific space in my excel sheet (take a look in the images) using vba , but when I try to do that, the

error 1004

occurs. I've searched in a lot of forums and try to solve my problem in different ways but it isn't working and the error 1004 still occurs.

Sub arrumando_dados_pro_xml()
Dim n As Integer
Dim i As Integer
Dim m As Integer
Dim j As Integer



n = Cells(1000, 1).End(xlUp).Row



j = Cells(n - 1, 1).End(xlUp).Row


m = Cells(1, 50).End(xlLeft).Row




 Range(Worksheets("Planilha1").Cells(2, 1), Worksheets("Planilha1").Cells(j, m)).SpecialCells(xlCellTypeVisible).Copy
 '''Range("A2:P37").SpecialCells(xlCellTypeVisible).Select
 ''''Selection.SpecialCells(xlCellTypeVisible).Select
 '''Selection.SpecialCells(xlCellTypeVisible).Copy
 ''''Call Plan1.AutoFilter.Range.Copy

 Range(Worksheets("Planilha2").Cells(1, 1), Worksheets("Planilha2").Cells(1, m)).Paste

 Range(Worksheets("Planilha2").Cells(1, 1), Worksheets("Planilha2").Cells(1, m)).Copy

 Range(Worksheets("Planilha1").Cells(n, 1), Worksheets("Planilha2").Cells(n, m)).Copy

''' Range(Cells(n, 1), Cells(n, m)).Select
''' ActiveSheet.Paste



End Sub

The error you are getting is from m = Cells(1, 50).End(xlLeft).Row you can't use xlLeft.Row it has to be xlUp or xlDown. It look like you are trying to use m to identify the last column in the rest of your code. Are you trying to use .End(xlToLeft).Column to set the last coumn? Please clarify — GMalc
– GMalc, Commented Jan 10, 2020 at 19:18
Also, your j will equal 1 if there are no empty cells in column A above n row. If you provide an example of your data — GMalc
– GMalc, Commented Jan 10, 2020 at 19:27

GMalc · Accepted Answer · 2020-01-10 20:55:09Z

0

Since your code was a little confusing, I simplified it. Here is a basic code example, with comments, to copy visible cells in a range and paste. It can be modified as needed.

'Declare your variables
Dim ws1 As Worksheet, ws2 As Worksheet, As Range, lRow As Long, lCol As Long

'Assign your variables, you should always identify the workbook and worksheet
'ThisWorkbook refers to the workbook where your code resides
Set ws1 = ThisWorkbook.Sheets("Planilha1")
Set ws2 = ThisWorkbook.Sheets("Planilha2")

'Using your worksheet variable find the last used row and last used column
lRow = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row
lCol = ws1.Cells(1, ws1.Columns.Count).End(xlToLeft).Column

'Define your range by resizing using lRow and lCol.
Set rng = ws1.Cells(2, 1).Resize(lRow - 1, lCol)

    'Copy the visible cells in the range(normally used after filtering or with hidden rows/columns)
    rng.SpecialCells(xlCellTypeVisible).Copy
    'paste the copied range starting on row 1, after the last column with data, by using .Offset(, 1)
    ws2.Cells(1, 1).PasteSpecial xlPasteValues

If you have any questions, please ask and I will help.

Edited I modified your code, had to make changes, see comments

'Added worksheet variables
Dim ws1 As Worksheet, ws2 As Worksheet, n As Long, m As Long 'removed j As Long

Set ws1 = ThisWorkbook.Sheets("Planilha1")
Set ws2 = ThisWorkbook.Sheets("Planilha2")

n = ws1.Cells(1000, 1).End(xlUp).Row
'Removed [j = ws1.Cells(n - 1, 1).End(xlUp).Row] if there are no blank cells after "n" the new last used row then j = 1
m = ws1.Cells(1, 50).End(xlToLeft).Column 'you can't use .End(xlLeft).Row to get the last column

'changed j to n, if j = 1 then only the top two rows will be copied
ws1.Range(ws1.Cells(2, 1), ws1.Cells(n, m)).SpecialCells(xlCellTypeVisible).Copy

'when pasting, just use one cell
ws2.Cells(1, 1).PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False 'Exits the CutCopyMode, removes "Marching Ants"

edited Jan 10, 2020 at 20:55

answered Jan 10, 2020 at 12:58

GMalc

2,6381 gold badge11 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Joo Victor Carvalho De Oliveir Over a year ago

'''copying the range Range(Worksheets("Planilha1").Cells(2, 1), Worksheets("Planilha1").Cells(j, m)).SpecialCells(xlCellTypeVisible).Copy '''pasting it in the place where I want Cells(n, m).Offset(, 1).PasteSpecialxlPasteValues

Joo Victor Carvalho De Oliveir Over a year ago

that's what I'm trying to do

GMalc Over a year ago

@JooVictorCarvalhoDeOliveir I edited and added your code below with modifications and comments, I would not have set the range the way you did, because it is easier to resize from the start cell to the last row and last column

Joo Victor Carvalho De Oliveir Over a year ago

Thanks man I could solve my problem the real problem was in the command lCol = ws1.Cells(1, ws1.Columns.Count).End(xlToLeft).Column that I use row instead of column and that was the error Thanks man!!!

Collectives™ on Stack Overflow

How to copy and paste only filtered cells in excel using vba

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related