copy sheet

[josros60]

Hi,

I have spreadsheet that want to copy another sheet and the date field has formula to copy date from above cell(s) the code copy sheet want to copy sheet with the headings and values to sheet1 but i having this issues

1. is not copy the headings just the records and start in row 2 and row 1 is blank where it should be the headings
2. date is copy formula instead of date values

here is what looks like my payment sheet and what's copying right now:

[TABLE="width: 891"]
<tbody>[TR]
[TD="align: right"]10/19/2018[/TD]
[TD]CAD[/TD]
[TD="align: right"]800.00[/TD]
[TD="align: right"]10/19/2018[/TD]
[TD]3[/TD]
[TD]9999[/TD]
[TD]3000[/TD]
[TD]mario[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10/19/2018[/TD]
[TD]CAD[/TD]
[TD="align: right"]899.00[/TD]
[TD="align: right"]10/19/2018[/TD]
[TD]7[/TD]
[TD]339[/TD]
[TD]3087887[/TD]
[TD]Antonio[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

code here:

Private Sub CommandButton1_Click()a = Worksheets("Payments").Cells(Rows.Count, 1).End(xlUp).Row


For i = 2 To a


        If Worksheets("Payments").Cells(i, 3).Value > "0" Then


        Worksheets("Payments").Rows(i).Copy
        
        Worksheets("Sheet1").Activate


        b = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row


       Worksheets("Sheet1").Cells(b + 1, 1).Select


        ActiveSheet.Paste


        Worksheets("Payments").Activate


End If




Next


Application.CutCopyMode = False


ThisWorkbook.Worksheets("Payments").Cells(1, 1).Select


End Sub


thank you,

 

[Fluff]

How about

Private Sub CommandButton1_Click()
   Dim Lr As Long
   
   With Worksheets("Payments")
      Lr = .Cells(Rows.Count, 1).End(xlUp).Row
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1:H1").AutoFilter 3, ">0"
      .Range("A1:A" & Lr).SpecialCells(xlVisible).EntireRow.Copy
      Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValues
      .AutoFilterMode = False
   End With
   Application.CutCopyMode = False
End Sub

 

[josros60]

Thank you for the quick response.

the only issues are:

doesn't not copy same format like table heading fill color and table column/rows borders and and the date just pasting serial numbers and the others rows values are formatting as General but should be text because when i upload to the bank will give me an error if is not text.

in other words can it copy and paste same format?

thanks again.

 

[Fluff]

Ok, try

Private Sub CommandButton1_Click()
   Dim Lr As Long
   
   With Worksheets("Payments")
      Lr = .Cells(Rows.Count, 1).End(xlUp).Row
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1:H1").AutoFilter 3, ">0"
      .Range("A1:A" & Lr).SpecialCells(xlVisible).EntireRow.Copy
      Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValues
      Sheets("Sheet1").Range("A1").PasteSpecial xlPasteFormats
      .AutoFilterMode = False
   End With
   Application.CutCopyMode = False
End Sub

 

[josros60]

worked great!

thank you,

 

[Fluff]

You're welcome