Copy paste values and formats

[eazy899]

Hi,

I am using the following code to copy over only the visible cells from 1 workbook to another due to the use of a filter.
However, some of these cells have formula so I need to copy over only formats and values as well.
How would i do this?

[/COLOR][COLOR=#333333]wb1.Worksheets(e).Range("A3:AM3000").SpecialCells(xlCellTypeVisible).Copy wb2.Worksheets(e).Range("A1")[/COLOR][COLOR=#333333]

 

[yky]

[CODE][COLOR=#333333]wb1.Worksheets(e).Range("A3:AM3000").SpecialCells(xlCellTypeVisible).Copy
wb2.Worksheets(e).Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
[COLOR=#333333]wb2.Worksheets(e).Range("A1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone[/COLOR]

[/COLOR]

[/CODE]

 

[eazy899]

[CODE][COLOR=#333333]wb1.Worksheets(e).Range("A3:AM3000").SpecialCells(xlCellTypeVisible).Copy
wb2.Worksheets(e).Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
[COLOR=#333333]wb2.Worksheets(e).Range("A1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone[/COLOR]

[/COLOR]

[/CODE]

Hi ya,


Sorry but I just realised that the line of code that I provided was wrong.
I need what you provided but for the below.


For Each e In Array("Instructions", "Director Checklist")
wb1.Worksheets(e).Copy After:=wb2.Sheets(wb2.Sheets.Count)
Next e

 

[Macropod]

Cross-posted at: https://www.excelforum.com/excel-programming-vba-macros/1214940-copy-paste-values-and-formats.html
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[eazy899]

Cross-posted at: https://www.excelforum.com/excel-programming-vba-macros/1214940-copy-paste-values-and-formats.html
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

I have now closed down the other post in accordance with forum rules. This is the only active post.

 

[Macropod]

I have now closed down the other post in accordance with forum rules.

Neither this forum nor ExcelForum has such a requirement. Please read both forums' rules carefully.