Macro to copy cells with borders and text from one sheet to another

[richardgmurillo]

I am working on a workbook that will contain a form for me to fill out regarding customers. I have completed a blank form and I have used a second worsheet to have a blank copy of the form and on worksheet1 is where the worksheet will go where I will fill out. I have worked on a macro where when I click a "click here" button it will past a blank sheet on the next available row. I searched online and came up with a macro for the cutton to copy the form from the second sheet and past it on the first sheet. The macro works but it does not copy the borders of the cell. Is there any way to code it so that it copies the borders as well so I can have a complete form to fill out everytime I get a new customer, Thanks.

 

[mrshl9898]

When you copy and paste you just need to past the formats as well by the sounds of it. Are you currently just pasting values?

Try to work in the below, or, share the code you currently use.

' Copy
wb.Sheets(wsSource.Name).Range("A1:W79").Copy
' Paste Special
wbTarget.Sheets("Sheet1").Range("A1:W79").PasteSpecial xlFormats

 

[richardgmurillo]

Would that be the whole formula? What I am doing is adding a click box on page one and every time I click it a new empty form will be pasted onto sheet one from sheet two. The column /rows in sheet two are A1:O16 n have formulas in that form which I want to also be copy an pasted along with the form. If you can help me come up with a macro that works it would be greatly appreciated. I am trying to look up on line different macros to see what works.

Thank you

 

[mrshl9898]

Don't you already have the code to copy and paste? After you paste, paste again using .PasteSpecial xlFormats

What do you have so far?

 

[richardgmurillo]

I do but I didn't save the sheet correctly so the macro disappeared....

 

[mrshl9898]

Ok, so you have a page formatted with borders and formulas that you are using as a form? And this is in range A1:O16 on sheet1?

What does this mean?

it will past a blank sheet on the next available row.

If you just want to make a copy of that sheet and paste it in a new sheet, you can just use:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">[COLOR=#101094][FONT=inherit]Sub[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Sample[/FONT][/COLOR][COLOR=#303336][FONT=inherit]()[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] test [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Worksheet
    Sheets[/FONT][/COLOR][FONT=inherit][COLOR=#303336]([/COLOR][COLOR=#7d2727]"Sheet1"[/COLOR][/FONT][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Copy After[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Sheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]Sheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Count[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Set[/FONT][/COLOR][COLOR=#303336][FONT=inherit] test [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ActiveSheet
    test[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Name [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"copied sheet!"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Sub[/FONT][/COLOR]</code>

 

[richardgmurillo]

did you get the copy of my code?

 

[mrshl9898]

Not that i'm aware of. Where did you put it?

 

[richardgmurillo]

Not that i'm aware of. Where did you put it?

Application.ScreenUpdating= False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

Set copySheet =Worksheets("Sheet1")
Set pasteSheet =Worksheets("Sheet2")

copySheet.Range("A1:O16").Copy
pasteSheet.Cells(Rows.Count,1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False

Here


Application.ScreenUpdating = True

 

[richardgmurillo]

its actually copy sheet 2
paste sheet 1