Macro won't copy data across worksheets

[titchroberts]

Hello,

I have been looking for an answer and I realise all that numerous types of script can be written for each action. I am producing a guestlist for a large event in which people across a network can access and then enter their details. If someone can help me with the script then I would be really grateful. What I need to happen is:

1. Data is populated in the next available row on another sheet.
2. Specific cells are copied and pasted into a cell in another worksheet. Example - data from D5 in worksheet 1 will be copied into E6 on worksheet 2.
3. Specific cells are copied and pasted into a cell in a third worksheet. Example - date from D35 in worksheet 1 will be copied into E6 on worksheet 3.

I was suggested this script, but it doesn't seem to have an effect:

Sub Copy()

Dim BlankRow As Long
BlankRow = Sheets("Guestlist").Range("<wbr>B65536").End(xlUp).Row + 1

Sheets("ITC Open Day Return Sheet").Range("D5").Value = Sheets("Guestlist").Range("E" & nextRow).Value
Sheets("ITC Open Day Return Sheet").Range("D7").Value = Sheets("Guestlist").Range("F" & nextRow).Value
Sheets("ITC Open Day Return Sheet").Range("D9").Value = Sheets("Guestlist").Range("C" & nextRow).Value
Sheets("ITC Open Day Return Sheet").Range("F5").Value = Sheets("Guestlist").Range("B" & nextRow).Value
Sheets("ITC Open Day Return Sheet").Range("F7").Value = Sheets("Guestlist").Range("D" & nextRow).Value
Sheets("ITC Open Day Return Sheet").Range("D13").Value = Sheets("Guestlist").Range("G" & nextRow).Value
Sheets("ITC Open Day Return Sheet").Range("D14").Value = Sheets("Guestlist").Range("H" & nextRow).Value
Sheets("ITC Open Day Return Sheet").Range("D15").Value = Sheets("Guestlist").Range("I" & nextRow).Value
Sheets("ITC Open Day Return Sheet").Range("D16").Value = Sheets("Guestlist").Range("J" & nextRow).Value
Sheets("ITC Open Day Return Sheet").Range("D17").Value = Sheets("Guestlist").Range("K" & nextRow).Value
Sheets("ITC Open Day Return Sheet").Range("D19").Value = Sheets("Guestlist").Range("L" & nextRow).Value
Sheets("ITC Open Day Return Sheet").Range("D21").Value = Sheets("Guestlist").Range("M" & nextRow).Value
Sheets("ITC Open Day Return Sheet").Range("D26").Value = Sheets("Guestlist").Range("N" & nextRow).Value
Sheets("ITC Open Day Return Sheet").Range("D29").Value = Sheets("Guestlist").Range("O" & nextRow).Value
Sheets("ITC Open Day Return Sheet").Range("D32").Value = Sheets("Guestlist").Range("P" & nextRow).Value
Sheets("ITC Open Day Return Sheet").Range("F26").Value = Sheets("Guestlist").Range("Q" & nextRow).Value
Sheets("ITC Open Day Return Sheet").Range("F29").Value = Sheets("Guestlist").Range("R" & nextRow).Value
Sheets("ITC Open Day Return Sheet").Range("F32").Value = Sheets("Guestlist").Range("S" & nextRow).Value
Sheets("ITC Open Day Return Sheet").Range("D36").Value = Sheets("Guestlist").Range("T" & nextRow).Value
Sheets("ITC Open Day Return Sheet").Range("F36").Value = Sheets("Guestlist").Range("U" & nextRow).Value

nextRow = Sheets("Feeding and Accommodation").Range("B65536"<wbr>).End(x1Up).Row + 1
Sheets("ITC Open Day Return Sheet").Range("D9").Value = Sheets("Feeding and Accommodation").Range("C" & nextRow).Value
Sheets("ITC Open Day Return Sheet").Range("F5").Value = Sheets("Feeding and Accommodation").Range("B" & nextRow).Value
Sheets("ITC Open Day Return Sheet").Range("D44").Value = Sheets("Feeding and Accommodation").Range("E" & nextRow).Value
Sheets("ITC Open Day Return Sheet").Range("D46").Value = Sheets("Feeding and Accommodation").Range("F" & nextRow).Value
Sheets("ITC Open Day Return Sheet").Range("D48").Value = Sheets("Feeding and Accommodation").Range("G" & nextRow).Value
Sheets("ITC Open Day Return Sheet").Range("D50").Value = Sheets("Feeding and Accommodation").Range("H" & nextRow).Value
Sheets("ITC Open Day Return Sheet").Range("D52").Value = Sheets("Feeding and Accommodation").Range("I" & nextRow).Value
Sheets("ITC Open Day Return Sheet").Range("D63").Value = Sheets("Feeding and Accommodation").Range("J" & nextRow).Value
Sheets("ITC Open Day Return Sheet").Range("D68").Value = Sheets("Feeding and Accommodation").Range("K" & nextRow).Value
Sheets("ITC Open Day Return Sheet").Range("D72").Value = Sheets("Feeding and Accommodation").Range("L" & nextRow).Value


End Sub

 

[JackDanIce]

Welcome to the board.

What value does nextRow have when the first line beginning Sheets("ITC Open Day Return Sheet") executes?

It initiates with value 0, so if you've not changed it in your code, you're asking cell D5 in ITC Open Day Return Sheet to have the same value as E0 in Guestlist, yet there is no row with value 0...

 

