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 wrote in
message ...
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).O ffset(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 wrote in message
...
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!