I am new to Macros. I have to compile several monthly reports that combine
information from other reports into one or more spreadsheets. There are 7
department reports (originally crystal reports) that I have converted to
Excel. I want to copy and paste data from specific cells in the department
reports to the a separate spreadsheet for each department. One for each of
the 7 departments.

This is a simple copy/paste function. The macro I recorded to copy and
paste specific cell data to the new spreadsheet works well for set of data
where I recorded the macro. But when I try to run it on the report with data
from a different department, it copies the same information and data that was
on the first department report.

I have created a workbook as a template to run the macro and then just
copied different department data into the worksheet, but it still creates the
new spreadsheet with the 1st department data.

What am I doing wrong?

If you post your code, we can better help pin point the problem. It sounds
like you're referencing a specific range on a specific sheet instead of doing
an "ActiveSheet" type of thing or even looping through all of the sheets that
you want to copy/paste data from.
--
-SA


"Challenger" wrote:

I am new to Macros. I have to compile several monthly reports that combine
information from other reports into one or more spreadsheets. There are 7
department reports (originally crystal reports) that I have converted to
Excel. I want to copy and paste data from specific cells in the department
reports to the a separate spreadsheet for each department. One for each of
the 7 departments.

This is a simple copy/paste function. The macro I recorded to copy and
paste specific cell data to the new spreadsheet works well for set of data
where I recorded the macro. But when I try to run it on the report with data
from a different department, it copies the same information and data that was
on the first department report.

I have created a workbook as a template to run the macro and then just
copied different department data into the worksheet, but it still creates the
new spreadsheet with the 1st department data.

What am I doing wrong?

You are probably right since I didn't know the difference in personal.xls,
active sheet, or all workbook options. I no longer have the code as I
removed the module. Anyway I can get it back? If not I'll recreate the
macro and post the code.

Can you explain looping?

"StumpedAgain" wrote:

If you post your code, we can better help pin point the problem. It sounds
like you're referencing a specific range on a specific sheet instead of doing
an "ActiveSheet" type of thing or even looping through all of the sheets that
you want to copy/paste data from.
--
-SA


"Challenger" wrote:

I am new to Macros. I have to compile several monthly reports that combine
information from other reports into one or more spreadsheets. There are 7
department reports (originally crystal reports) that I have converted to
Excel. I want to copy and paste data from specific cells in the department
reports to the a separate spreadsheet for each department. One for each of
the 7 departments.

This is a simple copy/paste function. The macro I recorded to copy and
paste specific cell data to the new spreadsheet works well for set of data
where I recorded the macro. But when I try to run it on the report with data
from a different department, it copies the same information and data that was
on the first department report.

I have created a workbook as a template to run the macro and then just
copied different department data into the worksheet, but it still creates the
new spreadsheet with the 1st department data.

What am I doing wrong?

If you've already deleted it and didn't save a copy, you'll probably be
better off just recording a new macro. Good luck!
--
-SA


"Challenger" wrote:

You are probably right since I didn't know the difference in personal.xls,
active sheet, or all workbook options. I no longer have the code as I
removed the module. Anyway I can get it back? If not I'll recreate the
macro and post the code.

Can you explain looping?

"StumpedAgain" wrote:

If you post your code, we can better help pin point the problem. It sounds
like you're referencing a specific range on a specific sheet instead of doing
an "ActiveSheet" type of thing or even looping through all of the sheets that
you want to copy/paste data from.
--
-SA


"Challenger" wrote:

I am new to Macros. I have to compile several monthly reports that combine
information from other reports into one or more spreadsheets. There are 7
department reports (originally crystal reports) that I have converted to
Excel. I want to copy and paste data from specific cells in the department
reports to the a separate spreadsheet for each department. One for each of
the 7 departments.

This is a simple copy/paste function. The macro I recorded to copy and
paste specific cell data to the new spreadsheet works well for set of data
where I recorded the macro. But when I try to run it on the report with data
from a different department, it copies the same information and data that was
on the first department report.

I have created a workbook as a template to run the macro and then just
copied different department data into the worksheet, but it still creates the
new spreadsheet with the 1st department data.

What am I doing wrong?

Here is the code.

Sub report001tofieldoffice()
'
' report001tofieldoffice Macro
' copies report 001 data to field office summary
'

