excel macro do-loop for dynamic number of rows

J

Jaspur

New Member
Joined
May 15, 2013
Messages
1
Good Morning - I am new to the forums and spent much of the past two days reading through threads to try and accomplish this but with little success. With little familiarity with VBA (I had to use the Macro recorder to just get this done) - I would like this code which runs correctly for the first row of data to run on every subsequent row of data until all rows were read in to the template.

I have two conundrums:
(1) How do i modify this code to run on every row of data and not just the first row.
(2) In my template, do I need to copy/paste the template XX amount of times for each row of data and/or will the data fill the template and provide a new sheet for every row of data I have (just using the one template shell)? ie currently I have the template setup as I want it and am expecting 5 rows of data - does the template need to appear 5 times or is just the 1 sufficient?

Background: I'm importing my data to an excel sheet from SAS. Unfortunately SAS doesn't allow the reporting flexibility i want to set up the report and so i'm relying on excel to give me that flexibility. I'm hoping to fully automate this process from SAS but am getting tripped up on the Macro/VBA language in excel.

I'm running on Windows 7 using Excel 2010. My code is below. Many thanks -

Sub Enter()
'
' Enter Macro
'
' Keyboard Shortcut: Ctrl+Shift+E
'
Windows("one.xls").Activate
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A2").Select
Selection.Copy
Windows("hello1.xlt").Activate
ActiveSheet.Paste
Windows("one.xls").Activate
Range("B2").Select
Application.CutCopyMode = False
Selection.Copy
Windows("hello1.xlt").Activate
Range("A11:B11").Select
ActiveSheet.Paste
Windows("one.xls").Activate
Range("I2").Select
Application.CutCopyMode = False
Selection.Copy
Windows("hello1.xlt").Activate
Range("C11").Select
ActiveSheet.Paste
Windows("one.xls").Activate
Range("P2").Select
Application.CutCopyMode = False
Selection.Copy
Windows("hello1.xlt").Activate
Range("D11").Select
ActiveSheet.Paste
Windows("one.xls").Activate
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
Range("W2").Select
Application.CutCopyMode = False
Selection.Copy
Windows("hello1.xlt").Activate
Range("E11").Select
ActiveSheet.Paste
Windows("one.xls").Activate
Range("AD2").Select
Application.CutCopyMode = False
Selection.Copy
Windows("hello1.xlt").Activate
Range("A13:C13").Select
ActiveSheet.Paste
Windows("one.xls").Activate
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 27
Range("AK2").Select
Application.CutCopyMode = False
Selection.Copy
Windows("hello1.xlt").Activate
Range("D13:E13").Select
ActiveSheet.Paste
Windows("one.xls").Activate
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 37
Range("AR2").Select
Application.CutCopyMode = False
Selection.Copy
Windows("hello1.xlt").Activate
Range("E6").Select
ActiveSheet.Paste
Windows("one.xls").Activate
Range("AS2").Select
Application.CutCopyMode = False
Selection.Copy
Windows("hello1.xlt").Activate
Range("B7").Select
ActiveSheet.Paste
Windows("one.xls").Activate
Range("AT2").Select
Application.CutCopyMode = False
Selection.Copy
Windows("hello1.xlt").Activate
Range("B8").Select
ActiveSheet.Paste
Windows("one.xls").Activate
Range("AU2").Select
Application.CutCopyMode = False
Selection.Copy
Windows("hello1.xlt").Activate
Range("E8").Select
ActiveSheet.Paste
Windows("one.xls").Activate
Range("AV2").Select
Application.CutCopyMode = False
Selection.Copy
Windows("hello1.xlt").Activate
Range("E7").Select
ActiveSheet.Paste
Windows("one.xls").Activate
Windows("hello1.xlt").Activate
Range("B6,B8,A11:B11,C11,A13:C13,D13:E13,E11,E8,E7,E6").Select
Range("E6").Activate
Application.CutCopyMode = False
With Selection.Font
.Name = "Calibri"
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
With Selection.Font
.Name = "Calibri"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
ActiveSheet.Shapes.Range(Array("Rectangle 2")).Select
End Sub
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
You must log in or register to reply here.

Similar threads

J
  • Question Question
Issue with Macros and renaming files
Replies
6
Views
996
kevin9999
K
G
  • Solved
How do I automatically insert into next blank row..
Replies
2
Views
649
ghrek
G
C
  • Solved
Automation error The Object invoked has disconnected from its clients.
Replies
7
Views
1K
cstuder
C
R
  • Question Question
Formatting using VBA
Replies
0
Views
544
RandyD123
R
B
  • Question Question
need recorded macro to copy from whatever workbook i currently have open and active rather than specific named workbook. without Dim objects or...
Replies
1
Views
303
Anthony47
A

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top