Transfer data [Solved/Closed]

Report
Posts
11
Registration date
Thursday March 24, 2016
Status
Member
Last seen
March 29, 2017
-
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
-
Hello,


Is it possible to transfer data for sheet 1 to another sheet i select form a drop down box that contains all the sheet's name in that work book? If its possible can someone please direct me in the right direction on how to go about it. Any help will be greatly appreciated. Thanks!

3 replies

Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
230
Hello Lynng,

The following code may do the task for you:-


Sub FindSheets()

Application.ScreenUpdating = False

   Dim MySelection As String
   
Sheet1.Select

    If Range("A2") <> "" Then
    MySelection = Range("A2")
    End If
Sheet1.UsedRange.Offset(3).Copy
Sheets(MySelection).Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
Sheet1.UsedRange.Offset(3).ClearContents

Sheets(MySelection).Columns.AutoFit
Application.CutCopyMode = False
Application.ScreenUpdating = True
Sheets(MySelection).Select

End Sub


As I don't know how your actual work book is set up or what exactly you want to transfer to each individual sheet, I have made the following assumptions:-

- Your data starts in Row 4 with headings in Row 3.
- Your drop down list is in cell A2.
- You wish to transfer all data from sheet1 (which I assume to be your Input sheet) to the selected individual sheet.
- You want the "used" data in sheet1 cleared once it is transferred to the selected individual sheet.

You will have to let us know exactly how your work book is set up so it would be a good idea to upload a sample of your work book to a free file sharing site such as DropBox, ge.tt or SpeedyShare then post the link to your file back here. Be careful with any sensitive data and show us your inputs and expected results.

Following is the link to my test work book which is set up based on my assumptions above:-

https://www.dropbox.com/s/uuea10j9wdnvwp9/Lynng%28Select%20Sheet%20from%20Drop%20Down%20List%2Ctransfer%20data%29.xlsm?dl=0

Select a sheet from the drop down in cell A2 and then click on the Find Sheets button to see the code at work.
Once the data is transferred, the code will take you directly to the selected sheet.

Let us know what you think.

Cheerio,
vcoolio.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Dear Friend,
Thanks so much for your time and help! Your answer was just what i was looking for to set up my workbook.
Friends
Thanks for your time and help! your answer got me right where i needed to go.
Posts
1292
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 7, 2021
230
Hello Lynng,

You're welcome. I'm glad that I was able to help.

Cheerio,
vcoolio.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!