• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help to create a loop to copy and paste data in a certain way

Ajay Gaikwad

New Member
I am struggling with a loop from past 3 days, finally requesting help from the community.

I want to each cell in column M from (input sheet) to output sheet in column BE multiple times based on the rows in sheet3 and then continue to copy the next cell in column M (input sheet) and copy it in output sheet in column BE below the already copied data. This should run till the last value in column M input sheet. Below is the real scenario i am facing

I have 63 rows (62 + 1 header) with value in column M of input sheet (the number of rows are not constant and should be flexible) I want to copy the value in cell M2 of the input sheet and then copy in the output sheet in column BE (row 1 is header). The number of times this value should be pasted should be based on the row count in Sheet3 Range B7 until last filled row (in this case I have 4 rows in sheet3 so the value should be pasted 4 times in output sheet). Then it should move to cell M3 in the input sheet and copy it same number of times in the next available row in Column BE of the output sheet.

This way it should copy till the last row in column M of input sheet to the output sheet in col BE, the same number of times as row available in sheet3 B7 onwards.

Result to check would be 62 (cells in input sheet) * 4 (rows in sheets 3 B7 onwards) = 248 (in output sheet column BE)

Thank you very much in advance.
 

Ajay Gaikwad

New Member
Hi @vletm

Thank you for respnding. I have copied my code that i have tried so far. Hope this helps

>>> use code - tags <<<
Code:
Sub Allocation()

'Raw data sheet lets us know how many times to copy, here B6 onwards down
'UBRSplit is the input sheet where we need to copy value from M2 onwards
'Working sheet is the output where the paste is required from BE2 onwards

Application.ScreenUpdating = False

Dim i As Integer
Dim r As Integer

i = Sheets("Raw Data").Range("B5").End(xlDown).Offset(1, 0).Row

For r = 2 To i

Sheets("UBRSplit").Select
    Range("M2").Select
    Selection.Copy


Sheets("Working sheet").Select
Range("BE" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial

Sheets("UBRSplit").Select
Next

End Sub
 

Attachments

vletm

Excel Ninja
Ajay Gaikwad
I wrote Without clear Excel sample file...
Do You have an idea, how others could verify / test Your challenge without it?
You're pasting Your code, which won't work - as You have written.
There should be as well as Your expected result based Your sample file.
 

vletm

Excel Ninja
Ajay Gaikwad
These are Your sheets - okay?
Screenshot 2021-05-28 at 12.30.58.png
You refer to ... input- and output-sheets ... hmm? ... which maybe could guess?
... and Sheet3, that can find.
Each of those sheets has M & BE-column.
You have the real scenario ... but ... hmm?
Where is Your scenario's expected result based Your given sample data?
 

Ajay Gaikwad

New Member
Hi,

I have just explained them as input/ output for explanation purpose. However ubrsplit sheet is our input sheet (column m), raw data sheet is sheet3 where number of rows are counted so that same number of times the data is copied in working sheet (output sheet) in column BE
 

Ajay Gaikwad

New Member
Hi @vletm
IfIf I run the macro which is currently build by me in the sheet. Then it only copies m2 from ubrsplit sheet to BE in working sheet 4 times (B2:B5). But then it's not going to the next cell in ubrsplit ie M3 and onwards.
 

vletm

Excel Ninja
Ajay Gaikwad
I didn't get those sheets - now, those are more - I won't even guess.
Did You reply to my the last question.
Your code question - as I've written - it would do something else that You would like to get - I won't use that.
 

Ajay Gaikwad

New Member
Hi @vletm

I am sorry if i am confusing you. let me explain again on the issue i am facing. Please refer to module 3 for my code

I am trying to copy each used cell in column M of UBRSplit sheet (except header) to column BE row 2 onwards in the working sheet. The number of times this should be copied in column BE depends on the rows present in Raw data sheet from A6 onwards down (4 rows in this case - so copy pasting should happen 4 times).

Current output:

Cell M2 from UBRSplit sheet is being copied 4 times in working sheet in column BE and then it stops there.

74813
Expected output:

After the M2 being copied X times based on numbers of rows in raw data sheet (B6 onwards), then it should move to M3, then M4 and so on till last used row in column M in UBRSplit sheet.

74814

Note: No of used rows in UBRSplit sheet in column M may very and no of rows in raw data may also vary so it should be flexible.

Thank you @vletm
 

Attachments

Ajay Gaikwad

New Member
Ajay Gaikwad
C... something ... if I have to ask same question many times because no clear answer.
Finally, I could guess that I could do my sample code.
Press [ Do It ]-button in UBRSplit -sheet.
Note: This sample won't clean 'oldies'.
Thank you very much for your help @vletm , It is working perfectly. very grateful for the solution provided.
 
Top