How to copy data from one column to other column using VBA

[zaska]

Hello,

I am looking for a VBA solution to ease my routine work in the following scenario.

1.The output from web query is loaded into "Master" Sheet.
2. In "Raw Data" sheet I used x lookup formula to get the required data for each column header from the "Master sheet"

What I want in "Raw Data" sheet is to copy the data from xlookup formula column and paste as values in the next cells every time I run the web query with new date. This I want to do for a max period of 22 days.

I am herewith attaching the excel file wherein I have created 22 blank columns for each header data in "Raw Data" Sheet highlighted in yellow color. I want to copy the data to adjacent column for each header in the master sheet.

Any help is much appreciated.

Thank you

[zaska]

Can anyone help to solve the problem?

[Marc L]

According to your attachment a VBA demonstration as a beginner starter :

Sub Demo1() 
    With Sheets("RawData").UsedRange.Rows 
        With .Item("3:" & .Count).Columns 
        For C% = 2 To 255 Step 23 
            If IsEmpty(.Cells(1, C + 22)) Then .Item(.Cells(1, C + 22).End(xlToLeft)(1, 2).Column) = .Item(C).Value 
        Next 
        End With 
    End With 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[zaska]

According to your attachment a VBA demonstration as a beginner starter :

Sub Demo1() 
    With Sheets("RawData").UsedRange.Rows 
        With .Item("3:" & .Count).Columns 
        For C% = 2 To 255 Step 23 
            If IsEmpty(.Cells(1, C + 22)) Then .Item(.Cells(1, C + 22).End(xlToLeft)(1, 2).Column) = .Item(C).Value 
        Next 
        End With 
    End With 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Thank you for the code. Once all the 22 columns are filled there are no empty columns. How to accomadate the new data in the first column so that I always have 22 days of data with new data updated

[Marc L]

As the new data are updated via a XLOOKUP formula - so nothing to do ! - or
it depends on what you really need which does not seem to match with this thread subject …
Anyway thanks for the rep' !

[zaska]

I ran the code for 22 days i.e 22 times so all the 22 columns got filled. Now for the 23rd day I have new data in the xlookup formula column. As there are no empty cells now the code will exit.

What I want to do is to copy the new data to the next column so that the data gets shifted to the next column rewriting the old data in column 22

Thank you.

[Marc L]

Add an Else to the condition in order to copy the data where you want …