Pasting Columns of Values and Rows of Dates in a Macro

[cheembucket]

Hi there, I'm currently trying to take a source sheet of values and use a macro to paste a subset of the original sheet to a new sheet.

I want to make a Dim kpi_var for the columns of KPIs, and then I want to paste the rows of dates and their respective values for the kpi_vars.

I'm trying to loop through the kpi_var columns to paste them into the target sheet, and loop through the dates to paste them in the rows.

The button for the macro will clear the sheet and start pasting the columns and rows.

Is anyone able to help me with some sample code that I can try to implement in my code? It doesn't seem to work properly right now.

Source Sheet:

sourcemacro1.PNG

Target Sheet (for ingestion to a SQL server):

sourcemacro2.PNG

[jdelano]

You can just loop through the rows on one sheet and write them to the other - it might be something like

Dim sourceRow as Long
Dim sourceCol as Integer
Dim destinationRow as Long
dim lastRowCol as String 

' clear the data on the destination sheet
lastRowCol = Split(Sheet1.UsedRange.Address, "$")(3) ' extract the last row and col used on sheet2
Sheet2.Range("A2:" & lastRowCol).Clear

destinationRow = 2
For sourceRow = 5 to Sheet1.UsedRange.Rows.Count
    For sourceCol = 1 to Sheet1.UsedRange.Columns.Count
        Sheet2.Cells(destinationRow, sourceCol).Value = Sheet1.Cells(sourceRow, sourceCol).Value
        destinationRow = destinationRow + 1
    Next sourceCol
Next sourceRow

This was just typed straight in here and might not work for you from the start