'
ActiveWindow.NewWindow
ActiveWorkbook.Windows.Arrange ArrangeStyle:=xlTiled
Sheets("FO Business Results)").Select
Windows("INV001 to FO Template.xls:1").Activate
Range("E15:E16").Select
Selection.Copy
Windows("INV001 to FO Template.xls:2").Activate
Range("B3").Select
ActiveSheet.Paste
Windows("INV001 to FO Template.xls:1").Activate
Range("E18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("INV001 to FO Template.xls:2").Activate
Range("B7").Select
ActiveSheet.Paste
Windows("INV001 to FO Template.xls:1").Activate
Range("E12").Select
Application.CutCopyMode = False
Selection.Copy
Windows("INV001 to FO Template.xls:2").Activate
Range("B10").Select
ActiveSheet.Paste
Windows("INV001 to FO Template.xls:1").Activate
Range("E13").Select
Application.CutCopyMode = False
Selection.Copy
Windows("INV001 to FO Template.xls:2").Activate
Range("B12").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=11
Windows("INV001 to FO Template.xls:1").Activate
Range("E11").Select
Application.CutCopyMode = False
Selection.Copy
Windows("INV001 to FO Template.xls:2").Activate
Range("B14").Select
ActiveSheet.Paste
Windows("INV001 to FO Template.xls:1").Activate
Range("E20").Select
Application.CutCopyMode = False
Selection.Copy
Windows("INV001 to FO Template.xls:2").Activate
Range("B16").Select
ActiveSheet.Paste
Windows("INV001 to FO Template.xls:1").Activate
ActiveWindow.SmallScroll Down:=10
Range("E23").Select
Application.CutCopyMode = False
Selection.Copy
Windows("INV001 to FO Template.xls:2").Activate
Range("B17").Select
ActiveSheet.Paste
Windows("INV001 to FO Template.xls:1").Activate
With ActiveWindow
.Width = 477
.Height = 327
End With
ActiveWindow.SmallScroll Down:=12
Range("E34").Select
Application.CutCopyMode = False
Selection.Copy
Windows("INV001 to FO Template.xls:2").Activate
Range("B18").Select
ActiveSheet.Paste
Windows("INV001 to FO Template.xls:1").Activate
Range("E39").Select
Application.CutCopyMode = False
Selection.Copy
Windows("INV001 to FO Template.xls:2").Activate
Range("B19").Select
ActiveSheet.Paste
Windows("INV001 to FO Template.xls:1").Activate
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("E6").Select
Application.CutCopyMode = False
Selection.Copy
Windows("INV001 to FO Template.xls:2").Activate
Range("B21").Select
ActiveSheet.Paste
Windows("INV001 to FO Template.xls:1").Activate
Range("I7").Select
Application.CutCopyMode = False
Selection.Copy
Windows("INV001 to FO Template.xls:2").Activate
Range("B23").Select
ActiveSheet.Paste
Windows("INV001 to FO Template.xls:1").Activate
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
Range("M7").Select
Application.CutCopyMode = False
Selection.Copy
Windows("INV001 to FO Template.xls:2").Activate
Range("B24").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=5
Windows("INV001 to FO Template.xls:1").Activate
Range("Q7").Select
Application.CutCopyMode = False
Selection.Copy
Windows("INV001 to FO Template.xls:2").Activate
Range("B27").Select
ActiveSheet.Paste
Windows("INV001 to FO Template.xls:1").Activate
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("E17").Select
Application.CutCopyMode = False
Selection.Copy
Windows("INV001 to FO Template.xls:2").Activate
Range("B26").Select
ActiveSheet.Paste
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("B2").Select
ActiveWindow.SmallScroll Down:=21
Range("B2:B27").Select
Selection.Interior.ColorIndex = xlNone
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
Selection.Font.Bold = True
Selection.Font.Bold = False
With Selection
.HorizontalAlignment = xlGeneral
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("B3").Select
ActiveCell.FormulaR1C1 = "160"
Range("B4").Select
ActiveCell.FormulaR1C1 = "2.04"
Range("B7").Select
ActiveCell.FormulaR1C1 = "123"
Range("B10").Select
ActiveCell.FormulaR1C1 = "78"
Range("B12").Select
ActiveCell.FormulaR1C1 = "47"
Range("B14").Select
ActiveCell.FormulaR1C1 = "125"
Range("B16").Select
ActiveCell.FormulaR1C1 = "1"
Range("B17").Select
ActiveCell.FormulaR1C1 = "4"
Range("B18").Select
ActiveCell.FormulaR1C1 = "78.80%"
Range("B19").Select
ActiveCell.FormulaR1C1 = "60.90%"
Range("B21").Select
ActiveCell.FormulaR1C1 = "63.00%"
Range("B23").Select
ActiveCell.FormulaR1C1 = "61.20%"
Range("B24").Select
ActiveCell.FormulaR1C1 = "23.20%"
Range("B26").Select
ActiveCell.FormulaR1C1 = "82.50%"
Range("B27").Select
ActiveCell.FormulaR1C1 = "14.20%"
Range("B3:B27").Select
Range("B27").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)