[titchroberts]

Welcome to the board.

What value does nextRow have when the first line beginning Sheets("ITC Open Day Return Sheet") executes?

It initiates with value 0, so if you've not changed it in your code, you're asking cell D5 in ITC Open Day Return Sheet to have the same value as E0 in Guestlist, yet there is no row with value 0...

Okay, that makes sense. I believed that nextRow meant that the information would populate the next empty row. What should it say?

 

[JackDanIce]

No need to quote back the previous reply. It exists on the thread and can be read, requoting the whole reply just adds unnecessary length to this thread. Unless you have a different perspective?

It seems like you only need a single variable nextRow. See the lines of code that begin nextRow =

Sub Copy()

Dim nextRow As Long

Application.ScreenUpdating = False
Msgbox "Value of nextRow right now is: " & nextRow

nextRow = Sheets("Guestlist").Cells(Rows.count, 5).End(xlUp).row
Msgbox "Value of nextRow right now is: " & nextRow
With Sheets("ITC Open Day Return Sheet")
    .Range("D5").Value = Sheets("Guestlist").Range("E" & nextRow).Value
    .Range("D7").Value = Sheets("Guestlist").Range("F" & nextRow).Value
    .Range("D9").Value = Sheets("Guestlist").Range("C" & nextRow).Value
    .Range("F5").Value = Sheets("Guestlist").Range("B" & nextRow).Value
    .Range("F7").Value = Sheets("Guestlist").Range("D" & nextRow).Value
    .Range("D13").Value = Sheets("Guestlist").Range("G" & nextRow).Value
    .Range("D14").Value = Sheets("Guestlist").Range("H" & nextRow).Value
    .Range("D15").Value = Sheets("Guestlist").Range("I" & nextRow).Value
    .Range("D16").Value = Sheets("Guestlist").Range("J" & nextRow).Value
    .Range("D17").Value = Sheets("Guestlist").Range("K" & nextRow).Value
    .Range("D19").Value = Sheets("Guestlist").Range("L" & nextRow).Value
    .Range("D21").Value = Sheets("Guestlist").Range("M" & nextRow).Value
    .Range("D26").Value = Sheets("Guestlist").Range("N" & nextRow).Value
    .Range("D29").Value = Sheets("Guestlist").Range("O" & nextRow).Value
    .Range("D32").Value = Sheets("Guestlist").Range("P" & nextRow).Value
    .Range("F26").Value = Sheets("Guestlist").Range("Q" & nextRow).Value
    .Range("F29").Value = Sheets("Guestlist").Range("R" & nextRow).Value
    .Range("F32").Value = Sheets("Guestlist").Range("S" & nextRow).Value
    .Range("D36").Value = Sheets("Guestlist").Range("T" & nextRow).Value
    .Range("F36").Value = Sheets("Guestlist").Range("U" & nextRow).Value
End With

nextRow = Sheets("Feeding and Accommodation").cells(rows.count, 2).end(xlup).row + 1
Msgbox "Value of nextRow right now is: " & nextRow
With Sheets("ITC Open Day Return Sheet")
    .Range("D9").Value = Sheets("Feeding and Accommodation").Range("C" & nextRow).Value
    .Range("F5").Value = Sheets("Feeding and Accommodation").Range("B" & nextRow).Value
    .Range("D44").Value = Sheets("Feeding and Accommodation").Range("E" & nextRow).Value
    .Range("D46").Value = Sheets("Feeding and Accommodation").Range("F" & nextRow).Value
    .Range("D48").Value = Sheets("Feeding and Accommodation").Range("G" & nextRow).Value
    .Range("D50").Value = Sheets("Feeding and Accommodation").Range("H" & nextRow).Value
    .Range("D52").Value = Sheets("Feeding and Accommodation").Range("I" & nextRow).Value
    .Range("D63").Value = Sheets("Feeding and Accommodation").Range("J" & nextRow).Value
    .Range("D68").Value = Sheets("Feeding and Accommodation").Range("K" & nextRow).Value
    .Range("D72").Value = Sheets("Feeding and Accommodation").Range("L" & nextRow).Value
End With


Application.ScreenUpdating = True


End Sub

If you're using Excel 2007 or later, the maximum number of rows on a sheet is not 65,536 instead is 1,048,576

 

[titchroberts]

Nope doesn't work. Thanks anyway, I'll try something else.

 

[JackDanIce]

In what way doesn't it work? If you give examples it may help?

 

[Fluff]

@titchroberts
are you trying to copy the data from ITC Open Day Return Sheet to the other 2 sheets?

 

[titchroberts]

@JackDanIce - on certain cells the data is going into random cells on the same worksheet. In others it is going into random cells on a different worksheet. @Fluff - yep, ITC Open Day Return is the master sheet which populates the two other sheets.

 

[Fluff]

In that case you need to swap these round

With Sheets("ITC Open Day Return Sheet")
    [COLOR=#0000ff].Range("D5").Value[/COLOR] = Sheets("Guestlist").Range("E" & nextRow).Value

Should be

With Sheets("ITC Open Day Return Sheet")
    Sheets("Guestlist").Range("E" & nextRow).Value = [COLOR=#0000ff].Range("D5").Value[/COLOR]

This will need to be done for the whole code.