0

I am just venturing into VBA, so please forgive me if I'm asking an easy question with a straightforward solution that has already been address on this site. I've searched for a solution, but I haven't found anything that helps.

I am using a staffing spreadsheet that has a tab for each person in our department and each worksheet lists all active projects and hours allocated by month. I shift the prior month column forward to the next year on a monthly basis and I'd like to automate as much of this work as possible.

I wrote a loop in VBA that cuts the prior month and moves it down, but I need help writing code that lets me clear the cells in a specific range in the column I am moving. The problem is each tab has a different number of projects so I can't simply write code that clears cells Q4:Q12 because the ending cell will vary from tab to tab. There is a row that is consistent across all the worksheets, but I am not sure how write in VBA to clear all cells up to that point.

Any help would be greatly appreciated! Thank you!

Edit - Here is the code I am currently working with.

Sub MoveColumn()

    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Staffing Summary" Then
                ws.Columns("F:F").Cut
                ws.Columns("R:R").Insert Shift:=xlToRight
        End If
    Next ws

Application.CutCopyMode = False

End Sub
Doug Glancy
  • 27,214
  • 6
  • 67
  • 115
Max MacEachern
  • 1
  • 1
  • 2
  • Your problem seems to be related to "How do I reliably find the *last row* in a given range or worksheet" -- see the answer linked to in the Close reason. If you have trouble implementing that solution, do ask a new question but please include the code you are using and the specific problem you're encountering. Cheers. – David Zemens Jun 09 '15 at 23:30
  • Can I ask a dumb question? Why not have a single master XL sheet with all raw data, then use VBA to generate a new tab for each employee using master data sheet. You run the macro each time you publish. Macro copies only relevent data to employee tabs, based on now(). Only master XL sheet has master data... (think like a database, generate reports [ employee tabs] on demand...) – zipzit Jun 09 '15 at 23:30
  • Thanks for the reply David and zipzit. @David - I'll read through the link you posted and see if I can draw what I need from the documentation. This isn't quite the same as my situation because I am not trying to find the last row on the worksheet, but rather the last row that contains project information. There are rows beyond the last project that contain information. zipzit - I'd love that, but this is a document that managers manipulate and enter information into. I'd have to get everyone on board with making such a change, which I am open to, but would take an organizational shift. – Max MacEachern Jun 09 '15 at 23:36
  • OK. I'll reopen the Question but I think it would behoove you to show the code you're currently using and which part of that code you need assistance with. You may be able to use the `.CurrentRegion.Rows.Count` to resize the range from "Q4". – David Zemens Jun 09 '15 at 23:40
  • Sounds good, I updated the original post with my code that shifts the column for each tab in the workbook. I haven't moved on writing code that clears the cells because I honestly don't know where to start. I'd post a picture if that would be more helpful, but I don't think I am allowed to given how new I am to this website. Can I post an imgur link here instead? – Max MacEachern Jun 09 '15 at 23:49
  • Yes post an image to imgur and someone will likely post it in for you. Is there gaps in the project. something like `range("Q4",range("Q4").end(xldown)).select` would do the same thing as putting your cursor in Q4 and pressing ctrl+shift+down which assumes there are no gaps between projects and then a gap between any other data. – gtwebb Jun 09 '15 at 23:58
  • Here is the imgur link: http://i.imgur.com/B25oJLP.png I want to be able to clear the information in rows 3-10 when I move the column to the next month, but the number of rows will vary depending on the number of projects someone is on. – Max MacEachern Jun 10 '15 at 16:30

1 Answers1

0

If you are after a quick simple answer then here is my 2 cents

To copy the range for a column of any length when you know there will not be a blank cell until the bottom then:

Range("a1", Range("a1").End(xlDown)).Copy

Basically its the same as clicking on a1 and doing Ctrl+Shift+DownArrow

If there are going to be blank rows then a not so nice but workable solution is:

Range("a1", Range("a100000").End(xlUp)).Copy

This is just going a long way down and doing a ctrl+upArrow to get to the bottom row.

There are probably more eloquent ideas out there but I use these all the time.

AndrewT
  • 468
  • 1
  • 8
  • 23