End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("B2").Select
ActiveCell.FormulaR1C1 = "=R[1]C/R[2]C"
Range("B2").Select
Selection.NumberFormat = "0"
Windows("INV001 to FO Template.xls:1").Activate
Range("E9").Select
Selection.Copy
Windows("INV001 to FO Template.xls:2").Activate
Range("B5").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "116"
Range("B6").Select
End Sub


"StumpedAgain" wrote:

If you've already deleted it and didn't save a copy, you'll probably be
better off just recording a new macro. Good luck!
--
-SA


"Challenger" wrote:

You are probably right since I didn't know the difference in personal.xls,
active sheet, or all workbook options. I no longer have the code as I
removed the module. Anyway I can get it back? If not I'll recreate the
macro and post the code.

Can you explain looping?

"StumpedAgain" wrote:

If you post your code, we can better help pin point the problem. It sounds
like you're referencing a specific range on a specific sheet instead of doing
an "ActiveSheet" type of thing or even looping through all of the sheets that
you want to copy/paste data from.
--
-SA


"Challenger" wrote:

I am new to Macros. I have to compile several monthly reports that combine
information from other reports into one or more spreadsheets. There are 7
department reports (originally crystal reports) that I have converted to
Excel. I want to copy and paste data from specific cells in the department
reports to the a separate spreadsheet for each department. One for each of
the 7 departments.

This is a simple copy/paste function. The macro I recorded to copy and
paste specific cell data to the new spreadsheet works well for set of data
where I recorded the macro. But when I try to run it on the report with data
from a different department, it copies the same information and data that was
on the first department report.

I have created a workbook as a template to run the macro and then just
copied different department data into the worksheet, but it still creates the
new spreadsheet with the 1st department data.

What am I doing wrong?

Here's a good site for looking at loops:
http://exceltip.com/st/Using_Loops_i...Excel/628.html

From your code it looks like you're trying to do a series of copy/paste with
some formatting and labeling... Before we try and come up with a solution, I
have a couple of questions:

1. How many workbook do you want to do this for?
2. Is the formatting for each workbook EXACTLY the same?
3. If not, how do they differ?
4. What are the names of each of these workbooks?

--
-SA


"Challenger" wrote:

Here is the code.

Sub report001tofieldoffice()
'
..
..
..

Can you explain looping?

"StumpedAgain" wrote:

If you've already deleted it and didn't save a copy, you'll probably be
better off just recording a new macro. Good luck!
--
-SA


"Challenger" wrote:

You are probably right since I didn't know the difference in personal.xls,
active sheet, or all workbook options. I no longer have the code as I
removed the module. Anyway I can get it back? If not I'll recreate the
macro and post the code.

Can you explain looping?

"StumpedAgain" wrote:

If you post your code, we can better help pin point the problem. It sounds
like you're referencing a specific range on a specific sheet instead of doing
an "ActiveSheet" type of thing or even looping through all of the sheets that
you want to copy/paste data from.
--
-SA


"Challenger" wrote:

I am new to Macros. I have to compile several monthly reports that combine
information from other reports into one or more spreadsheets. There are 7
department reports (originally crystal reports) that I have converted to
Excel. I want to copy and paste data from specific cells in the department
reports to the a separate spreadsheet for each department. One for each of
the 7 departments.

This is a simple copy/paste function. The macro I recorded to copy and
paste specific cell data to the new spreadsheet works well for set of data
where I recorded the macro. But when I try to run it on the report with data
from a different department, it copies the same information and data that was
on the first department report.

I have created a workbook as a template to run the macro and then just
copied different department data into the worksheet, but it still creates the
new spreadsheet with the 1st department data.

What am I doing wrong?