• 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.

Vba copy and paste

mkshrestha

New Member
I been struggling a lot on this issu.
i have sheet 1 where i column a i have some data like
Col A sheet 1 Column B
Status Count
Rejected 12
Accepted 13
Duplicate 14
No duplicate 15

In my second sheet i have simillar data
Column A Column B
Status Count
Rejected
Accepted
Duplicate
No duplicate

The order of the status in sheet 2 is same all the time but the order if the status of sheet 1 will change each time i get the new sheet. Now i need to copy the count from sheet 1 to sheet 2 based on status in sheet 2. I can only copy the data if the order of the status in sheet 1 and sheet 2 are same, the code below copies the data onyl if the status order in sheet 1 column A and sheet 2 column A is same.

Code:
   Sub cpy()
    Dim mystatus As String
    Dim i As Long
    Dim j As Long
    Dim lastrow As Long
    Dim lstrow As Long
    Dim nextcol As Long
    lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
  
    ' mystatus = Sheets("FL").Cells(i, "A").Value
 
          
              For i = 2 To lastrow
               mystatus = Sheets("FL").Cells(i, "A").Value
                 If Sheets("Sheet1").Cells(i, "A").Value = mystatus Then
                   nextcol = Sheets("FL").Cells(i, Columns.Count).End(xlToLeft).Column + 1
                 
               
                    Sheets("Sheet1").Cells(i, "B").Copy
                    Sheets("sheet2").Cells(i, nextcol).PasteSpecial
                End If
            
            
              Next i
   
End Sub
it doesn't copy the data if sheet 1 column A status is
Status
Accepted
Not Duplicate
Rejected
Duplicate

Please help, any suggesstion will be very very helpful..
 
Last edited by a moderator:

Marc L

Excel Ninja
Why VBA as an easy beginner formula does the job ? (VLOOKUP) And even via VBA you can directly use a formula …​
 

mkshrestha

New Member
Thank you so much for the responses, i would have used the vlook up , but i also have second creteria to full fill before it copies the data , the code needs to look for the status and also for the state name in the second row of each colunm to the right. If the row value is FL it copy and paste it to the FL tab, if the row value NJ then copy and paste it to the NJ tab, if the row value is TN then copy and paste it to TN tab.

please look for the attachment for cleat idea.



Thank you so very much, i really appericiated....
 

Attachments

mkshrestha

New Member
I am not sure, wheather its a Database task, but need to accomplish this taks using vba, i think sorting will resolve this issue,
I tried to run following code but its giving run time error
ActiveSheet.PivotTables(1).PivotFields("Row Labels").AutoSort xlAscending " Unable to get Pivot Fields of the pivot Tables. Please help

Thank you in Advance
 

Marc L

Excel Ninja
The attachment is far away from your initial explanation so yo must revise your attachment​
or revise your explanation or move to some mind reader forum …​
 
Top