Vba : Find select offset copy and paste

[Atik]

Please help me with a macro.
I have added a attach file.
In sheet 1
red letters are the number of week. Every left cell contains the payment amount of that week.
I need to arrange all payment of specific week in sheet two.
an example -sheet 1, row 1, the value of A1 will go to sheet 2, row 1, column 33. I mean cell AG1.
another example -sheet 1, row 2, the value of A2, C2 will go to sheet 2, row 2, column separating by a comma. I mean cell AC2 fill with $268.00,$244.00
Next cell E2 will go to sheet 2, row2, cell AD2
Next cell G2 will go to sheet 2, row2, cell AL2
in this way cell A3 sheet 1 will go to cell AL3 sheet 2,
Please help me, I need this.
Book1.xlsx

[HaHoBe]

Hi, Atik,

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Ciao,
Holger

[MarvinP]

Hi Atik,

I can't give you my answer until you fix your thread title to something like "Indirect Copy to Sheet2". If you Edit your question and change the title I can give you my answer.
Private Message me if/when you fix your thread title as I have already written the VBA but need you to follow the rules before I can give the answer.

I'm off to bed now (Seattle, WA, USA time) so I need a PM to remember to give you the answer, tomorrow.

[MarvinP]

Hi Atik,

Find the attached where I've written VBA to copy your sheet1 data to Sheet2. You should look at sheet2 to see what it does. If you need to run it again you should clear the contents of sheet2 first. I didn't do exactly like you asked, where you wanted comma between each value. I think it is much more useful in the format I've given. On sheet3 I've copied sheet2 and pasted it, Transposed, and then inserted a column A where I've totaled each row. I think this is where you are really going with this problem. Here is the code and example.

Sub MoneyWeek()
    Dim LastRow As Double
    Dim RowCtr As Double
    Dim ColCtr As Double
    Dim ColPlace As Double
    Dim Last2Row As Double
    
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For RowCtr = 1 To LastRow
        For ColCtr = 1 To Cells(RowCtr, Columns.Count).End(xlToLeft).Column Step 2
            ColPlace = Cells(RowCtr, ColCtr + 1).Value
            Last2Row = Worksheets("Sheet2").Cells(Rows.Count, ColPlace).End(xlUp).Row + 1
            Cells(RowCtr, ColCtr).Copy Destination:=Worksheets("Sheet2").Cells(Last2Row, ColPlace)
        Next ColCtr
    Next RowCtr
End Sub

[Atik]

Sir,
Thanks for your response.
In sheet 1. Each row contain the payment of one customer. I need to paste the value in the same row in sheet two. example,
In sheet 1 row 10 contain the payment of Mr Jack and I need that amount in row 10, on sheet 2 in respective week. Cell A10 and C10 will go to cell S10. separated by comma or sum of this two value. Cell E10, will go to U10. I want to say that I need to keep the payment amount in the same number of row from where I have copied it.
I'm waiting for your response.

[MarvinP]

OK Atik,

Here is the new code that must be run while on Sheet 1. You should clear all the contents on Sheet4 before running the code.

Sub MoneyWeek()
    Dim LastRow As Double
    Dim RowCtr As Double
    Dim ColCtr As Double
    Dim ColPlace As Double
    Dim Last2Row As Double
    
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For RowCtr = 1 To LastRow
        For ColCtr = 1 To Cells(RowCtr, Columns.Count).End(xlToLeft).Column Step 2
            ColPlace = Cells(RowCtr, ColCtr + 1).Value
            Worksheets("Sheet4").Cells(RowCtr, ColPlace) = Worksheets("Sheet4").Cells(RowCtr, ColPlace) + _
            Cells(RowCtr, ColCtr)
        Next ColCtr
    Next RowCtr
End Sub

See the attached where I've run the code and look at sheet4 to see if it is ok. You can insert a row at the top and put in your week numbers is you want.

Is this what you needed now?

[Atik]

Yes, Thank you very much sir, I need this.