Using Macros

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?

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?

Here's a good site for looking at loops:
http://exceltip.com/st/Using_Loops_in_VBA_in_Microsoft_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()
> '
..
..
..