Solved

Using a macro to add data to worksheets in XL 2003

Posted on 2011-03-25
5
193 Views
Last Modified: 2012-06-27
I have a worksheet that keeps track of conference room reservations. I have 4 workbooks each with one quarter of the year for 2011. For the second quarter, on each worksheet for the workbook (see attached), I need to insert 17 Central 4 cells below 17 North on each sheet. So for instance, on the April sheet, 17 Central is on A 30, A 68, etc. I have entered 4 manually, but would need a macro to automate this process for the remainder of the April sheet and the other two months in this workbook, and in the workbooks containing the remaining two quarters of the year.
Conference-Rooms.xls
0
Comment
Question by:contrain
5 Comments
 
LVL 8

Accepted Solution

by:
wchh earned 500 total points
Comment Utility
Try Macro below
Sub Insert()
   Dim Worksheet As Worksheet
   Dim i As Integer
   For Each Worksheet In ThisWorkbook.Worksheets
       With Worksheet
       For i = 2 To .Cells(Worksheet.Rows.Count, "A").End(xlUp).Row
           If Trim(.Cells(i, 1).Value) = "17 North" Then
            .Cells(i + 4, 1).Value = "17 Central"
           End If
       Next i
       End With
    Next Worksheet
            
End Sub

Open in new window

0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
A word of caution:

>>   Dim i As Integer

Never, ever, EVER declare a row-counting variable as Integer.  The moment you get to Row 32,768, you will get a runtime error.

Just don't use Integer, period.  Use Long instead.  In the 70's it made senseto save every byte you could in your programming, but that was 40 years ago.

:)
0
 
LVL 6

Expert Comment

by:FernandoFernandes
Comment Utility
I second that ! :)
0
 
LVL 8

Expert Comment

by:wchh
Comment Utility
I also agreed. For this example, integer is good enough but long is the best.
0
 

Author Closing Comment

by:contrain
Comment Utility
This worked and gave me what I wanted. I also appreciate the other Expert's comments on what to look out for in macros.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
A few years ago I was very much a beginner at VBA, and that very much remains the case today.  I'll do my best to explain things as I go in the hope that other beginners can follow.  If you just want to check out a tool that creates a Select Case fu…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now