copy paste cells to another cell from different files

[tuesmorning]

I know this has been address with using the same file/sheet, but need help since this involves two files.

I have a list of store locator numbers in column B of sheet "Kick List" with the filepath Q:\WORDDATA\ZZZ\ANALYST GROUP\Kick Meeting\Kick List Master.xlsm

I would like to copy and paste each store locator number sequentially into cell H1 of sheet "Temp" from the filepath Q:\WORDDATA\ZZZ\ANALYST GROUP\Kick Meeting\Temp Master.xlsm

Essentially what I wish to do is have a template that uses cell H1 as a reference for vlookups for a report. I want the macro to copy paste the store locator numbers into H1, save a new file, and then change H1 to the next store locator number.

I already have a working the save a new file from ref. cell macro:

Public Sub SaveAsA1()
ThisFile = Range("A1").Value
ActiveWorkbook.SaveAs Filename:=ThisFile
End Sub

but need help creating this one. Any help would be greatly appreciated!

 

[tuesmorning]

I found and altered this code but am getting an error

Sub CopyPaste()
Dim Ws1 As Worksheet
Dim Ws2 As Worksheet
Dim Dest As Range
Set Ws1 = Sheets("'[Kick List Master.xlsm]Sheet1'")
Set Ws2 = Sheets("Temp")
Set Dest = Ws2.Range("H1").End(xlDown).Offset(1)
Ws1.Range("'[Kick List Master.xlsm]Sheet1'!$B1:B82").Copy
Dest.PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

error highlights:
Set Ws1 = Sheets("'[Kick List Master.xlsm]Sheet1'")

run-time error '9'
Subscript out of range

 

[tuesmorning]

Column B




Needs to go to H1

 

[tuesmorning]

bump w/ pics