Copy and pasting entries from one sheet to another based on Column Value

[AbhirajChauhan]

My task is to enter on one cell of a first worksheet (ROW) is transpose to the correspond cell on the other sheet. For Example; any edits made to Line 47 on the ROW sheet must automatically be replicated on Line 43 on Japan’s second worksheet. Therefore any one inputting data and making changes to the format will be automatically be updated on the corresponding Column in Japan’s sheet.
If we compare the ROW sheet to Japan’s sheet we can see only a selected number of values in column C are the same; not all the Values on the ROW Sheet is present on Japan’s sheet.

Sub CopyCells()
      Application.ScreenUpdating = False
    Dim sh1 As Worksheet, sh2 As Worksheet
  Dim j As Long, i As Long, lastrow1 As Long, lastrow2 As Long
  Set sh1 = Worksheets("ROW")
 Set sh2 = Worksheets("Japan")
 lastrow1 = sh1.Cells(Rows.Count, "C").End(xlUp).Row
 
 lastrow2 = sh2.Cells(Rows.Count, "C").End(xlUp).Row
     For i = 5 To lastrow1
   For j = 5 To lastrow2
If sh1.Cells(i, "C").Value = sh2.Cells(j, "C").Value Then
              sh1.Cells(i, "C").Value = sh2.Cells(j, "C").Value
    Application.CutCopyMode = False
       End If
      Next j
 Next i
 Application.ScreenUpdating = True
End Sub

[AbhirajChauhan]

The Line sh1.Cells(i, "C").Value = sh2.Cells(j, "C").Value should copy the row and paste it another sheet.But don't know What is missing? I am able to match the criteria but not able to paste the rows.

[Mihail]

I don't understand very well your task.
But here you are a tip:

Start a new macro.
Make your own the operations: SELECT from sh1 -> COPY -> SELECT in sh2 -> PASTE.
Stop the macro.
Edit the macro see the exact syntax and/or work around this code (use the code generate by Excel as a template).

Note that

sh1.cells(Row1,Column1)= sh2.cells(Row2,Column2)

will NOT copy the cell from sh2 to sh1.
This line of code will get to left side cell only the VALUE(not format, comments or anything else) from the right side cell.

If this not help you, post your workbook and I'll try to solve this for you. (I use Excel 2007 but I can save in a previous version if you need).

Good luck !

[AbhirajChauhan]

Thanks for replying!!Well I tried all damn things in VBA to do this task.Just want to replace the contents of entire row of sheet1 with formats to same row of sheet2.

Sub CopyCells()
      Application.ScreenUpdating = False
    Dim sh1 As Worksheet, sh2 As Worksheet
  Dim j As Long, i As Long, lastrow1 As Long, lastrow2 As Long
  Set sh1 = Worksheets("ROW")
 Set sh2 = Worksheets("Japan")
 lastrow1 = sh1.Cells(Rows.Count, "C").End(xlUp).Row
 
 lastrow2 = sh2.Cells(Rows.Count, "C").End(xlUp).Row
     For i = 5 To lastrow1
   For j = 5 To lastrow2
If sh1.Cells(i, "C").Value = sh2.Cells(j, "C").Value Then
              'sh1.Cells(i, "B").Value = sh2.Cells(j, "C").Value
                 sh1.Range("C" & i).Value = sh2.Range("C" & j).Value
                 sh1.Activate
                 sh1.Range("C" & i).Select
                 Selection.Copy
                 sh2.Activate
                 sh2.Range("C" & j).Select
              'ActiveSheet.Paste
             ' ActiveSheet.Paste Destination:=Worksheets("Japan").Cells(j, "C")
 
Application.CutCopyMode = False
With Selection
.MergeCells = False
End With
 
'Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
 
 
                 ' Selection.Range("C" & j).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
           ' :=False, Transpose:=False
                ' MsgBox " " & sh1.Range("C" & i).Value = sh2.Range("C" & j).Value & ""
 
       End If
      Next j
 Next i
 Application.ScreenUpdating = True
End Sub

[Rabbit]

The column parameter of the Cells array uses integers as inputs, not characters.

[ADezii]

@Rabbit: The Index need not be Numeric,

Worksheets("Sheet1").Cells(1, 3) = "Hello World"
Worksheets("Sheet1").Cells(1, "C") = "Hello World"

will both work.

[Mihail]

To copy row 14 from sheet_1 to sheet_2:

    Sheets("Sheet1").Select
    Rows("14:14").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A14").Select
 
    ' Only formats
    Selection.PasteSpecial Paste:=xlPasteFormats
 
    ' All (formats and values)
    Selection.PasteSpecial Paste:=xlPasteAll

or to transpose row 14 into column 14 ("N"):

    Sheets("Sheet1").Select
    Rows("14:14").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("N1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Transpose:=True

Note that ALL this code was generated by Excel using Record Macro option.
I copy-paste it for you.

If you still are not able to do the job, attache your workbook to the next post and will see.

Good luck !

[NeoPa]

If you can manage to explain what you're trying to do so that it starts to make sense then I'll look at it for you. Unfortunately it seems we're all trying to guess what you're on about and none of us has much of an idea it seems.

In the mean time I will tidy up all the posts without the mandatory CODE tags and hope you won't post any more that way in future (as it justs wastes more time).