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.
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.