copying cells from one workbook to another

I have a very redundant project where I need to copy a range of cells from
one workbook and paste them in another thousands of times. The thing is,
there are hundreds of source workbooks and only one destination workbook.
But, all the source workbooks are formatted the same. So, I recorded a macro
to go back and forth and copy and paste the ranges of cells. The problem is
that there are absolute references in my code and I need there to be relative
references. I have no problem doing this for the cells, but I cannot figure
out how to do it for the workbook. Here is an example of the code:

Windows("PTL40125.T2B").Activate
ActiveWindow.SmallScroll Down:=9
Range("C82:C86").Select
Application.CutCopyMode = False
Selection.Copy
Windows("PTL 2000 JAN_19 LINES.xls").Activate
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=18
Range("G11002").Select

My problem is I want it to use whatever file I open and make that the active
workbook (or window) and be able to reference that rather than the absolute
reference Windows("PTL40125.T2B").Activate as that only works for that one
file.

I have another macro that pops a file open box up where you select the file
and it is assigned a variable name, and I tried to use this variable, but it
doesn't work because it's referencing the entire path of the file, rather
than just the workbook name.

I hope this makes sense to someone and they can help. Thanks!

Yup. Use a workbook object variable. Depends on how you open the file, but
essentially

dim wbkFile as workbook

set wbkFile = Workbooks.open("Filepath here")

You can then use wbkfile to reference that workbook.

Jeff

"Weatherman Adam" <Weatherman [email protected]> wrote in
message news:[email protected]...
>I have a very redundant project where I need to copy a range of cells from
> one workbook and paste them in another thousands of times. The thing is,
> there are hundreds of source workbooks and only one destination workbook.
> But, all the source workbooks are formatted the same. So, I recorded a
> macro
> to go back and forth and copy and paste the ranges of cells. The problem
> is
> that there are absolute references in my code and I need there to be
> relative
> references. I have no problem doing this for the cells, but I cannot
> figure
> out how to do it for the workbook. Here is an example of the code:
>
> Windows("PTL40125.T2B").Activate
> ActiveWindow.SmallScroll Down:=9
> Range("C82:C86").Select
> Application.CutCopyMode = False
> Selection.Copy
> Windows("PTL 2000 JAN_19 LINES.xls").Activate
> ActiveSheet.Paste
> ActiveWindow.SmallScroll Down:=18
> Range("G11002").Select
>
> My problem is I want it to use whatever file I open and make that the
> active
> workbook (or window) and be able to reference that rather than the
> absolute
> reference Windows("PTL40125.T2B").Activate as that only works for that
> one
> file.
>
> I have another macro that pops a file open box up where you select the
> file
> and it is assigned a variable name, and I tried to use this variable, but
> it
> doesn't work because it's referencing the entire path of the file, rather
> than just the workbook name.
>
> I hope this makes sense to someone and they can help. Thanks!

Adam,

The macro below will copy the same range from the first sheet of every file in the given folder, and
copy it into the first sheet of the workbook with the code.. Of course, you could copy a variable
range, copy from files in other folders, etc. but this gives you an idea of how to build that
macro.

HTH,
Bernie
MS Excel MVP


Sub Consolidate()
' Will consolidate Mulitple Sheets
' from Multiple Files onto one sheet
' Never tested with files that would
' give more than one sheets as end result
' Assumes that all data starts in cell A1 and
' is contiguous, with no blanks in column A

With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With

With Application.FileSearch
.NewSearch
'Change this to your directory
.LookIn = "C:\Excel"
.SearchSubFolders = False 'Change to true if needed
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
Set Basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
Set myBook = Workbooks.Open(.FoundFiles(i))
Range("C82:C86").Copy _
Basebook.Worksheets(1).Range("a65536").End(xlUp).Offset(1, 0)
myBook.Close
Next i
End If
End With

With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With

Basebook.SaveAs Application.GetSaveAsFilename

End Sub



"Weatherman Adam" <Weatherman [email protected]> wrote in message
news:[email protected]...
>I have a very redundant project where I need to copy a range of cells from
> one workbook and paste them in another thousands of times. The thing is,
> there are hundreds of source workbooks and only one destination workbook.
> But, all the source workbooks are formatted the same. So, I recorded a macro
> to go back and forth and copy and paste the ranges of cells. The problem is
> that there are absolute references in my code and I need there to be relative
> references. I have no problem doing this for the cells, but I cannot figure
> out how to do it for the workbook. Here is an example of the code:
>
> Windows("PTL40125.T2B").Activate
> ActiveWindow.SmallScroll Down:=9
> Range("C82:C86").Select
> Application.CutCopyMode = False
> Selection.Copy
> Windows("PTL 2000 JAN_19 LINES.xls").Activate
> ActiveSheet.Paste
> ActiveWindow.SmallScroll Down:=18
> Range("G11002").Select
>
> My problem is I want it to use whatever file I open and make that the active
> workbook (or window) and be able to reference that rather than the absolute
> reference Windows("PTL40125.T2B").Activate as that only works for that one
> file.
>
> I have another macro that pops a file open box up where you select the file
> and it is assigned a variable name, and I tried to use this variable, but it
> doesn't work because it's referencing the entire path of the file, rather
> than just the workbook name.
>
> I hope this makes sense to someone and they can help. Thanks!