Some VBA to beat a repetitive task

[alanlambden]

Hi All,

I have a task that's very time consuming that I was hoping I could get help with some VBA code. I don't think it's all that complicated, except maybe for the pop-up box I was envisioning, that's a bit beyond me. I'm just not that quick on my feet to get it all written myself.

I have 94 files .. and this needs to be done on each one. Each one with a unique piece of information.

Here is what I was envisioning:

1) The code would create a pop up asking what file I want to work on
2) I navigate windows to select the file
3) the code automatically inserts a new column in A and inserts the text "New column heading X" into the first row of the new column.
4) The code then pops up a box that asks what details do you want in the new column A?
5) I insert the text (in the pop up) "Column A details - X".
6) The code copies "Column A details - X" down the worksheet until the last row that contains information. It is this "Column A details - X" that is unique to each sheet. In the next of 94 I will need to put in different details like "Column A details - Y" but I'm hoping the code would avoid me having to do the inserting, copy and pasting manually.

I have some VBA code that will then collate all my 94 files into one, but it's the "Column A details - X" that will distinguish what data is relevant to each individual file.

Thanks for your help with this.

 

[Fluff]

How about

Sub alanlambden()
    Dim Fname As String, Txt As String
    Dim Wbk As Workbook
    
    Fname = Application.GetOpenFilename
    If Fname = "False" Then Exit Sub
    Set Wbk = Workbooks.Open(Fname)
    With Wbk.Sheets(1)
        .Range("A:A").Insert
        .Range("A1") = "X"
        Txt = InputBox("Please enter something")
        .Range("A2:A" & .Range("B" & Rows.Count).End(xlUp).Row).Value = Txt
    End With
End Sub

 

[alanlambden]

Wow thanks Fluff!

It blows me away how incredibly useful and helpful this forum is.

That worked perfectly. Thank you again.

 

[Fluff]

You're welcome & thanks for the feedback