Transfer data
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Transfer data
Hi
Thanks for looking at my post.
I have attached my query in the word document.
Really appreciate your time and help.
It will be great if some one could help please.
Thanks again
Jim
Thanks for looking at my post.
I have attached my query in the word document.
Really appreciate your time and help.
It will be great if some one could help please.
Thanks again
Jim
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transfer data
Code: Select all
Sub TransferData()
Dim wbs As Workbook
Dim wbt As Workbook
Dim wss As Worksheet
Dim wst As Worksheet
Dim s As Long
Dim m As Long
Dim t As Long
Application.ScreenUpdating = False
Set wbs = ActiveWorkbook ' Source
Set wss = wbs.Worksheets(1)
Set wbt = Workbooks("Destination.xlsx") ' Destination
Set wst = wbt.Worksheets(1)
t = 1
m = wss.Range("A" & wss.Rows.Count).End(xlUp).Row
For s = 2 To m
If wss.Range("C" & s).Value = "Class 2" Then
wst.Range("Q" & t).Resize(1, 12).Value = wss.Range("D" & s).Resize(1, 12).Value
Else
t = t + 1
wst.Range("B" & t).Resize(1, 14).Value = wss.Range("B" & s).Resize(1, 14).Value
End If
Next s
Application.ScreenUpdating = True
End Sub
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer data
Thanks a lot for your reply and really really appreciate it.
First of all really thank you for your time and effort :)
I just have one issue when the destination id is not in sequence with source id the data is not transferring according to the id number. Believe it is transferring according from row to row. I have highlighted in the destination workbook. Row 2 and 12 i have swapped to id to check correct data is transferring from source to destination , but unfortunately it does not.
Any idea about this please.
This help means lot :)
Thanks again
First of all really thank you for your time and effort :)
I just have one issue when the destination id is not in sequence with source id the data is not transferring according to the id number. Believe it is transferring according from row to row. I have highlighted in the destination workbook. Row 2 and 12 i have swapped to id to check correct data is transferring from source to destination , but unfortunately it does not.
Any idea about this please.
This help means lot :)
Thanks again
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer data
Thanks for your reply Hans.
Is there any way of doing filter with matching id?
Because I have 100s of data sometimes more than one I'd repeat that's why.
Thanks again
Is there any way of doing filter with matching id?
Because I have 100s of data sometimes more than one I'd repeat that's why.
Thanks again
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transfer data
Since there can be more than one row with the same ID, that would be difficult.
An alternative would be to simply populate the entire destination sheet instead of relying on already present IDs.
An alternative would be to simply populate the entire destination sheet instead of relying on already present IDs.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer data
That will be ideal.
Any idea of how to do that please?
Do I need to modify outstanding code?
Sorry as I am not good in vba
Any help will be appreciated
Thanks again
Any idea of how to do that please?
Do I need to modify outstanding code?
Sorry as I am not good in vba
Any help will be appreciated
Thanks again
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transfer data
The destination sheet only needs to contain the headers in row 1. See the attached workbooks.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer data
That's fantastic Hans. You are genius... Much appreciated your work.
One last question is what if I want to transfer only to visible rows please?
One last question is what if I want to transfer only to visible rows please?
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transfer data
Like this:
Code: Select all
Sub Button1_Click()
Dim wbs As Workbook
Dim wbt As Workbook
Dim wss As Worksheet
Dim wst As Worksheet
Dim s As Long
Dim m As Long
Dim t As Long
Dim t2 As Long
Application.ScreenUpdating = False
Set wbs = ActiveWorkbook ' Source
Set wss = wbs.Worksheets(1)
Set wbt = Workbooks("Destination.xlsx") ' Destination
Set wst = wbt.Worksheets(1)
t = 1
m = wss.Range("A" & wss.Rows.Count).End(xlUp).Row
For s = 2 To m
If wss.Range("A" & s).EntireRow.Hidden = False Then
If wss.Range("C" & s).Value = "Class 2" Then
t2 = wst.Range("A:A").Find(What:=wss.Range("A" & s).Value, LookAt:=xlWhole).Row
wst.Range("P" & t2).Resize(1, 13).Value = wss.Range("C" & s).Resize(1, 13).Value
Else
t = t + 1
wst.Range("A" & t).Resize(1, 15).Value = wss.Range("A" & s).Resize(1, 14).Value
End If
End If
Next s
Application.ScreenUpdating = True
End Sub
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer data
Unbelievable....
Great works hans
Thanks a lot for your time and input.
Much appreciated
Great works hans
Thanks a lot for your time and input.
Much appreciated
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer data
Sorry Hans just one last question as I have not explained you properly. Really sorry.
What if I want to transfer only to visible cells in the destination workbook please?
Really sorry.
Just point me where I need to change in the code that should be fine.
Really apologies
What if I want to transfer only to visible cells in the destination workbook please?
Really sorry.
Just point me where I need to change in the code that should be fine.
Really apologies
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer data
What I mean is I wanted to hide some rows in destination workbook. So when I wanted to transfer data from source workbook to destination I don't want to transfer to hidden row please.
Example row 10 to 20 is hidden in destination workbook. When I transfer from source workbook to destination, row 11 of source workbook will be from row 21 in the destination.
Thanks a lot
Example row 10 to 20 is hidden in destination workbook. When I transfer from source workbook to destination, row 11 of source workbook will be from row 21 in the destination.
Thanks a lot
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transfer data
Code: Select all
Sub Button1_Click()
Dim wbs As Workbook
Dim wbt As Workbook
Dim wss As Worksheet
Dim wst As Worksheet
Dim s As Long
Dim m As Long
Dim t As Long
Dim t2 As Long
Application.ScreenUpdating = False
Set wbs = ActiveWorkbook ' Source
Set wss = wbs.Worksheets(1)
Set wbt = Workbooks("Destination.xlsx") ' Destination
Set wst = wbt.Worksheets(1)
t = 1
m = wss.Range("A" & wss.Rows.Count).End(xlUp).Row
For s = 2 To m
If wss.Range("A" & s).EntireRow.Hidden = False Then
If wss.Range("C" & s).Value = "Class 2" Then
t2 = wst.Range("A:A").Find(What:=wss.Range("A" & s).Value, LookAt:=xlWhole).Row
wst.Range("P" & t2).Resize(1, 13).Value = wss.Range("C" & s).Resize(1, 13).Value
Else
Do
t = t + 1
Loop Until wst.Range("A" & t).EntireRow.Hidden = False
wst.Range("A" & t).Resize(1, 15).Value = wss.Range("A" & s).Resize(1, 14).Value
End If
End If
Next s
Application.ScreenUpdating = True
End Sub
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer data
Fantastic Hans
Really thanks for your kind help
Thanks again
Really thanks for your kind help
Thanks again