Need to automatically copy data from one sheet to another

Closed
BamaChic - Mar 11, 2015 at 03:18 PM
vcoolio
Posts
1357
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 22, 2022
- Mar 14, 2015 at 01:18 AM
Hello,

I have a worksheet titled "Pipeline Data". There is a header row and data from A-R. In column K there are dates of the contract solicitation and column L has the date of contract award. (Most dates are currently in format Month/Day/Year, so will convert the ones that aren't to this format). I am trying to figure out a way to automatically copy data into a worksheet named "2015", "2016", "2017" etc., if the date in either of these two columns are in the year 2015 I want it to be copied to "2015", if the date in either column are in 2016 then it will be copied to "2016" etc. (if I add a new opportunity to Pipeline Data worksheet I want it to automatically copy to the appropriate years worksheet). I do not want to copy/paste at all to have the data from my Pipeline Data copied to the year worksheets. I can copy the header to the yearly worksheets, but that is the only copy/paste I would like to do. The solicitation & award dates can be 5 years apart, that is why I would like to know what opportunities are being solicited in 2015 and which opportunities will be awarded in 2015 all on one sheet. Thank you in advance for any help you can give!




<

3 replies

vcoolio
Posts
1357
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 22, 2022
250
Mar 13, 2015 at 06:16 AM
Hello CMarzhan & BamaChic,

Apologies for intruding but please bear in mind that this is a public Forum and all posts and resolutions need to be displayed on the Forum for all to see, from start to finish.

Other Users may have similar issues to solve and are usually directed here via various search engines so its a case of help one, help all.

Please refrain from resolving any queries via PM or e-mail.

Please familiarise yourself with the Kioskea Charter (click on Charter at the bottom of the page).

Thank you and we trust that you understand.

Kind regards,
vcoolio.
(Moderator).
1
BamaChic
Posts
2
Registration date
Wednesday March 11, 2015
Status
Member
Last seen
March 13, 2015

Mar 13, 2015 at 09:09 AM
vcoolio,
My apologies...I did not mean to go against any rules and I understand that it could help someone else with the same dilemma if it is kept on here. I tried to attach the file on here and also through private message but it wouldn't allow me to either way. I'm not sure if it is because the file is too large? Can you advise me on how I could possibly get it to attach (in the post, not in private message). Thank you for your help and once again I apologize.

BamaChic
0
cmarzahn
Posts
35
Registration date
Wednesday February 18, 2015
Status
Member
Last seen
March 13, 2015
7
Mar 11, 2015 at 04:24 PM
Hello BamaChic,
Seems to me that you concern could be resolved with a pivot table. Any chance you could share your sheet or email me on private message? I'd be happy to take a shot at it...

Do you need all the columns in the first worksheet to appear in the year-based worksheet?

CMarzahn
0
BamaChic
Posts
2
Registration date
Wednesday March 11, 2015
Status
Member
Last seen
March 13, 2015

Mar 12, 2015 at 08:58 AM
Hi CMarzahn,
Thank you for responding to my question. Yes I am wanting all of the columns from the first worksheet to appear in the year-based worksheets. I attempted to send the workbook to you yesterday in a private message and it was taking forever to upload so I am about to attempt it again. And by the way, my boss asked me to add an additional column after my original post so there are columns A-S now, although I know that doesn't make a difference, I wanted you to be aware of it. I will try to send it to you again and hopefully it will work this time! Thanks so much again!

BamaChic
0
cmarzahn
Posts
35
Registration date
Wednesday February 18, 2015
Status
Member
Last seen
March 13, 2015
7
Mar 13, 2015 at 12:29 PM
My apologies as well. Still a bit new to all this.
0
vcoolio
Posts
1357
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 22, 2022
250
Mar 14, 2015 at 01:18 AM
Hello BamaChic & CMarzhan,

Thank you for acknowledging.

If the file is large, just supply a sample of your dataset ( a few rows and columns) but be mindful of any sensitive data. Create a dummy work book if need be showing the sample.

To upload the sample, use a free file sharing site such as DropBox or ge.tt.

If you are interested, a few months ago, I helped another Poster with a similar query which worked out nicely using VBA. Here is a link to the test work book which I have re-assigned as BamaChic Test:-

https://www.dropbox.com/s/tsfx2wf43xeunrh/BamaChic%20Test.xlsm?dl=0

It is based on one column of dates (Column F) as the criteria to have data transferred to the relevant year sheet.

Another example is the following link which I have re-assigned as BamaChic (Test2):-

https://www.dropbox.com/s/34wiej96jjph21m/BamaChic%28Test%202%29.xlsm?dl=0

This example uses a helper column (Column B) which extracts the year from the dates in Column A by using this formula:-

=Year(A3) (data starts in A3)

dragged down as far as need be. This method just simplifies things somewhat.

The code then uses Column B to transfer the data to the relevant year sheet.

My preference is the second example as the code is more compact and robust.

The datasets may be a little different to that of BamaChic but based on BamaChic's first post, I would say that you would only need one column of dates to use as criteria. You can, of course, adapt the code to your own scenario. The codes in the modules are:-

Example 1:-

Sub TransferData()

Application.ScreenUpdating = False

  Dim ws As Worksheet
  Dim lrow As Long
  Dim rng As Range
  Set ws = Sheets("Projects")
  lrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
  
For Each rng In ws.Range("F2:F" & lrow)
    Text = Right(rng.Value, 4)
Select Case Text
    Case Is = 2013
    rng.EntireRow.Copy Sheets("Projects 2013").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    rng.EntireRow.ClearContents
    Case Is = 2014
    rng.EntireRow.Copy Sheets("Projects 2014").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    rng.EntireRow.ClearContents
    Case Is = 2015
    rng.EntireRow.Copy Sheets("Projects 2015").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    rng.EntireRow.ClearContents
    Case Is = 2016
    rng.EntireRow.Copy Sheets("Projects 2016").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    rng.EntireRow.ClearContents
    
End Select

Next
MsgBox "Data transfer completed!", vbExclamation
Range("A1:I" & lrow).Sort key1:=Range("F1:F" & lrow), order1:=xlAscending, Header:=xlYes
Application.ScreenUpdating = True

End Sub


Example 2:-

Sub TransferData()
Application.ScreenUpdating = False
Dim lRow As Long
Dim MySheet As String
lRow = Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Range("B3:B" & lRow)
    MySheet = cell.Value
    cell.EntireRow.Copy Sheets(MySheet).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Next cell

MsgBox "Data transfer completed!", vbExclamation
Application.ScreenUpdating = True

End Sub


It may help...........................

I'll leave it with you both.

Cheerio,
vcoolio.

P.S. Kudos to fellow Moderator TrowaD who actually developed the code for the second example.
0