Repeated copy/paste

[Lost Paul]

Hi all,

I'll try to explain this so it can be understood :)

We have data from one source, that has to be uploaded to a customer hub.
The organisation of the hub does not match ours (of course).

The main difference is that each 'time stamp' has it's own record in the hub, along with the other reference data and its own description (event type).
Of course, we have the time stamps as 'fields' thereby keeping the number of records down.

So I'm looking at how to copy column by column from one sheet to another (ready for hub upload).
Did consider Pivot table, but I couldn't make it work.

I've attached a sample, showing the start of the copy paste.

So the sequence would be;

• 1 Copy values from 'DD3 CDI 6-15' column R (usually many more records)
  2 Paste these into 'Hub Upload3' column A
  3 Copy values from 'DD3 CDI 6-15' column J
  4 Paste these into 'Hub Upload3' column E
  5 Copy values from 'DD3 CDI 6-15' column M
  6 Paste these into 'Hub Upload3' column Y (repeat for cols N, O AA & AD from 'DD3 CDI 6-15' into 'Hub Upload3')
  
  7 Copy values from 'DD3 CDI 6-15' column S
  8 Find the first blank row in 'Hub Upload3' column A and paste values
  9 Copy values from 'DD3 CDI 6-15' column K
  10 Find the first blank row in 'Hub Upload3' and paste these into 'Hub Upload3' column E
  
  And repeat

So, which is the best way?
I can record a macro for the majority, but there are a few steps I can't seem to get.
Particularly;
* Finding the first blank row.
* Copying the column headers into the relevant cells

[ChrisGreaves]

... So the sequence would be:-
1 Copy values; 2 Paste these; 3 Copy values; 4 Paste these; 5 Copy values; 6 Paste these; 7 Copy values; 8 Find the; 9 Copy values; 10 Find the; 11 And repeat. I can record a macro for the majority, but there are a few steps I can't seem to get.

A central maxim of mine is that “Computer are Good At Doing Boring and Repetitive Tasks”, whose corollary is “If It’s Boring and Repetitive, Write a Macro”.
That you are considering writing a macro to achieve this work is commendable, and I would say that 80% of the battle is won, providing that you have convinced those-who-write-pay-cheques that a macro is the way to go.

You mention a couple of hurdles "Finding the first blank row" and "Copying the column headers into the relevant cells".
The first could be achieved with modifications to

Code: Select all

    Selection.End(xlDown).Select
    Range.Offset(1, 0).Select

I used Selection rather than Range so that you can watch it work by single-stepping through the code.

If you have got code written and tested for all but those two hurdles, I'd recommend recording a FindFirstBlankRow macro and perfecting it in isolation on your data sheet.

Then let's attack(grin) the second hurdle.

Cheers
Chris

[Lost Paul]

Thank you.

That is certainly more elegant than what I came up with.
I put in a counta+1 and used that ID the first cell for pasting into

Code: Select all

Sub NxtRw()
'Find 1st blank row
Dim Rw As Integer
Rw = Range("AH1")
Range("E" & Rw).Select 'changing the "E" to whatever as required.
End Sub

So next....

[HansV]

Here is such a macro:

Code: Select all

Sub CopyData()
    Dim wshS As Worksheet
    Dim wshT As Worksheet
    Dim m As Long
    Dim c As Long
    Dim t As Long
    Application.ScreenUpdating = False
    Set wshS = Worksheets("DD3 CDI 6-15")
    Set wshT = Worksheets("Hub Upload3")
    m = wshS.Cells(wshS.Rows.Count, 5).End(xlUp).Row
    t = 2
    ' Loop through columns F to L of the source sheet
    For c = 6 To 12
        wshT.Cells(t, 1).Resize(m - 3).Value = wshS.Cells(1, c).Value
        wshT.Cells(t, 4).Resize(m - 3).Value = wshS.Cells(4, 5).Resize(m - 3).Value
        wshT.Cells(t, 5).Resize(m - 3).Value = wshS.Cells(4, c).Resize(m - 3).Value
        wshT.Cells(t, 25).Resize(m - 3).Value = wshS.Cells(4, 13).Resize(m - 3).Value
        wshT.Cells(t, 26).Resize(m - 3).Value = wshS.Cells(4, 14).Resize(m - 3).Value
        wshT.Cells(t, 27).Resize(m - 3).Value = wshS.Cells(4, 15).Resize(m - 3).Value
        wshT.Cells(t, 30).Resize(m - 3).Value = wshS.Cells(4, 16).Resize(m - 3).Value
        t = t + m - 3
    Next c
    Application.ScreenUpdating = True
End Sub

[Lost Paul]

Thank you Hans.

I'll have to check that out later.

I will feedback when I can.
cheers

[Lost Paul]

Had a quick check; looks perfect.

Thank you

[Lost Paul]

Hi again,

All is working fine - thank you.

Can you 'walk me through' how the code works please?
I'm going to have to do a few of these.

TIA

[HansV]

I have added comments to the code - does that help? Feel free to ask questions.

Code: Select all

Sub CopyData()
    ' Declare variables
    Dim wshS As Worksheet ' The source sheet
    Dim wshT As Worksheet ' The target sheet
    Dim m As Long         ' The last used row on the source sheet
    Dim c As Long         ' The column number on the source sheet - loops from 6 to 12
    Dim t As Long         ' The row to start pasting on the target sheet
    ' Temporarily 'freeze' the screen for efficiency
    Application.ScreenUpdating = False
    ' Reference to source sheet
    Set wshS = Worksheets("DD3 CDI 6-15")
    ' Reference to target sheet
    Set wshT = Worksheets("Hub Upload3")
    ' Get the last used row in column E of the source sheet
    m = wshS.Cells(wshS.Rows.Count, 5).End(xlUp).Row
    ' The starting row number on the target sheet
    t = 2
    ' Loop through columns F to L of the source sheet
    For c = 6 To 12
        ' For each of those columns, copy the column header to column A of the target sheet
        ' Since the source data start in row 4, the number of rows is m - 3
        wshT.Cells(t, 1).Resize(m - 3).Value = wshS.Cells(1, c).Value
        ' Copy the values in column E to column D
        wshT.Cells(t, 4).Resize(m - 3).Value = wshS.Cells(4, 5).Resize(m - 3).Value
        ' Copy the values in the current loop column to column E
        wshT.Cells(t, 5).Resize(m - 3).Value = wshS.Cells(4, c).Resize(m - 3).Value
        ' Copy the values in column M to column Y
        wshT.Cells(t, 25).Resize(m - 3).Value = wshS.Cells(4, 13).Resize(m - 3).Value
        ' Copy the values in column N to column Z
        wshT.Cells(t, 26).Resize(m - 3).Value = wshS.Cells(4, 14).Resize(m - 3).Value
        ' Copy the values in column O to column AA
        wshT.Cells(t, 27).Resize(m - 3).Value = wshS.Cells(4, 15).Resize(m - 3).Value
        ' Copy the values in column P to column AD
        wshT.Cells(t, 30).Resize(m - 3).Value = wshS.Cells(4, 16).Resize(m - 3).Value
        ' Increase the row number on the target sheet for the next round
        t = t + m - 3
    Next c
    ' Update the screen again
    Application.ScreenUpdating = True
End Sub

[Lost Paul]

Thank you