Need to automatically copy data from one sheet to another Closed

Question

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!

	


<
Configuration: Windows 7 / Mozilla 11.0

cmarzahn · Answer

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

vcoolio · Answer

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).

vcoolio · Answer

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.

Need to automatically copy data from one sheet to another

3 responses

Similar discussions

Subscribe To Our Newsletter!