Loop to new column (new day)

[RossC92]

Hi Forum,

I've recently been tasked with changing a daily process at my work. The general overview is the main tab is filled in daily by some of our operators and then stored in a 'database' as such for each month. However, I'm not that great at VBA

My current code is below

Sub copier()
Dim data As WorksheetDim Lastcolumn As Long
Set wb = ThisWorkbook
Set sht1 = wb.Sheets("Daily log")
Set Sht2 = wb.Sheets("Data")
Set sht3 = wb.Sheets("Shift Reports")
Lastrow = sht1.Range("G5:G43").Find("*", searchdirection:=xlPrevious).Row

ii = 3 
For i = 5 To Lastrow 
    Sht2.Range("B" & ii) = sht1.Range("G" & i).Value 
    ii = ii + 1
Next i




sht1.Range("G5:G43").ClearContents 
MsgBox "Input data copied and entries cleared for next day"

This prints from Sheet "Daily log" and pastes into "Data". The code currently works super fast, however, I'm stumped as to how I can get my code to shift to the next column in Sheet "Data" ready for the next day. I've seen a few ideas online but none seem to work.

Can someone point me in the right direction? I'm enjoying learning VBA at the moment, so would rather not be spoon fed - unless of course it does get complicated!

Thanks in advance,

Ross

 

[Fluff]

Hi & welcome to MrExcel.
You can find the next column like

   NxtCol= Sht2.Cells(3, Columns.Count).End(xlToLeft).Offset(, 1).Column

and use that in you loop like

Sht2.Cells(ii, NxtCol).Value = Sht1.Range("G" & i).Value

 

[RossC92]

Hi & welcome to MrExcel.
You can find the next column like

   NxtCol= Sht2.Cells(3, Columns.Count).End(xlToLeft).Offset(, 1).Column

and use that in you loop like

Sht2.Cells(ii, NxtCol).Value = Sht1.Range("G" & i).Value

Hi Fluff,

I just tried this; it just printed it in to the row below rather than the next column. Any ideas on amendments?

 

[Fluff]

Do you have it like

   lastrow = Sht1.Range("G5:G43").Find("*", searchdirection:=xlPrevious).Row
   NxtCol = Sht2.Cells(3, Columns.Count).End(xlToLeft).Offset(, 1).Column
   
   ii = 3
   For i = 5 To lastrow
       Sht2.Cells(ii, NxtCol).Value = Sht1.Range("G" & i).Value
       ii = ii + 1
   Next i

 

[RossC92]

Hi Fluff,

No I had - just changed it to how you just put it. It's now print across the columns when 1 days is completed. However, the first column (which will be "B" as "A" is a list of equipment) updates with the new set of values rather than keeping the 1st entry. Any idea why, I've looked at the loop but can't see any particular reason why.

[COLOR=#333333]:[/COLOR]   lastrow = Sht1.Range("G5:G43").Find("*", searchdirection:=xlPrevious).Row
   NxtCol = Sht2.Cells(3, Columns.Count).End(xlToLeft).Offset(, 1).Column
   
   ii = 3
   For i = 5 To lastrow
       ii = ii + 1
       Sht2.Cells(ii, NxtCol).Value = Sht1.Range("G" & i).Value

   Next i

 

[RossC92]

Hi Fluff,

No I had - just changed it to how you just put it. It's now print across the columns when 1 days is completed. However, the first column (which will be "B" as "A" is a list of equipment) updates with the new set of values rather than keeping the 1st entry. Any idea why, I've looked at the loop but can't see any particular reason why.

:   lastrow = Sht1.Range("G5:G43").Find("*", searchdirection:=xlPrevious).Row
   NxtCol = Sht2.Cells(3, Columns.Count).End(xlToLeft).Offset(, 1).Column
   
   ii = 3
   For i = 5 To lastrow
       ii = ii + 1
       Sht2.Cells(ii, NxtCol).Value = Sht1.Range("G" & i).Value

Next i

Apologies forgot the [/code]

 

[RossC92]

Hi Fluff,

I found my error; thank you very much for your help! I really appreciate it.

 

[Fluff]

Glad you figured it out & thanks for the feedback