Copy first non-blank column to the left using macro

[elizabethlll]

Hello, I'm new to posting though reference threads from time to time - thank you!

I'm trying to create a macro that selects a range of cells in the current column, and then copies information from the cells in the column directly to the left in the range. I have gotten that to work successfully this far with the following (there are 51 cells I'm interested in):

Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(51, 0)).Select
Selection.FillRight

This would happen daily, each day is a new column that copies the previous column when the user clicks a button that I've assigned the macro to. My problem is that after every 5 days, there are two blank columns (these represent Sat and Sun, for which no data is entered). So the user would come in Monday, run the macro to populate from the previous "day" (previous column) and it would come back with blanks. Is there a way to write a macro that populates fro the previous "day" (column) unless that column is blank, in which case it would populate from last "day" (column) with values? I should say that it's OK if there are some blank CELLS in the column from which I'm populating, but the whole column can't be blank.

Thank you!

 

[JPARKHURST]

Hello, I'm new to posting though reference threads from time to time - thank you!

I'm trying to create a macro that selects a range of cells in the current column, and then copies information from the cells in the column directly to the left in the range. I have gotten that to work successfully this far with the following (there are 51 cells I'm interested in):

Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(51, 0)).Select
Selection.FillRight


Thank you!

If I may make a few suggestions:

- Currently your formula depends on your user having correct cell selected to work. Can you define a characteristic of the cells which we can use to dynamically build a range?
- Do you have a date stored in your data?
- Is there a limit to how many days you are storing and do they need to break off into a separate sheet at some point?

Here is the process I would do:

1. Create Range containing info, say rSource
2. Create Range I want to copy to, say rDestination.
3. Determine if there is a unique value I can use to identify a date (if there is not, perhaps we build one into the document at the time of data entry)
4. Once date is established, we can seperate out weekends.
- Creating our own timestamp: If date column value is nothing, then go to next column to copy (if we are entering in the data).
- Using stored data date: If weekday is Saturday+2 to date
5. rDestination.Value = rSource.value
6. Clear source
7. Return active cell to being entering data

 

[elizabethlll]

Thanks.

Currently, the sheet has a macro that puts the user in the column with the current date in it. So they click "go to today" and it takes them to the right column. The range of rows for dynamic selection would be rows 4:54, just moving one column to the right each time, except skipping Sat and Sun. Each column has a date above the range of rows I'm selecting. Does that help?

 

[JPARKHURST]

The range of rows for dynamic selection would be rows 4:54, just moving one column to the right each time, except skipping Sat and Sun. Each column has a date above the range of rows I'm selecting. Does that help?

Yes, mostly.

When you say you are moving one column to the right each time, except skipping sat & sun - does the macro you click in have a saturday/sunday in it? Does it take you to a sat/sun on Monday? Or are there just blank columns where the weekends are? I'm hearing your process looks like this:

Currently
1. User clicks on go to today button
2. button takes them to column with correct date at the top - but it has data from yesterday in the places you need to enter data.
3. You want to copy everything to the right, unless it's Monday
- If Monday, then you want to copy everything to the right, add two blank columns for Saturday/Sunday, then enter new data

 

[elizabethlll]

The macro the user clicks simply takes them to today's date. So if they were to open the file and click the button on a sat/sun, it would take them to that column. But the file isn't used on weekends. Essentially the spreadsheet contains a column for every day of the year including Sat/Sun. I suppose I could remove columns for Sat/Sun, but sometimes in doing analysis we look at calendar days, not just business days.

You have the process nearly right:
1. User clicks on go to today button
2. Button takes them to column with today's date on top. Column is blank (no data populated)
3. I want to copy the data from the previous day (column to the left) into today's column.
4. If the previous day is blank, then I want to copy the data from the first previous column that has been populated. So in most cases, that would be Monday populating from the previous Friday. There are a few exceptions, primarily if there is a holiday Monday, in which case Tue would populate from the previous Fri. (No blank columns need to be inserted because the columns for Sat/Sun are already there).

 

[JPARKHURST]

Sorry for the delay.

Sounds like i was making it waay to complicated.

So, you go to the top of a column, and simply want to copy the first column to the left which has data, correct? Really sorry this took so long!

Your code is fine, but I'll use a range just because I like them.

Sub UpdateToday()
 Dim rSource As Range, rDest As Range
  
 Set rDest = Range(ActiveCell, ActiveCell.Offset(16, 0))
 Set rSource = rDest.Offset(0, -1)
  
 rDest.Value = rSource.Value
 
 Set rSource = Nothing
 Set rDest = Nothing
End Sub

 

[elizabethlll]

No problem, thanks for keeping at it! So, that gets me where I was, which was to just fill directly from the left. My issue is when the column directly to the left is blank. I'm hoping for something that when the column directly to the left is blank, will go however many number of columns over until there is data and then fill from there.

Thanks!

 

[JPARKHURST]

is there an icon for a face palm?

Sorry...give me just a few and I will get the statement up here. Crazy busy round here lately!

 

[elizabethlll]

No problem, seeing as YOU are helping ME!

 

[JPARKHURST]

No problem, seeing as YOU are helping ME!

Glad to see you said that, b/c i got tied up here, then bolted as soon as possible!!

Sub UpdateToday()
 Dim rSource As Range, rDest As Range
 Dim lOffset As Long
 
 lOffset = -1
 
 Set rDest = Range(ActiveCell, ActiveCell.Offset(16, lOffset))
 Set rSource = rDest.Offset(0, lOffset)


    Do While FindWeekend(rSource)
        lOffset = lOffset - 1
        Set rSource = rDest.Offset(0, lOffset)
    Loop
 
 rDest.Value = rSource.Value
 
 Set rSource = Nothing
 Set rDest = Nothing
End Sub


Public Function FindWeekend(rCheck As Range) As Boolean
    If Application.WorksheetFunction.CountA(rCheck) > 0 Then
      FindWeekend = False
      MsgBox ("Weekday")
      Else
        MsgBox ("Weekend Found!")
        FindWeekend = True
    End If
End Function

Be back in a few to see how you're doing.