Log In
Register
Home
Active Member
July 21, 2020
Offline
Hello,
I would like to ask someone to help me with this:
I have a table that is filled regularly and the data is not sorted. I need all this data to be automatically copied to another file, because there we add an additional column and I linked both tables but I want the data to be sorted automatically by submission date in the second table.
I attach an example file with two sheets, not linked.
Thank you
P.S. Excel 2016 on PC
Trusted Members
December 20, 2019
Offline
Somthing like this?
Sub CopySort()
Worksheets("Table 1").Range("a1").CurrentRegion.Copy Worksheets("Table 2").Range("a1")
Worksheets("Table 2").Range("a:a").Columns.Insert xlToLeft
With Worksheets("Table 2").Range("a1")
.Value = "Comment"
.Interior.Color = vbBlue
.Font.Color = vbWhite
End With
Worksheets("Table 2").Range("a1").CurrentRegion.Sort KEY1:=Worksheets("Table 2").Range("C:C"), ORDER1:=xlAscending, Header:=xlYes
Worksheets("Table 2").Activate
End Sub
Active Member
July 21, 2020
Offline
Hi, yes, exactly, thank you very much.
But I won't be able to write it myself. Also, the example I gave is between two sheets, but I need this between two workbooks.
If I attach the exact tables that I use (columns) in different workbooks, would you help me again?
Do the two workbooks need to be open in order to work?
Trusted Members
December 20, 2019
Offline
The concept is simular with 2 workbooks and we can get VBA to open the workbooks if we know where they are located.
I will update the code later and repost
Active Member
July 21, 2020
Offline
Hi Purfleet, a kind reminder 🙂
It would make my life much easier and I apologize if I am being pushy.
Thank you
Trusted Members
December 20, 2019
Offline
Sorry, completly forgot
Tried to make it as flexible as possible, might be overkill
you need to put the destination Drive,Workbook name, sheet name and range and starting worksheet name and range on the input page - this will not check is the file or range actually exists so you need to make sure the path & file name etc are 100% correct.
at the end of the code i have set the destination worksheet to autosave and the starting wort to save and close - take these out if you dont want this to happen
'Saves Destination worksheet
Workbooks(DestWb).Save
'Close orginal worksheet
Workbooks(StartWb).Close True
There is lots more that can be done to make this macro better - such as check the file are there and that the input fields are populated, but i will leave that to you to improve as you see fit.
1 Guest(s)
