Using Macros

C

Challenger

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?
 
S

StumpedAgain

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.
 
C

Challenger

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?
 
S

StumpedAgain

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!
 
C

Challenger

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
 
C

Challenger

Can you explain looping?

StumpedAgain said:
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!
Click to expand...
 
S

StumpedAgain

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 said:
Here is the code.

Sub report001tofieldoffice()
'
Click to expand...
..
..
..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Grouping Names in Mail Merge 0
Need Help with Macro Security 1
Need Help with a VBA subroutine 0
Creating a Chart on form from selected data in query. 0
How to set a rotating shift schedule for 4 crews with repeat occurrence? -SOLVED 1
Create a dynamic master file 1
Match a value to multple criteria both in rows and columns 3
Need help to update a column when Drop Down List is changed 0

Top