Search workbooks and paste to ActiveSheet.Range

[Bodaw]

Evening!

So, I'm trying to make a macro that will search multiple workbooks for a date defined from a user input box, select that cell, offset the selection by 3 columns and resize with 14 rows.
The reason for this is that every day I get 1 staffing report from 6 different bases (effectively 6 files) and have to compare the info from yesterday with the updated info from today.
So what I get are 6 files that look something like what's below. A2 and downwards are the dates I want to search for, while D2 and downwards contain the numbers I want.
The .Resize is because I need the info from the date I search from and the 2 following weeks (+14 rows).

Report ex.
A B C D E
1| x x x 1 x
2| x x x 2 x
3| x x x 3 x
4| x x x 4 x
5| x x x 5 x

I've already set up an Excel sheet to store and compare the extracted info, but I'm having some issues with efficiently extracting it from multiple rows in the reports, to 1 row in the "master".
The example below is for 1 report only, but I'm thinking that if I get it to work, I can just add the path and name variables, then add the code necessary.

What I got so far:

Option Explicit

Sub SearchARN()

Application.ScreenUpdating = False

Dim ARNsource As Workbook
Dim MasterWB As Workbook
Dim MasterSH As Worksheet

Set MasterWB = ActiveWorkbook
Set MasterSH = MasterWB.Sheets("Sheet1")
Set ARNsource = Workbooks.Open("C:\Users\"xxxxx"\Desktop\Macro\Reports\Nightshift_ARN.xlsx")

' D2 here is for testing only, later it should be the "date"/value stored from the user input box

With ARNsource.Sheets("Sheet1").Range("D2").Resize(14, 0)
.Select.Copy
End With

' The range defined is where I need the extracted data to go, for it to work with the comparison formula in the "master"
' I've tried using .Transpose to paste from say "D2:D16" to "C33:Q33", but for me that only created a whole new set of issues

With MasterSH.Range("C33:Q33").PasteSpecial
End With

ARNsource.Close SaveChanges:=False

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

I'm getting a bit different errors depending on where I make changes, but I'm hoping you guys could give me some pointers to where I can begin.

Thanks in advance!

[xladept]

Hi Bodaw,

Welcome to the Forum!

Try this:

Please Login or Register  to view this content.

*Note that I used code tags - here's how we address that:

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

[Bodaw]

40min later and it does pretty much exactly what I want!

I've got a couple questions that I hope you could answer though:
1.: The target range now saves to C33:C46 instead of C33:Q33, which only activates one of the comparison cells, could that be solved?
(I'm more adept in PowerShell, and only recently started with VBA, so normally I would use 'Delimiter'. I tried playing around with '.Transpose', but then I'm back to the errors...)

2.: After the save, the original formatting is removed. Earlier I used the .PasteSpecial to solve that, and I hoped .Value would do the same now.
Would it be easier to add the formatting back further down or is there a ".keepFormatting" kind of thing I could use?

3.: At the bottom there's a 'Application.CutCopyMode = False' to clear out the clipboard.
Could this potentially clear out what I've stored from the user input box earlier or is it limited to the 'Sub -> End Sub'?

Thanks for your help so far!

[xladept]

Maybe:

Please Login or Register  to view this content.

*Let me know if this works - and, I'm thinking about the formatting - what is the formatting, by the way?

[xladept]

Here it is with the paste special code:

Please Login or Register  to view this content.

*Are you saying that all those 14 values are a single date?

[Bodaw]

The code now works perfectly - The formatting solved it self after I entered it again, it had probably just been removed by one of my earlier attempts
Those 14 values are the amount of people we expect to have available for each day, so they're not 14 values stored within a single day, rather 1 value from 14 days.

I'm now trying to integrate the user input box value as a text string to locate in the target.
What I got so far seems logical to me, but I'm getting a '438' error from the target range.

Please Login or Register  to view this content.

[xladept]

Try:

Please Login or Register  to view this content.

** When you put quotes around a variable name it's looking for the variable name - not the variable

[Bodaw]

The whole thing makes much more sense now, and you've probably saved me many, many hours of frustration!

Last thing before this all should fall in to place.
This piece here is returning "Duplicate decleration...":

Please Login or Register  to view this content.

Tried removing it and changing:

Please Login or Register  to view this content.

To:

Please Login or Register  to view this content.

But then of course the 'F =' is missing a reference.

[xladept]

Put all the Dims up top and try it:

Please Login or Register  to view this content.

[Bodaw]

Smart thought, but this is returning "Object doesn't support this property or method"

Please Login or Register  to view this content.

[xladept]

My bad

Try:

Please Login or Register  to view this content.

[Bodaw]

Should have noticed that my self... :P
Tried it out at work today and it's running without errors, but it's also transferring the data from the first source book to all the target ranges.
Played around with some loop functions, but then the errors were back of course.

Here's the code now, with all the paths to the workbooks set up:

Please Login or Register  to view this content.

It's basically taking the info I request from ARN and pastes it in to the ranges for the other bases as well, without retrieving the info from the 5 other bases.

Ideas?

[xladept]

Please Login or Register  to view this content.

Why wouldn't the second assignment over write all but 4 of the first assignment?

[Bodaw]

You've lost me.
What it does now is fill up the target range for ARN (C13:Q13) as well as the range for BGO (C17:Q17), and then continues to fill up the ranges for the other bases as well, with the information from ARN.
Instead of filling up range ARN, then continuing to the BGO, locating the input and moving it over to the BGO range, and so on.

[xladept]

Replace your settings with:

Please Login or Register  to view this content.

And try it:|