Moving ranges

[Stew2019]

Hello everyone. I am slowly learning excel vba code and need some help.

I have vba code to add two new columns in A&B and it is currently working. Now cell C1 has heading which is a combination of company and date. I need to take this and spilt it into two, moving it into cells A2 and B2. This cell contains company and date (example WXYZ 7/2023). I would like to take the company and move it to cell A2 and move the date to cell B2. The company format is always four characters, and the date is always formatted as shown in the example. Finally copy the new contents in cells A2-B2 down to the last row of data.

Hope someone can help me with this. Many thanks.

 

[vletm]

Stew2019​

• Try and tell the readers what is the situation, what have you tried and what do you want to achieve.
• Don't leave out information more info is better
• For the best/fastest results, Upload a Sample File (...with expected results)

 

[Stew2019]

Here is an example of before and after.

 

[vletm]

Stew2019​

One sample ...

 

[Stew2019]

Thanks for the file. Trying to run the macro, but not quite sure what it is doing. Cleared out columns J-AI and ran it but it is not doing what it is supposed to. Could you provide some additional information on how it works?

 

[vletm]

Stew2019​

You can run it once.
( After that, it should 'load' again. )
In the beginning - data should be as original.
... or it would give something else that it should give.
( or You have clicked that button more than once. )

... what it is doing ...
> It should give same layout as in range AA8 ... AI12 <
Those columns can delete.
Those columns AA ... AI had Your original data.

Did You click [ Do It ]-button?

 

[Stew2019]

Yes I see it now and understand. How can I modify the code to do this for multiple files in a specific folder? Been trying to add this in but no success so far.

 

[vletm]

for multiple files
It can use in any number of files.
in a specific folder
What would be connection with any folders?
Been trying to add this in but no success so far.
Could You try to explain more
... what do You really would like to do?

 

[Stew2019]

I have a folder on my desktop and it has multiple files (one for each company. Location is C:\Users\12345\Desktop\New Folder\

Need to go thru each file (files are in .dat format) in the folder above and add the columns and information as listed previously.

Finally consolidate all the files into one excel spreadsheet.

 

[p45cal]

I'm getting there:



Now I need a few .dat files to work on…

 

[vletm]

Stew2019​

That's a bit different story than Your original need.
You gotta send a sample .dat-file (instead an Excel-file).
After that, it could do with VBA Macros, as You've wanted.
Of course, there are other ways to do this - as You can see from previous reply.
Is there any other minor differences?

 

[Stew2019]

Hello p45cal. I have attached a zip file with four .dat files.

 

[Marc L]

Hello, as it seems they are csv text files so why named as .dat ?!​

​

 

[vletm]

Stew2019​

You sent zip-file instead of dat-file.
I won't open zip-files.
... then I cannot give any sample for Your thread with VBA.
Take care.

 

[Frank2021]

Hi @Stew2019

I enclosed my proposal.
First, press Blue button for surfing to the folder where .dat files are
then press Green button for getting data and that's it.

 

[Marc L]

For good enough reader as a beginner starter :​

• paste the below Macro Recorder VBA procedure only to the worksheet module.
• Update the variable P for the source folder path …

Sub Macro1()
    Dim P$, F$, R&
        P = Parent.Path & "\DAT\"
        F = Dir$(P & "*.dat"):  If F = "" Then Beep: Exit Sub
        R = 1
        UsedRange.Clear
        Application.ScreenUpdating = False
    Do
    With QueryTables.Add("TEXT;" & P & F, Cells(R, 3))
        .AdjustColumnWidth = False
        .TextFileCommaDelimiter = True
        .Refresh False
        .Delete
    End With
        Range("A" & R + 1 & ":B" & UsedRange.Rows.Count) = Split(Cells(R, 3))
        If R > 1 Then Rows(R).Delete Else [A1:C1] = Split("Company Date Location")
        R = UsedRange.Rows.Count + 1
        F = Dir$
    Loop Until F = ""
        Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[p45cal]

Hello p45cal. I have attached a zip file with four .dat files.

See attached.
Cell A1 contains the path to the folder with the .dat files in. It's a named range called Folder. It needs to be amended to point to your folder with the .dat files in.
If the files should change within that folder you will need to refresh the table by right-clicking it and choosing Refresh.

The Date column contains actual dates, but to see them as you have, you may want to change the format of the cell; I've done that for one cell, B6.