Changing linking so instead of being between tables, it is linking between ranges

[dpaton05]

I have been working on a spreadsheet for months. This spreadsheet involved filling out information in a table, upon finishing, it would be copied to another table on another sheet, where further information would be added. Once this had been done, it would be copied to another workbook. The way I had it set up was a table on the first sheet which would be copied to a table on the second sheet and finally a range in the different work book.

I have been having all kinds of problems and I have had it recommended that I convert it all so it is going from range to range to range. I am not the best at coding, as I am still learning and I wanted some assistance. I am fine with formatting the sheets, just need help with the coding.

The last workbooks are financial year documents and each row in the first sheet will have a date which is transferred to the second sheet with the other relevant information for the row. The rows contain quotes for various services. Each row is transferred one row at a time.

Could someone please help me with the code I would use to transfer each row from one range object to the next?

I have attached a copy of my spreadsheet to give you some idea of what I want.

https://www.dropbox.com/s/fjljdrd0afd0wgs/quoting tool 11.7 WCI.xlsm?dl=0

Thanks,
Dave

 

[dpaton05]

Thanks Michael, that's a good idea. Would you use the line of code, Workbooks.open?

 

[Michael M]

Ok, put the name of the allocation workbook / sheet in cell A1 of a NEW..BLANK..SAVED workbook.
Then run this code from your costing workbook...try it at home first, make sure it works, then try it at work !!

Sub DP1()
Dim docYearName As String
    docYearName = Cells(1, 1).Value
    Workbooks.Open (ThisWorkbook.Path & "\" & docYearName)
End Sub

 

[dpaton05]

The code works if I add a new button on a new workbook at home. When I press the button, my allocation sheet opens.
You also said, to run it from the costing tool. It also works if I run it from the costing tool at home, as in, it opens my allocation sheet. I am going to work tomorrow, so I will test it there too.

Thanks Michael,
Dave

 

[dpaton05]

I commented out the activeworkbook.save and activeworkbook.close and it seemed to work at first. By that, I mean no error but I looked a bit closer and realised that one row in the costing tool was copied to 2 identical rows in the allocation sheet. If I press the copy button again, it should add another row underneath but it won't add any more than the initial time the copy is performed. I keep pressing the copy button and nothing else happens.

 

[Michael M]

Dave
Lets focus on getting the workbook open first....no good trying to solve a second problem, if the first doesn't work.....

 

[dpaton05]

I think I might of found a problem, with this part of the copy code:

 Combo = tblrow.Range.Cells(1, 26).Value
            
        If tblrow.Range.Cells(1, 6).Value = "Ang Wes" Then
            docYearName = tblrow.Range.Cells(1, 37).Value
        Else
            docYearName = tblrow.Range.Cells(1, 36).Value
        End If

            Workbooks.Open (ThisWorkbook.Path & "\" & docYearName)
            
            Set wsDst = Workbooks(docYearName).Worksheets(Combo)

cell 36: =CONCATENATE(AI5," ","NPSS Work Allocation Sheet.xlsm") or 2018 - 2019 NPSS Work Allocation Sheet.xlsm


but with the Set wsDST, should docYearName be only the name of the workbook?

With the current code, I think it would look like:

workbooks(2018 - 2019 NPSS Work Allocation Sheet.xlsm).(July)

Shouldn't it just be :

workbooks(2018 - 2019 NPSS Work Allocation Sheet).(July)

?

 

[dpaton05]

Dave
Lets focus on getting the workbook open first....no good trying to solve a second problem, if the first doesn't work.....

Fair enough, sorry :wink:

 

[dpaton05]

I think I may have found a problem. I changed my workbooks.open path to be

Workbooks.Open (ThisWorkbook.Path & "\" & docYearName) & (".xlsm")

and if I have the allocation sheet open, it will copy to it but if I close it, it won't open it. paste the line, then close it.

Is there another method that I could try for opening the workbook?

 

[Michael M]

Not really....you could change the thiswoorkbook to activeworkbook
AND
remove the brackets

Workbooks.Open Activeworkbook.Path & "\" & docYearName & ".xlsm"

 

[dpaton05]

It was driving me crazy so I went back to an earlier version that was working. I will keep you updated.