Macro to copy from a spreadsheet and paste only into cells visible from another

Asked

Viewed 10,265 times

1

I need to copy the "L" column of the sheet "DIVISAO" to the filtered cells of the "L" column of the sheet "DPTO". That is, I need a macro that looks for the information in a spreadsheet and sticks only in the visible cells of the other.

I was able to do two macros: the 1st search information from another spreadsheet "DIVISION" and paste in the spreadsheet "DPTO". So far ok. Only I need to paste this information only into the visible cells (filter), and this macro glue into the hidden cells as well. The second macro copies and pastes in the visible cells, but the tables must be in the same spreadsheet. I could not leave the tables in separate spreadsheets and execute the macro.

What I need is: to fetch data from a spreadsheet and paste only into the visible cells (FILTERED) of the other spreadsheet.

I tried to unite the two, through CALL, but when I was executed gave an error message saying it is incompatible.

Below are the macros:

Sub COPIARPLANILHA()
'
' COPIARPLANILHA Macro
'

Sheets("DIVISÃO").Select
Range("L7:L714").Select
Range("L7:L714").Select
Selection.Copy
Sheets("DPTO").Select
Range("L7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("G1").Select
End Sub


Sub Copiar_Celulas_Visiveis()
Set too = Application.InputBox("Selecione o intervalo de células de destino", Type:=8)
For Each Cell In from
Cell.Copy
For Each thing In too
If thing.EntireRow.RowHeight > 0 Then
thing.PasteSpecial
Set too = thing.Offset(1).Resize(too.Rows.Count)
Exit For
End If
Next
Next
End Sub
excel vba

  • Do you already have part of the code? Have you tried to write a macro of this procedure to see the code that is generated automatically?

  • HI. I found these two, only one is to copy from the other sheet. The other glue in visible cells, but has to be in the same sheet. I can’t get her to "pull" the other one... they go down:

  • Copy_visible cells() Set Too = Application.Inputbox("Select target cell range", Type:=8) For Each Cell In from Cell.Copy For Each Thing In Too If Thing.EntireRow.Rowheight > 0 Then Thing.Pastespecial Set Too = Thing. Offset(1). Resize(Too.Rows.Count) Exit For End If Next Next End Sub

  • Sub COPIARPLANILHA() ' ' COPIARPLANILHA Macro ' Sheets("DIVISION"). Select Range("L7:L714"). Select Range("L7:L714"). Select Selection.Copy Sheets("DPTO"). Select Range("L7"). Select Selection.Pastspecial Paste:=xlPasteValues, Operation:=xlNone, Skipblanks _ :=False, Transpose:=False Application.Cutcopymode = False Range("G1"). Select End Sub

  • Put these codes in the body of the question, and then try to make it clearer because you couldn’t put the two subs together. What did you try with them? Did you make a mistake? Which one? With the edition your question will be reevaluated and can be reopened.

1 answer

2

When recording the macro, copy and when paste select from L7 until L(number you want) then hold ALT+; (Alt + dot and comma) This command lets you select only filtered data by ignoring hidden data.

Browser other questions tagged excel vba

You are not signed in. Login or sign up in order to post.