PDA

View Full Version : VBA Pastespecial only visible cells after filtering



lakshman
06-14-2017, 06:19 PM
Hi everyone I am new to VBA Express!

I just began exploring VBA and I am trying to automate some processes at work. I have written the code below to filter a datasheet (csv or Source) based on specific criteria from another sheet (chartbuilder or Destination). The one final issue I have is that when i copy paste from Source to Destination, I cannot preserve the Destination's formatting. I googled and read some posts about how you cannot mix Copy Destination together with Pastespecial but I'm not sure of how to not mix them. How can I modify my code to proceed with this?


Sub ELNParametersCopyPaste()
Dim csv As Workbook
Dim chartbuilder As Workbook
Dim pdttype As Worksheet
Dim rngDest As Range
Dim copyRange As Range
Dim lastRow As Long


Set chartbuilder = Workbooks("Write Up Generator (5 Jan) with Tracker & Parameters Update.xlsm")
Set csv = Workbooks("ideas 9 june 17 - TEST.xlsx")
csv.Activate


' turn off any autofilters that are already set
csv.Activate
ActiveSheet.AutoFilterMode = False


lastRow = Cells(Rows.Count, "AM").End(xlUp).Row
ActiveSheet.Range("$A$1:$AM" & lastRow).AutoFilter Field:=36, Criteria1:=Array("John", "Jane", "Jack"), Operator:=xlFilterValues
ActiveSheet.Range("$A$1:$AM" & lastRow).AutoFilter Field:=14, Criteria1:=chartbuilder.Sheets("ELN").Range("C5")


Set copyRange = ActiveSheet.Range("J2:J" & lastRow)
copyRange.SpecialCells(xlCellTypeVisible).Copy


chartbuilder.Activate
Set pdttype = Worksheets("ELN")
copyRange.SpecialCells(xlCellTypeVisible).Copy pdttype.Range("C11")

End Sub


Thank you for all your help! Really nice knowing that there are active forums full of people who want to help others learn code.

Leith Ross
06-14-2017, 07:01 PM
Hello lakshman,

This will copy all the formats to your destination cells, except changes in row height.



Sub ELNParametersCopyPaste()


Dim csv As Workbook
Dim chartbuilder As Workbook
Dim pdttype As Worksheet
Dim rngDest As Range
Dim copyRange As Range
Dim lastRow As Long


Set chartbuilder = Workbooks("Write Up Generator (5 Jan) with Tracker & Parameters Update.xlsm")
Set csv = Workbooks("ideas 9 june 17 - TEST.xlsx")
csv.Activate


' turn off any autofilters that are already set
csv.Activate
ActiveSheet.AutoFilterMode = False


lastRow = Cells(Rows.Count, "AM").End(xlUp).Row
ActiveSheet.Range("$A$1:$AM" & lastRow).AutoFilter Field:=36, Criteria1:=Array("John", "Jane", "Jack"), Operator:=xlFilterValues
ActiveSheet.Range("$A$1:$AM" & lastRow).AutoFilter Field:=14, Criteria1:=chartbuilder.Sheets("ELN").Range("C5")


Set copyRange = ActiveSheet.Range("J2:J" & lastRow)
copyRange.SpecialCells(xlCellTypeVisible).Copy


chartbuilder.Activate
Set pdttype = Worksheets("ELN")
copyRange.SpecialCells(xlCellTypeVisible).Copy

pdttype.Range("C11").EntireColumn.PasteSpecial Paste:=xlPasteColumnWidths
pdttype.Range("C11").PasteSpecial Paste:=xlPasteAll

End Sub

lakshman
06-14-2017, 10:41 PM
Hi Leith,

Thank you for the quick response. Your code copies the source's format. I want the source's values to be pasted onto the destination such that the destination's format is preserved. Is there a way to edit this? Thank you.

Regards,
Lakshman

lakshman
06-14-2017, 11:19 PM
Hi Leith,

Actually I have solved that. I just needed to modify your code to paste values. I just have one last thing. The idea is actually to take the data file, filter it and copy the relevant parameters to the chartbuilder. Once I modified my code to paste values, I don't have any issues except when I copy the date. Those 3 dates from the image on the left need to be copied into the corresponding highlighted regions on the right while appearing as a date. Somehow after the copy is executed, Excel cannot give it to me in the format of highlighted regions.


19499

This is what the my code looks like as of now.


Sub ELNParametersCopyPaste()

Dim csv As Workbook
Dim chartbuilder As Workbook
Dim pdttype As Worksheet
Dim rngDest As Range
Dim copyRange As Range
Dim lastRow As Long


Set chartbuilder = Workbooks("Write Up Generator (5 Jan) with Tracker & Parameters Update.xlsm")
Set csv = Workbooks("ideas 9 june 17 - TEST.xlsx")
csv.Activate


' turn off any autofilters that are already set
csv.Activate
ActiveSheet.AutoFilterMode = False


lastRow = Cells(Rows.Count, "AM").End(xlUp).Row
ActiveSheet.Range("$A$1:$AM" & lastRow).AutoFilter Field:=36, Criteria1:=Array("Hau Tat CHEN", "Rohit JAISINGH", "Wei Lek YEO"), Operator:=xlFilterValues
ActiveSheet.Range("$A$1:$AM" & lastRow).AutoFilter Field:=14, Criteria1:=chartbuilder.Sheets("ELN").Range("C5")


Set copyRange = ActiveSheet.Range("J2:J" & lastRow)
copyRange.SpecialCells(xlCellTypeVisible).Copy


chartbuilder.Activate
Set pdttype = Worksheets("ELN")
copyRange.SpecialCells(xlCellTypeVisible).Copy


pdttype.Range("C11").PasteSpecial Paste:=xlPasteValues


End Sub

Regards,
Lakshman

Leith Ross
06-15-2017, 07:56 AM
Hello Lakshman,

Change the constant in the PasteSpecial function to xlPasteValuesAndNumberFormats . That should fix the Date issue.

lakshman
07-25-2017, 11:18 PM
Hey I didn't notice you replied! I figured that part out. It works now! Thank you so much.

Leith Ross
07-26-2017, 01:17 AM
Hello Lakshman,

You're welcome. Figuring it out on your is more satisfying. Nice work.