VBA - Losing formats when pasting later in code

[jardenp]

I'm copying data from one book to another and I'm losing very important leading zeroes in column A when I put things between my copy and paste codes. It seems the formatting isn't carrying over, but it does when there is nothing between the copy and paste.

When I use this

'Copy data to Shared Macros.xlsb sheet[INDENT=2]Range("A2").Select[/INDENT]
                Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
                Selection.Copy
                ActiveWorkbook.Close[INDENT=2]
Workbooks.add
[/INDENT]
                ActiveSheet.Paste

I get the following on the new sheet (this is what I want because it's exactly like the original sheet)[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Unit[/TD]
[/TR]
[TR]
[TD]00012[/TD]
[/TR]
[TR]
[TD]00123[/TD]
[/TR]
[TR]
[TD]01234[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[/TR]
</tbody>[/TABLE]

When I use my real code

'Copy data to Shared Macros.xlsb sheet[INDENT=2]Range("A2").Select[/INDENT]
                Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
                Selection.Copy
                ActiveWorkbook.Close
                
                'Test if Shared Macros.xlsb is open. If not, open it
                Dim TestWorkbook As Workbook
            
                Set TestWorkbook = Nothing
                On Error Resume Next
                Set TestWorkbook = Workbooks("Shared Macros.xlsb")
                On Error GoTo 0
            
                If TestWorkbook Is Nothing Then
            
                    Workbooks.Open Filename:="S:\PLI Misc\Macro Codes\Shared Macros.xlsb"
                    Windows("Shared Macros.xlsb").Activate
                    Sheets("Equipment Master").Select
                    Range("A1").Select
            
                Else
                    Windows("Shared Macros.xlsb").Activate
                    Sheets("Equipment Master").Select
                    Range("A1").Select
                End If
                
                'Paste data
                Dim AddRow As Long
                AddRow = Range("A1").End(xlDown).Row + 1
                
                Range("A" & AddRow).Select

                ActiveSheet.Paste

I get [TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Unit[/TD]
[/TR]
[TR]
[TD]12[/TD]
[/TR]
[TR]
[TD]123[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[/TR]
</tbody>[/TABLE]

Any suggestions on how I can keep the formatting?

Thanks,

Josh in IN

 

[Fishboy]

Hi Jardenp, make a COPY of your workbook and try replacing this line of code:

ActiveSheet.Paste

With these lines of code instead:

With ActiveSheet     
     .PasteSpecial xlPasteFormats
     .PasteSpecial xlPasteValues
End With

 

[jardenp]

Thanks for the reply, Fishboy. That throws an error:

Run Time Error '1004'
PasteSpecial method of WorkSheet class failed

 

[Fishboy]

Hmm, I'll go back to the drawing board and have another think. Maybe someone else will swoop in and answer your query before I get back to you as I am not in the office much longer today so I apologise if I go quiet for a while.