VBA Copy-Paste not working ?

[appu_gusai]

hi all

i have this code, can anyone correct the code for me?

Sub Get_Data()
    Dim lastrowDB As Long, lastrow As Long
    Dim arr1, arr2, i As Integer

    With Worksheets("Sheet1")
        lastrowDB = .Cells(9, "A").End(xlUp).Row
    End With

    arr1 = Array("B", "O", "P")
    arr2 = Array("C", "E", "G")

    For i = LBound(arr1) To UBound(arr1)
        With Worksheets("Sheet1")
            lastrow = Application.Max(5, .Cells(.Rows.Count, arr1(i)).End(xlUp).Row)
            .Range(.Cells(5, arr1(i)), .Cells(lastrow, arr1(i))).Copy
        End With
         
            Workbooks.Add
   
        With Worksheets("Sheet1")
            .Range(arr2(i) & lastrowDB).Resize(lastrow - 4).Value = _
            .Range(.Cells(5, arr1(i)), .Cells(lastrow, arr1(i))).Value
        End With
   
    Next
    Application.CutCopyMode = False
End Sub

the above code is working for me, but in same file, when i add new workbooks, its not working for me and i also want to be paste data in new workbooks...


thank's in advance

 

[NARAYANK991]

Hi ,

Can you explain this in more detail ?

but in same file, when i add new workbooks, its not working for me and i also want to be paste data in new workbooks...

Narayan

 

[appu_gusai]

i want to open workbook copied data paste in new workbook


sample file attached herewith

 

[NARAYANK991]

Hi ,

Thanks for uploading a file ; can you explain the connection between the uploaded file and the code you posted initially ?

Does the posted code work with the uploaded file ?

Is it supposed to ?

Narayan

 

[appu_gusai]

HI

I want to run initially uploaded code to this file only.

yes it is supported.

if i copy the data in current workbook that should paste in new workbook

in addition to this i want to delete the blank row in between data of the sheet.

please find the attachment sample file uploaded here with.

 

[NARAYANK991]

Hi ,

I am still confused ; however see what the code is now doing and comment.

Narayan

 

[appu_gusai]

thank you.

the code is working properly.

the data shows in this file is starts from row no. 8 , but i want the data to start from row no. 9.

with addition to this i want to enter the serial no. in column "A" starts from row no. 9 to last pasted value.

in column B, the values of PAN No. by using Vlookup(sheet-2 C9, ACTIVE workbook Sheet2,2,0) formula in row no. 9 to last pasted value.

In Column D & I, i want to display the date of 3rd day of next month of the sheet generated.

IN column E,F & G i want to display the total amount of pasted values.

thanking you in advance.