0

I am creating an excel macro which automatically enters the stats from daily_stats.xlsx into cells D10:D33 and cells E10:E33 from a file called statistics.txt.

These stats are generated everyday and the target cells for tomorrow are to the right of the current cells.

So todays stats go into:

D10:D33 and E10:E33

tomorrows will go into:

F10:F33 and G10:G33

the day after tomorrow will go into:

H10:H33 and I10:I33...

and so on..

How can I alter the syntax below so that whenever it picks up the text file "stats.txt" it will automatically enter it to the 2 columns to the right if the target cells have already been generated?

Thanks in advance

My syntax is as follows: 

Sub Macro1()

Workbooks.OpenText Filename:= _
    "C:\Users\username\Desktop\stats.txt" _
    , Origin:=xlMSDOS, StartRow:=2, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, _
    Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 9), _
    Array(2, 1), Array(3, 1), Array(4, 9), Array(5, 9)), TrailingMinusNumbers:=True
Range("A1:A24").Select
Selection.Copy
Windows("daily_stats.xlsx").Activate
Range("D10").Select
ActiveSheet.Paste
Windows("stats.txt").Activate
Range("B1:B24").Select
Application.CutCopyMode = False
Selection.Copy
Windows("daily_stats.xlsx").Activate
Range("E10").Select
ActiveSheet.Paste
Windows("stats.txt").Activate
ActiveWindow.Close
Range("D10").Select

End Sub
Teamothy
  • 2,000
  • 3
  • 16
  • 26
SSR
  • 1

2 Answers2

0

Here is how you can find the first blank column:

Dim lFirstNewCol as Long
Const lRow = 1 

lFirstNewCol = ActiveSheet.Cells(lRow, ActiveSheet.Columns.Count).End(xlToLeft).Column + 1

This uses the first row to find it, you can change the Const value to a different row if you want.

If you prefer the column letter: Convert Column Number to Letter

Then, as for how to apply this to your specific code, the line right before you paste would change to this:

Cells(10, lFirstNewCol).Select

On a side note, please learn how to convert your code to use fully qualified objects. Avoid using things like .Select and .Activate as they are problematic.

How to avoid using Select in Excel VBA

braX
  • 11,506
  • 5
  • 20
  • 33
  • Hi, thank you for forwarding me this syntax but i can't seem to get it to work- it still paste's the macro output into the same cells, regardless of however many times i run the macro – SSR Nov 21 '19 at 09:56
  • No problem. If you find that it works for you, please remember to accept the answer so that it does not remain an open question. – braX Nov 21 '19 at 09:57
  • Hi, So I still haven't managed to get this to work, it keeps populating the same cells – SSR Nov 21 '19 at 10:51
  • And this is why select and activate is bad - was the right sheet active when getting the new column? it has to go after setting the sheet you want to active, not before. – braX Nov 21 '19 at 11:23
  • Hi, i've just posted an update which explains the issue that i'm having in a lot more detail. I know that using ".Active" and ".Select" is not good practice but just wanted to know if the solution could be done before trying to correct the syntax. Thanks again – SSR Nov 21 '19 at 11:33
0

So just an update. I've incorporated the above syntax and shortened the syntax so it copies it in one expression.

The new syntax im using is: 

Sub Macro3()
'
' Macro3 Macro
'

'

Workbooks.OpenText Filename:= _
    "C:\Users\username\Desktop\stats.txt"_
    , Origin:=xlMSDOS, StartRow:=2, DataType:=xlDelimited, 
TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, 
Semicolon:=False, _
    Comma:=False, Space:=True, Other:=False, 
FieldInfo:=Array(Array(1, 9), _
    Array(2, 1), Array(3, 1), Array(4, 9), Array(5, 9)), 
TrailingMinusNumbers:=True
Range("A1:B24").Select
Selection.Copy
Windows("daily_stats.xlsx").Activate
Dim lFirstNewCol As Long
Const lRow = 1
lFirstNewCol = ActiveSheet.Cells(lRow, 
ActiveSheet.Columns.Count).End(xlToLeft).Column + 1
Cells(10, lFirstNewCol).Select
ActiveSheet.Paste
Windows("stats.txt").Activate
ActiveWindow.Close
End Sub

So this does add a new column to the right of the first "blank" column.

However, the issue is that this is an already formatted spreadsheet so there are values in a lot of rows except the target cells which I want to paste in (which are blank) The first completely blank column is column "CJ"; Columns A,B,C,D,E,F,H,I,....CI are already formatted and have values in rows except the target rows (10:33).

I hope this makes sense

Thanks in advance

SSR
  • 1