Macro to find and select a row, then paste

[cwhaley1]

Hi all,

I've been trying to write a macro. Currently, I have a workbook with lots of sheets within it. Each of those sheets contain a value in "A1", which is the name of the sheet and also duplicated in a 'master' sheet containing a summary of all the worksheets.

I am trying to copy selected data from any sheet and paste it into the relevant row on the master sheet. This is all I've managed to write so far and it works up until the point where data needs to be pasted:

Sub Finished_Updating()

If MsgBox("Are you sure you have finished updating this consultant?", vbYesNo) = vbNo Then Exit Sub

Range("A1").Select
    Selection.Copy
    Sheets("Document Date Log").Select
    Range("Z1").Select
    Range("Z1").PasteSpecial
   
Columns("A:A").Select
Cells.Find(What:=Range("Z1").Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate


ActiveSheet.Previous.Select

   Range("H5:H16").Select
   Selection.Copy
   Sheets("Document Date Log").Select
    Range("B" & Cells.Rows.Count).End(xlUp).Select
   
  ''THIS IS THE POINT AT WHICH EXCEL NEEDS TO MAKE ROW IT FOUND EARLIER ACTIVE, AND THEN PASTE IN COLUMN "B" (the number of cells to copy will always be the same)
  '' Below is the code to paste the copied data transposed.   

    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
   

ActiveSheet.Visible = xlSheetHidden
Sheets("Main_Page").Activate

End Sub

Hoping somebody can help. Been trying on and off for days trying different methods!

 

[DanteAmor]

I assume you have something like this in the "Document Date Log" sheet:

Try this:

Sub Finished_Updating_1()
  Dim sh As Worksheet, sh1 As Worksheet
  Dim c As Range, f As Range
  Dim lr As Long
  
  Set sh1 = Sheets("Document Date Log")
  For Each sh In Sheets
    If sh.Name <> sh1.Name Then
      Set f = sh1.Range("A:A").Find(sh.Name, , xlValues, xlWhole, , , False)
      If Not f Is Nothing Then
        f.Offset(, 1).Resize(1, 12).Value = Application.Transpose(sh.Range("H5:H16").Value)
      Else
        'If the sheet does not exist in the master sheet list, it is added to the end of the list in column A on the master sheet
        lr = sh1.Range("A" & Rows.Count).End(3).Row + 1
        sh1.Range("A" & lr).Value = sh.Name
        sh1.Range("B" & lr).Resize(1, 12).Value = Application.Transpose(sh.Range("H5:H16").Value)
      End If
    End If
  Next
End Sub

 

[cwhaley1]

Hello DanteAmor.

First of all thank you very much for the time looking into this. Yes, you've assumed correctly how the 'document date log' tab basically looks.

Good news is that this macro will correctly copy the dates over from a tab to the appropriate row on the spreadsheet.

Bad news is that it replaces the headers of the table with a date and it also deletes other data in the table where the dates were entered manually. As I've ran the macro on different tabs, I've also noticed it's randomly entering data into other rows, too -- where their corresponding tab has no data to copy over.

I'll keep reading through the code you have kindly shared, and try to work out where it might be going wrong. Thanks again for sharing this.##

 

[cwhaley1]

UPDATE -- I've managed to sort most of the issues which were down to formatting on the tabs.

The only remaining issue is that the headers of the table (B1, C1, D1, etc) keep being replaced with a date. I'll review the code and report back.

 

[DanteAmor]

Better, give examples of what you have on each sheet and an example of the master sheet of how it is before the macro and how you want the result after the macro.

 

[cwhaley1]

Better, give examples of what you have on each sheet and an example of the master sheet of how it is before the macro and how you want the result after the macro.

I'm actually happy to report that I fixed the last remaining issue, which was again down to formatting. The original author of the workbook had merged cells on some of the tabs -- cells which were used by the formula to reference a search term. I think that was throwing it when it then pasted the data.

All working lovely now, so thanks for such a nice code. I 'paused' the code which adds rows for tab names not found, just because there are other tabs which do not need to be on the master date tab. It was useful to highlight spelling discrepancies, however!

Thanks again, I've marked your answer as the solution.