Thread: Macro help to copy row from one spreadsheet to another

sfoxbox,


Detach/open workbook GetIncomplete - sfoxbox - EF809567 - SDG10.xls and run macro GetIncomplete.

sfoxbox,

rows 1 through 7 include generic data that I'd like to have at the top of the "priorities sheet

Your attached workbook did not have this information in worksheet Priorities.

Are these 7 rows coming from worksheet Full Report?

Or, should I just leave the first 7 rows of worksheet Priorities untouched?

sfoxbox,


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub GetIncompleteV2()
' stanleydgromjr, 01/15/2012
' http://www.excelforum.com/excel-programming/809567-macro-help-to-copy-row-from-one-spreadsheet-to-another.html
Dim wF As Worksheet, wP As Worksheet
Dim c As Range, firstaddress As String, nr As Long
Application.ScreenUpdating = False
Set wF = Worksheets("Full Report")
Set wP = Worksheets("Priorities")
nr = 8
With wF.Columns(4)
  Set c = .Find("Incomplete", LookIn:=xlValues, LookAt:=xlWhole)
  If Not c Is Nothing Then
    firstaddress = c.Address
    Do
      wF.Rows(c.Row).Copy wP.Range("A" & nr)
      nr = wP.Range("D" & Rows.Count).End(xlUp).Offset(1).Row
      Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstaddress
  End If
End With
wP.Activate
Application.ScreenUpdating = True
End Sub

Then run the GetIncompleteV2 macro.

sfoxbox,


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub GetIncompleteV3()
' stanleydgromjr, 01/15/2012
' http://www.excelforum.com/excel-programming/809567-macro-help-to-copy-row-from-one-spreadsheet-to-another.html
Dim wF As Worksheet, wP As Worksheet
Dim c As Range, firstaddress As String, nr As Long, lr As Long
Application.ScreenUpdating = False
Set wF = Worksheets("Full Report")
Set wP = Worksheets("Priorities")
lr = wP.Cells(Rows.Count, "D").End(xlUp).Row
If lr > 7 Then wP.Range("A8:J" & lr).Clear
nr = 8
With wF.Columns(4)
  Set c = .Find("Incomplete", LookIn:=xlValues, LookAt:=xlWhole)
  If Not c Is Nothing Then
    firstaddress = c.Address
    Do
      wF.Rows(c.Row).Copy wP.Range("A" & nr)
      nr = wP.Range("D" & Rows.Count).End(xlUp).Offset(1).Row
      Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstaddress
  End If
End With
wP.Activate
Application.ScreenUpdating = True
End Sub

Then run the GetIncompleteV3 macro.

sfoxbox,

Thanks for the feedback.

Come back anytime.

cyee,

Forum Rules
2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.


Please create your own New Post, and then send me a Private Message with a link to your own New Post, and I will be happy to assist you.

In your own New Post:

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.

Hey Stan, it's me again. Over the past week or so I've had the chance to use the Excel spreadsheet and related macro you helped me with. By and large, it works just how I want it to but there is one more tweak I'd like to do if possible. I slightly modified the last code you sent (changed name of the "trigger word") so I've attached the latest copy of my spreadsheet so we can be on the same page. There are three worksheets in this workbook ("Full Report", "Prioritized Report" and "Executive Summary"). All of the data entry is done in the "Full Report" and once completed, your macro copies all rows that have a "No" in column D to the "Prioritized Report" spreadsheet. This function is working fine. However, the first 7 rows in the "Full Report" spreadsheet are basically "header rows" that should always be copied to the "Prioritized Report" spreadsheet and then the filtered data should begin at row 8. You last modified the macro to simply begin pasting at row 8 in the "Prioritized Sheet" which is working fine, but I'm having to do a manual cut and paste of the header rows into the "Prioritized Report" and the "Executive Summary". Can you adjust the macro to copy the first seven rows of the "Full Report" into both the "Prioritized Report" and the "Executive Summary" and then the filtered data into only the "Prioritized Report" beginning at row 8? I also want to preserve the existing function that does not duplicate data in any sheets if the macro is ran more than once. Your help is greatly appreciated. Thanks, Scott.

sfoxbox,

Can you adjust the macro to copy the first seven rows of the "Full Report" into both the "Prioritized Report" and the "Executive Summary"

In your latest workbook, worksheet Full Report, range A1:J7, can be copied to worksheet Prioritized Report, range A1:J7.

But, worksheet Executive Summary, at present, only displays the data from worksheet Full Report, range A1:E3.

Do you want me to copy worksheet Full Report, range A1:J7, into worksheet Executive Summary?

I also want to preserve the existing function that does not duplicate data in any sheets if the macro is ran more than once.

Please explain in detail what this means.