• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy paste Formulas and Formats

R

ranaray

Member
Hi All,

The below code only copy pastes values in the sheet that is generated. How do i get it to copy & paste formulas and formats in the sheet that is generated.

[pre]
Code: 
Sub breakMyList()
' This macro takes values in the range myList
' and breaks it in to multiple lists
' and saves them to separate files.

Dim cell As Range
Dim curPath As String

curPath = ActiveWorkbook.Path & ""

Application.ScreenUpdating = False
Application.DisplayAlerts = False

For Each cell In Range("Accountbutton")
[ValAccount] = cell.Value
Range("Mylist").AdvancedFilter Action:=xlFilterCopyFormulas, _
criteriarange:=Range("Criteria"), copyToRange:=Range("Extract"), unique:=False
Range(Range("Extract"), Range("Extract").End(xlDown)).Copy
Workbooks.Add
ActiveSheet.Paste

ActiveWorkbook.SaveAs Filename:=curPath & cell.Value & Format(Now, "dmmmyyyy-hhmmss") & ".xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Range(Range("Extract"), Range("Extract").End(xlDown)).ClearContents
Next cell

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub
[/pre]
 
Hi Rana ,


Instead of using :


ActiveSheet.Paste


can you not use :


ActiveSheet.PasteSpecial xlPasteFormulasAndNumberFormats


Narayan
 
Sorry Narayan,


This does not seem to work.


https://docs.google.com/file/d/0B_upMYdtf67Rb0Y1YjJqeG5ZSTA/edit?usp=sharing


Please find the link


Thanks again

Rana
 
Hi Rana ,


Can you specify what this macro should do ?


D1 has a drop-down ; suppose I select any particular item from the drop-down , and run the macro , what should happen ?


Narayan
 
Hi Rana ,


Can you try this ?

[pre]
Code: 
Sub breakMyList()
' This macro takes values in the range myList
' and breaks it in to multiple lists
' and saves them to separate files.

Dim cell As Range
Dim curPath As String

curPath = ActiveWorkbook.Path & ""

Application.ScreenUpdating = False
Application.DisplayAlerts = False

For Each cell In Range("Accountbutton")
[ValAccount] = cell.Value
Range("Mylist").AdvancedFilter Action:=xlFilterInPlace, _
criteriarange:=Range("Criteria"), unique:=False
Range("MyList").Copy
Workbooks.Add
ActiveSheet.PasteSpecial xlPasteFormulasAndNumberFormats
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:=curPath & cell.Value & Format(Now, "dmmmyyyy-hhmmss") & ".xlsx", _
FileFormat:=xlWorkbookDefault, CreateBackup:=False
ActiveWindow.Close
Next cell

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub
[/pre]
Narayan
 
Sorry Narayan - the above code works like a charm but does not paste the cell format. It only pastes the number format.


Please help.


I want it to paste number and cell format.
 
Hi Rana ,


Can you replace the existing PasteSpecial statement with this one and see ?

[pre]
Code: 
ActiveSheet.PasteSpecial xlPasteAllExceptBorders
[/pre]
Narayan
 
You must log in or register to reply here.
Back
Top