Using a Macro to copy & Paste from one sheet to another.

Solved/Closed
Marksman702 Posts 3 Registration date Monday December 15, 2014 Status Member Last seen December 17, 2014 - Dec 15, 2014 at 09:08 AM
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 - Dec 18, 2014 at 05:19 PM
Hello,

Hi Guy's I hope you can help, I am using two sheets. one is spread across different rows
so when I click create list it copies everything to my list on my new worksheet,

Now what I want it to do is copy and paste to the next row, and so on because my first sheet is the initial one to complete and then it is cleared.

I'm New to these forums so please be patient with me. see the below

Sub Create_List()
'
' Create_List Macro
'

'
Sheets("Single SDR").Select
Range("C2").Select
Selection.Copy
Sheets("SDR list").Select
Range("F4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("C3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("C4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("C4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("D4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("C5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("E4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("C7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("B4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("C12:F12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("G4:J4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("K8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("L4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("M8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("M4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("I13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("N4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("K13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
Range("O4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("L13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("P4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("D19:H19").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("R4:V4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("O18:O19").Select
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.UnMerge
Range("O18").Select
With Selection.Font
.Name = "Calibri"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("O19").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlHairline
End With
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("O18").Select
Selection.Copy
Sheets("SDR list").Select
Range("W4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("I27").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("X4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("I26").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("Y4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
Range("I24").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("AA4").Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
Sheets("Single SDR").Select
Range("I25").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("AB4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 41
Range("D58:E59").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("AC4").Select
Range("AB7").Select
Sheets("Single SDR").Select
Range("D64:E65").Select
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.UnMerge
Range("D58:E59").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.UnMerge
Range("D64").Select
Selection.Copy
Sheets("SDR list").Select
Range("AD4").Select
Sheets("Single SDR").Select
Range("J73").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("AE4").Select
Sheets("Single SDR").Select
ActiveWindow.SmallScroll Down:=-45
Range("I29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("AF4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
ActiveWindow.SmallScroll Down:=30
Range("D64").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("AD4").Select
ActiveSheet.Paste
Sheets("Single SDR").Select
ActiveWindow.SmallScroll Down:=15
Range("J73").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("SDR list").Select
Range("AE4").Select
ActiveSheet.Paste
Range("B05").Select
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Sheets("Single SDR").Select
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1



End

7 replies

vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
Dec 15, 2014 at 04:50 PM
Hello Marksman,

Looks like you've busy trying to work this one out!

To simplify and better understand exactly what you would like, please upload a sample work book. Please desensitise any confidential information.

Use a file sharing site such as DropBox to do this.

Regards,
vcoolio.
1
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
Dec 16, 2014 at 10:10 PM
Hello Mark,

Just letting you know that I haven't forgotten you. I'll be tackling your case again tonight when I get home from work. I went through it last night with a fine-tooth comb (well, fairly fine anyway!).

Just wondering, did you record the current macro with the macro recorder?

Regards,
vcoolio.
1
Marksman702 Posts 3 Registration date Monday December 15, 2014 Status Member Last seen December 17, 2014 1
Dec 17, 2014 at 04:44 AM
Hi Coolio,

Yes mate I used the Macro recorder, it is a little messy, I will tidy it up a little : )
1
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
Dec 17, 2014 at 05:15 AM
Hello Mark,

That's ok. Leave it as it is. Sorting through it for you. Will be in touch.

Cheers,
vcoolio.
0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
Dec 17, 2014 at 11:47 PM
Hello again Mark,

Click on the following link to retrieve your file:-

https://www.dropbox.com/s/gpezz3vvxwvji8u/Marksman.xlsm?dl=0

I've re-arranged your code so now it transfers data as you wanted - row by row - to the SDR List.

I have made your existing button "Create List" the active cell also so, when you click on it, all the data will be transferred. Its in cell R2. If you ever move the button, you will need to change the cell reference in the code.

Also (and I hope that you don't mind), I had to re-arrange your form ("Single SDR") a little as there were many, many merged cells that created havoc with the code. There are no merged cells now. To trim the code down, I have removed the formatting code from the macro but both sheets are still formatted, and saved, as you had them originally. You can change and save the formatting any time you wish.

I have placed the code in Module 1 as it was not being used.

I hope its now all working as you wanted.

Kind regards,
vcoolio.
1

Didn't find the answer you are looking for?

Ask a question
Hi Coolio,

Thank you very much, I have tidied it up now similar to the original, you were right
that merged cells were causing a problem and found that if you un-merged the titles
the macro would work.

Please see the link

https://www.dropbox.com/s/bs1oo8oom7dsgvt/Trim%20SDR-List%20Creator.xlsm?dl=0

Once again thanks very much, I have also learned a lot from your good work and can use it on other tasks.

Kindest Regards


Mark
1
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
Dec 18, 2014 at 05:19 PM
Hello Mark,

Great tidy up work on the form! I knew you'd make that your first task. It looks great now. I was more interested in unmerging everything.

I'm actually still playing around with the code to try and make it more efficient. I'll let you know if it turns out any better.

Glad that I could help.

Cheers,
vcoolio.
1
Marksman702 Posts 3 Registration date Monday December 15, 2014 Status Member Last seen December 17, 2014 1
Dec 16, 2014 at 06:50 AM
Hi Coolio,

Thanks for the quick reply buddy, please see the link to the file below

https://www.dropbox.com/s/zkn5n4v1g9wu7kr/Copy%20of%20SDR%20list%20test.xlsm?dl=0

Basically the first tab (SDR) is filled in, I click the Create file list and the second worksheet tab 2 is completed. then what I want to be able to do is everytime I create a new SDR and create the list it moves down to row 2 and so on.

I hope the link works ok, or do I need to do it a different way?


Many thanks


Mark
0