Dameon8888
New Member
- Joined
- Apr 30, 2019
- Messages
- 2
Hello!
I was given a user "form" (an Excel Sheet with cells designated to enter info) and tasked to be able to "push" the data from the "FORM" page to the "Upload" Page.
The form has two basic versions - one where all of the cells are have direct correlations to the Upload sheet, and another where there are about 6 variable cells, and the rest is fairly static data.
Version 1: Robert will start on April 1st and End on April 15th, with these 9 other pieces of data
Version 2: Robert and Sue and Jeff and George will start on April 1st and End on April 15th, with these 9 other pieces of data
So, I need to be able to (easily) copy and paste the version 1 data onto the upload sheet. The data from both sheets are not continuous. For Version 1, I've done cell references on the "Upload" tab, so it's just one copy/paste.
I'm running into problems with the Version 2, where I need to loop through the data.
I'm able to get the loop to work, BUT, because I use "Lastrow" to reference the same row for multiple pasting, when the data loops through a second time (or up to 15 times), it doesn't go DOWN to another row.
My guess is that I need to put something just before "Next n"... but I just can't figure out what it is...
Code:
Any assistance would be GREATLY appreciated!
Cheers!
Robert
I was given a user "form" (an Excel Sheet with cells designated to enter info) and tasked to be able to "push" the data from the "FORM" page to the "Upload" Page.
The form has two basic versions - one where all of the cells are have direct correlations to the Upload sheet, and another where there are about 6 variable cells, and the rest is fairly static data.
Version 1: Robert will start on April 1st and End on April 15th, with these 9 other pieces of data
Version 2: Robert and Sue and Jeff and George will start on April 1st and End on April 15th, with these 9 other pieces of data
So, I need to be able to (easily) copy and paste the version 1 data onto the upload sheet. The data from both sheets are not continuous. For Version 1, I've done cell references on the "Upload" tab, so it's just one copy/paste.
I'm running into problems with the Version 2, where I need to loop through the data.
I'm able to get the loop to work, BUT, because I use "Lastrow" to reference the same row for multiple pasting, when the data loops through a second time (or up to 15 times), it doesn't go DOWN to another row.
My guess is that I need to put something just before "Next n"... but I just can't figure out what it is...
Code:
Code:
Sub MoveData()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim rng As Range
Lastrow = Worksheets("Upload").Cells(Rows.Count, "A").End(xlUp).Row + 1
Nextrow = Worksheets("Upload").Cells(Rows.Count, "A").End(xlUp).Row + 2
Set rng = Worksheets("Upload").Range("A1", "CS1")
Dim n As Integer
Dim r As Integer
Dim EndNumber As Integer
EndNumber = Worksheets("FORM").Range("C8").Value
If Worksheets("FORM").Range("C8").Value = "1" Then
Worksheets("Upload").Range("A1", "CS1").Copy
Worksheets("Upload").Cells(Lastrow, 1).PasteSpecial Paste:=xlPasteValues
Else
For n = 31 To EndNumber + 31 - 1
Worksheets("Upload").Range("A1", "E1").Copy
Worksheets("Upload").Cells(Lastrow, 1).PasteSpecial Paste:=xlPasteValues
Worksheets("FORM").Cells(n, 9).Copy
Worksheets("Upload").Cells(Lastrow, 6).PasteSpecial Paste:=xlPasteValues
Worksheets("FORM").Cells(n, 2).Copy
Worksheets("Upload").Cells(Lastrow, 7).PasteSpecial Paste:=xlPasteValues
Worksheets("FORM").Cells(n, 3).Copy
Worksheets("Upload").Cells(Lastrow, 8).PasteSpecial Paste:=xlPasteValues
Worksheets("FORM").Cells(n, 7).Copy
Worksheets("Upload").Cells(Lastrow, 9).PasteSpecial Paste:=xlPasteValues
Worksheets("FORM").Cells(n, 8).Copy
Worksheets("Upload").Cells(Lastrow, 10).PasteSpecial Paste:=xlPasteValues
Worksheets("Upload").Range("K1", "AI1").Copy
Worksheets("Upload").Cells(Lastrow, 11).PasteSpecial Paste:=xlPasteValues
'Enter the Date the Person Left Position into Reviewer Comments
Worksheets("FORM").Cells(n, 10).Copy
Worksheets("Upload").Cells(Lastrow, 36).PasteSpecial Paste:=xlPasteValues
Worksheets("Upload").Range("AK1", "CS1").Copy
Worksheets("Upload").Cells(Lastrow, 37).PasteSpecial Paste:=xlPasteValues
Next n
End If
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Your records have been saved. Continue to enter additional requests, or send for Processing.", , "Success!"
End Sub
Any assistance would be GREATLY appreciated!
Cheers!
Robert