I'm in a position where I have to put various repetitive data points into several excel docs and or specific sheets in a mess of a document. As of current there are upwards of 4 documents that all get the exact same information while some of it may be in a different order.
As shown above would basically be the "Master file" to get the job number.
From here we go to another excel that has the various customers (spread through different docs)
Example of the alphabetized customer list
Whom then intern have their own sheets in the docs
Which then to to a customer specific status document
I've been looking for a good way to streamline this process so I'm not having to input these data points 3-4 times every single time a new project comes up.
At this time the best solution I've been able to come up with and test involves manually inputting the data into the first and second excel docs, then from there using a function such as:
='\\File Path\Foldername\AnotherNestedFolderHere\[CUSTOMER LOG B TO I.xls]SHEETNAME'!A1084
Then just modifying the cell info at the end to pull from whatever cell I need at the time for the last two workbooks. Ive been looking all over to try to figure this out from Googling macros to Youtubing VBA but nothing gets close to what I'm trying to do to the best of my understanding.
Edit: I did a little more digging, had an idea with using chatgpt. Well Bings version of it to see about writing something and after about 5 attempts this looks close to what I'm going for but upon testing, it just fails and I'm not 100% sure as to why
Sub CopyData()
Dim wbSource As Workbook
Dim wbTarget As Workbook
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim lastRow As Long
Dim targetFile As Variant
Dim targetSheet As Variant
'Open the source workbook
Set wbSource = ThisWorkbook
'Ask user to select target workbook
targetFile = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")
'Open the target workbook
Set wbTarget = Workbooks.Open(targetFile)
'Ask user which worksheet to put data onto
targetSheet = InputBox("Enter worksheet name:")
'Set the source worksheet
Set wsSource = wbSource.Worksheets("Sheet1")
'Set the target worksheet
Set wsTarget = wbTarget.Worksheets(targetSheet)
'Find the last row in the source worksheet
lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
'Copy the data from column A to I on the last row to the target worksheet
wsSource.Range("A" & lastRow & ":I" & lastRow).Copy wsTarget.Range("A" & wsTarget.Cells(wsTarget.Rows.Count, "A").End(xlUp).Row + 1)
'Increment the next line in column A by one
wsSource.Cells(lastRow + 1, "A").Value = wsSource.Cells(lastRow, "A").Value + 1
'Save and close the target workbook without prompting for changes
wbTarget.Close True
End Sub
Edit: New runtime error
