VBA column selecting

[JeremyGraham95]

I am working on an inventory project and have 2 problems that i cannot find a solution for.


1. The first is i need to select the first empty cell in the first row and fill it with the current date. I have the code for the date working great and the code works fine the first time, but after that it does not seem to benefit me at all.

2. My second problem, the more complex problem. after completing the step above, i want the code to select the entire column of data starting with the third row, and also select the previous 3 columns, a total of 4 columns will be selected. and on top of this, i dont want it to select the first column. so with this being a brand new spreadsheet, the first 3 occurences would benefit from that code but would still be useful.


This is the code i have written:


Worksheets("TRENDS").Activate

SaveColNdx = Cells(1, Columns.Count).End(xlToLeft).Column
If SaveColNdx > 1 Then SaveColNdx = SaveColNdx + 1


With ActiveCell
.Value = Date
.NumberFormat = "mm/dd/yyyy"



*** later on i would like it to hide all columns except the first column ( info column) and the 6 columns with data that are closest to the right end of the sheet.

 

[mikerickson]

What's happening might be that the first time you run it, A1 is empty and SaveColNdx = 1.
The second time that you run it A1 has a date in it, but agains SaveColNdx = 1, so it gets overwritten.

You could change the test to

If Cells(1, SaveColNdx).Value <> vbNullString Then SaveColNdx = SaveColNdx + 1

Or you could put a header (or a space or a something) in A1 and always increment SaveColNdx without testing to see which column it is.

 

[JeremyGraham95]

I tried that code and it was now placing the date in a1 each time. its not moving over to the next empty cell in the row.

 

[mikerickson]

Try

With Sheets("TRENDS").Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
    .Value = Date
    .NumberFormat = "mm/dd/yyyy"
    
    If 7 < .Column Then
        Range(.Parent.Range("B1"), .Offset(0, -6)).EntireColumn.Hidden = True
    End If
End With

 

[JeremyGraham95]

That code works very well however it doesnt do the first date, which is not a problem.

the next thing i am adding to the code is to take those 6 columns, with data that arent hidden, and make a trend line in a new sheet called "sheet 2". i want the line to tell me how many of each item(row) i have used between columns. subtract the the first column from the second, second from the third and so on.

 

[JeremyGraham95]

Dim source As Worksheet
Dim destination As Worksheet
Dim emptyColumn As Long


Set source = Sheets("Order Summary")
Set destination = Sheets("TRENDS")


'find empty Column (actually cell in Row 1)'
emptyColumn = destination.Cells(1, destination.Columns.Count).End(xlUp).Column
If emptyColumn > 1 Then
emptyColumn = emptyColumn + 1
End If


source.Range("B2:B127").Copy destination.Cells(1, emptyColumn)

 

[JeremyGraham95]

i am having trouble getting this piece of code to work. it says that there is an object error in the last row of the code. can anyone help me out???