Can anyone help me with the following, in trying to create a formula or VBA wich automate the number of copied file.
I will try to explain with an example:
A B C D
circle red 3 Red Circle
square blue 5 Blue Square
triangle pink 1 Pink triangle
based on the number in column C (that is variable) I would like to have the values in D (string created via formula) copied to a specific area in another sheet.
Hope this clarifies a bit. Would be great if someone can help me!
By another sheet do you mean another sheet in the same workbook or in another file?
Based on what could that specific area be found?
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
Yes, same workbook another sheet. In that sheet, the row should be placed in a certain row.
sheet 1 = 1a Asset production
sheet 2 = 1b Upload string
Asset Channel Aligned Filename Keywords Standard image Online 4 Standard_image_online.tif Standard image, online, white Main image Retailer 2 main_image_retailer.jpg main image, retailer, blue
Based on the value in 'aligned' i want the filename + keywords copied in this case 2 and 4 times in a different sheet. I understand that base dup on the different values it is hard to let the formula just automate as the number of rows can vary. That is why i think a sertain place in sheet 2 is nessacary to avoid errors.
Last edited by Aafke; 01-31-2017 at 05:18 AM.
Best to post an example file with both sheets filled so we can see where goes what.
how do I attached that?
Click Go Advanced button and scroll down to Manage Attachments.
Does this help?
Sub Aafke() Dim i As Long For i = 2 To Range("C" & Rows.Count).End(3).row Cells(i, "D").Resize(, 2).Copy Sheets("another sheet").Range("A" & Rows.Count).End(3)(2).Resize(Cells(i, "C").Value) Next i End Sub
Thank you for the script, unfortunately i receive an error "run-time error '9': Subscript out of range. CAn you specify the VBA on the attached file so i can understand where you're referring to? Thanks in advance
What sheet are you copying too. The script is copying too "another sheet". But we need to know the exact destination: otherwise you'll get a subscript out of range. BTW there is no attachment.
attachment in this message. Thank you
Try:
Sub Aafke() Dim i As Long For i = 16 To Range("N" & Rows.Count).End(3).row Cells(i, "N").Resize(, 3).Copy Sheets("1b upload string").Range("D" & Rows.Count).End(3)(2).Resize(Cells(i, "M").Value).PasteSpecial xlPasteValues Next i End Sub
This is great! it works in the example fiel. I will try to transfer this to my original file... so far so good!! Thanks for the effort!
You're welcome. Hope it works out for you.
I can't figure out how I insert this to my original file, when I adjust the values between the " " i keep receiving errors. Is there a way i can share my file privately so you can check what im doing wrong?
You're welcome. Hope it works out for you.
Ah we have the bottleneck.. if there is no value (cell is empty) the script stops. Can we add a addional formatting that when the number "0" is in a cell it means the script skips the row and moves to the next row.
Try this.
Sub Aafke() Dim i As Long For i = 16 To Range("N" & Rows.Count).End(3).Row If Cells(i, "M") <> vbNullString Then Sheets("1b upload string").Range("D" & Rows.Count).End(3)(2) _ .Resize(Cells(i, "M").Value, 3).Value = Cells(i, "N").Resize(, 3).Value End If Next i End Sub
Perfect!! Thanks for your quick answer, it works perfectly!
If I add another column that needs to be copied from the file ( so instead 3 columns starting from N there are 4 columns) what do I need to change? When I change all numbers 3 into a 4 the formula doesn't work anymore.
Can you please assist m on this?
*sorrySub Aafke() Dim i As Long For i = 16 To Range("N" & Rows.Count).End(3).Row If Cells(i, "M") <> vbNullString Then Sheets("1b upload string").Range("D" & Rows.Count).End(3)(2) _ .Resize(Cells(i, "M").Value, 3).Value = Cells(i, "N").Resize(, 3).Value End If Next i End Sub
Last edited by Aafke; 02-08-2017 at 03:43 AM.
Please read Forum Rules regarding the use of Code Tags (Rule#3) when posting code.
Please change your post#19 accordingly.
To answer your question, in the Resize part of the code change 3 to 4 twice.
thanks, sorry for the breaking the rules.. adjusted.
I would like to add another question, if the range "N" is in a specific file (1a template) how do add this. Can i just mention Sheets (1a template) before the range?
Something like this?
Sub Aafke() Dim i As Long, ws As Worksheet Set ws = Sheets("1a template") For i = 16 To ws.Range("N" & ws.Rows.Count).End(3).Row If ws.Cells(i, "M") <> vbNullString Then Sheets("1b upload string").Range("D" & Rows.Count).End(3)(2) _ .Resize(ws.Cells(i, "M").Value, 3).Value = ws.Cells(i, "N").Resize(, 3).Value End If Next i End Sub
That something, is woking perfectly! thanks a lot!!
You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks