VBA: Need code to grab a range (different workbook) based off of a specific text value, then paste them below the previous data (to keep a running dat

[BGAA2011]

So the code I currently have is button-based, and is located in Workbook A, when pressed, it opens Workbook B and pastes a designated range of data from Workbook A to Workbook B.

Unfortunately, I am now stuck. I know it seems simple, but I am at a writers block.

I need this code to not just grab a range from Workbook A, but search for the word "NEW" accross 4 columns, and then paste any row that contains "NEW" into Workbook B below the data that is already there.

This is a data tracker, so I want historical data which is why I need the code to recognize the old data and paste the new days' data under yesterdays.

I apoligize if this does not make sense, I am a little flustered as I do need this complete soon. Any help would be much apprecaited!

Here is what I have:

     'Copy the range from Workbook A
     wsI.Range("A1:H30").Copy
     'Paste to Workbook B
     wsO.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
     SkipBlanks:=False, Transpose:=False

 

[JLGWhiz]

The description of your objective is a little vague, but maybe this will get you started.

Sub t()
Dim wb1 As Workbook, wb2 As Workbook, wsI As Worksheet, wsO As Worksheet, fn As Range, fAdr
Set wb1 = Workbooks(1)
Set wb2 = Workbooks(2)
Set wsI = wb1.Sheets(1) 'Edit sheet name
Set wsO = wb2.Sheets(1) 'Edit sheet name
Set fn = wsI.UsedRange.Find("New", , xlValues, xlWhole)
    If Not fn Is Nothing Then
        fAdr = fn.Address
        Do
            wsI.Cells(fn.Row, A).Resize(1, 8).Copy
            wsO.Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues
            fn = wsI.UsedRange.FindNext(fn)
        Loop While fn.Address <> fAdr
    End If
End Sub

 

[BGAA2011]

Thank you for the response! I am trying to code out now. Out of curiosity, does Dimming wb1 and wb2 as the same name of "workbook" not make a difference? Or should I be renaming those?

 

[JLGWhiz]

I just used those to get the worksheet variables assigned. You did not post your entire code so I did not know how your workbooks were declared. The Dim statement does not affect the name of the object, only the data type of the variable. Both wb1 and wb2 are workbooks, in your post they were described as workbooks A and B. I simply used index numbers assuming that the one with wsI sheet variable would be opened first. The code is provided as a guideline of 'How to' and not intended to be directly applied to your project without modification of workbook names and sheet names to coincide with your actual objects.

 

[BGAA2011]

Oh I wasn't expecting to copy-paste in, I am not quite that amateur, but I am new enough that I still have issues with workbooks and worksheets.

My biggest issue now, is figuring out how to reference the current book (workbook A, or, the source workbook) as well as the new workbook (workbook b, destination workbook).

I have code that automatically opens up the destination workbook (it is located in a shared drive, not on my desktop), but I am having problems as to how to reference those workbooks. I am not quite understanding how to refernce each code to each workbook.

Let me see if I can clarify my original question a little bit:

Data is stored in Source Workbook (SourceWorkbook) in sheet "Dailey Update". If the word "NEW" (can I use a Wildcard? because some are "NEW TODAY", or "NEW ___") shows up in Columns D through H, then I want the range from A to H copied, and pasted into the Destionation Woorkbook (DestWorkbook) in sheet "Historic Tracker". The only other thing I Need it to do is paste underneath data that is previously there. So if there are already 10 rows in sheet "Historic Tracker", I would need the new data pasted below that creating 20 rows (or whatever amount there are.

Does this make a little more sense than what I wrote above? I really apprecaite the help, I think I am almost there just confusing myself with some silly things.

 

[JLGWhiz]

You can try to run this on a copy (not your original) of your file. Just make sure that your source workbook is opened first and your destination workbook is opened second, with no other workbooks open. Or you can substitute the filenames for the index numbers in the Set statements.

Sub t2()
Dim wb1 As Workbook, wb2 As Workbook, wsI As Worksheet, wsO As Worksheet, fn As Range, fAdr
Set wb1 = Workbooks(1) 'substitute file name for index number here
Set wb2 = Workbooks(2) 'substitute file name for index number here
Set wsI = wb1.Sheets("Daily Update") 'Edit sheet name
Set wsO = wb2.Sheets("Historic Tracker") 'Edit sheet name
Set fn = wsI.Intersect(Columns("D:H"), UsedRange).Find("New", , xlValues, xlPart)
    If Not fn Is Nothing Then
        fAdr = fn.Address
        Do
            wsI.Cells(fn.Row, A).Resize(1, 8).Copy
            wsO.Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues
            fn = wsI.UsedRange.FindNext(fn)
        Loop While fn.Address <> fAdr
    End If
End Sub

 

[BGAA2011]

Holy cow, thank you for the response! I definitely had some things flipped when I had a similar code.

So I just tried running this, and I am receiving a "Compile Error: Method or data member not found" for the below section:

Sub Transfer_Data_Tracker()
Dim wb1 As Workbook, wb2 As Workbook, wsI As Worksheet, wsO As Worksheet, fn As Range, fAdr
Set wb1 = Workbooks("SourceWorkbook") 'substitute file name for index number here
Set wb2 = Workbooks("DestWorkbook") 'substitute file name for index number here
Set wsI = wb1.Sheets("Daily Update") 'Edit sheet name
Set wsO = wb2.Sheets("Historic Tracker") 'Edit sheet name
[B]Set fn = wsI.Intersect(Columns("A:H"), UsedRange).Find("New", , xlValues, xlPart)[/B]
    If Not fn Is Nothing Then
        fAdr = fn.Address
        Do
            wsI.Cells(fn.Row, A).Resize(1, 8).Copy           
            wsO.Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues
            fn = wsI.UsedRange.FindNext(fn)
        Loop While fn.Address <> fAdr
    End If
End Sub

 

[JLGWhiz]

Don't worry, I had the code screwed up too. This should work better.

Sub Transfer_Data_Tracker2()
Dim wb1 As Workbook, wb2 As Workbook, wsI As Worksheet, wsO As Worksheet, fn As Range, fAdr
Set wb1 = Workbooks("SourceWorkbook") 'substitute file name for index number here
Set wb2 = Workbooks("DestinationWorkbook") 'substitute file name for index number here
Set wsI = wb1.Sheets("Daily Updates") 'Edit sheet name
Set wsO = wb2.Sheets("Historic Tracker") 'Edit sheet name
Set fn = Intersect(wsI.Columns("A:H"), wsI.UsedRange).Find("New", , xlValues, xlPart)
    If Not fn Is Nothing Then
        fAdr = fn.Address
        Do
            wsI.Cells(fn.Row, "A").Resize(1, 8).Copy
            wsO.Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues
            Set fn = Intersect(wsI.Columns("A:H"), wsI.UsedRange).FindNext(fn)
        Loop While fn.Address <> fAdr
    End If
End Sub

 

[BGAA2011]

Okay so that worked nearly perfectly. I changed the range from A:H to D because I was stupid and realized just now I only need it to find "NEW" in column D.

When the code pulls the data from the Source Workbook, it does pull it, but it also pulled the column headers and pasted it at the bottom. I need to make sure the headers stay in the destination sheet, and when I run this tomorrow it pulls new data and throws it directly underneath todays data.

Is that possible?

 

[BGAA2011]

**Can't figure out how to edit post, so I'm gonna double post real quick.

I just tried to use this code with new data and it worked perfectly, it pasted directly under the old data. However, it did still include the column header from the Source Workbook. I can't figure out how to tell it not to grab the column header.