How do I automatically insert into next blank row..

G

ghrek

Active Member
Joined
Jul 29, 2005
Messages
426
I have 2 workbook that I need to copy and paste special data over.

I know how to copy and paste special normally but what im trying to do is have numerous people copy and paste data automatically into new sheet but also I need it input on next row.

In book called "NEW SUMMARY" you have a sheet called sheet 2. Im trying to copy and paste special from this sheet into certain columns in workbook called "INPUT SCREEN" but always in next empty row.

What I mean is say the workbook called input screen has no data in it. I need the data from the sheet called new summary copied into row 1. Then when someone else runs it I need the data put into row 2 and so on depending on what the next row empty is.

Macrowhat ive done so far shown below


VBA Code: 
Workbooks.Open Filename:= _
        "[URL='https://xxx.sharepoint.com/teams/O365GRP-StationAccounts/Shared%20Documents/General/GLENN/INPUT%20SCREEN.xlsm']https://XXX.sharepoint.com/teams/O365GRP-StationAccounts/Shared%20Documents/General/GLENN/INPUT%20SCREEN.xlsm[/URL]"
    ExecuteExcel4Macro _
        "(""[URL='https://xxx.sharepoint.com/teams/O365GRP-StationAccounts/Shared']https://XXX.sharepoint.com/teams/O365GRP-StationAccounts/Shared[/URL] Documents/General/GLENN/INPUT SCREEN.xlsm"",48)"
    Sheets("INPUT SCREEN").Select
    Windows("NEW SUMMARY.xlsm").Activate
    Range("C7").Select
    Selection.Copy
    Windows("INPUT SCREEN.xlsm").Activate
    Range("J6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("NEW SUMMARY.xlsm").Activate
    Range("C10").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("INPUT SCREEN.xlsm").Activate
    Range("K6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("NEW SUMMARY.xlsm").Activate
    Range("C11").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("INPUT SCREEN.xlsm").Activate
    Range("L6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("NEW SUMMARY.xlsm").Activate
    Range("C12").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("INPUT SCREEN.xlsm").Activate
    Range("M6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("NEW SUMMARY.xlsm").Activate
    Range("C15").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("INPUT SCREEN.xlsm").Activate
    Range("N6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("NEW SUMMARY.xlsm").Activate
    Range("C16").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("INPUT SCREEN.xlsm").Activate
    Range("O6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("NEW SUMMARY.xlsm").Activate
    Range("C18").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("INPUT SCREEN.xlsm").Activate
    Range("P6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("NEW SUMMARY.xlsm").Activate
    Range("C19").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("INPUT SCREEN.xlsm").Activate
    Range("Q6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("NEW SUMMARY.xlsm").Activate
    Range("C21").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("E31").Select
    Windows("INPUT SCREEN.xlsm").Activate
    Range("R6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("NEW SUMMARY.xlsm").Activate
    Range("C24").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("INPUT SCREEN.xlsm").Activate
    Range("S6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 16
    Windows("NEW SUMMARY.xlsm").Activate
    Range("C25").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("INPUT SCREEN.xlsm").Activate
    Range("T6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("NEW SUMMARY.xlsm").Activate
    Range("C26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("INPUT SCREEN.xlsm").Activate
    Range("U6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AA6").Select
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 27
    ActiveWindow.ScrollColumn = 28
    ActiveWindow.ScrollColumn = 29
    Windows("NEW SUMMARY.xlsm").Activate
    Range("G7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("INPUT SCREEN.xlsm").Activate
    Range("AF6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("NEW SUMMARY.xlsm").Activate
    Range("G9").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("INPUT SCREEN.xlsm").Activate
    Range("AG6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("NEW SUMMARY.xlsm").Activate
    Range("G10").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("INPUT SCREEN.xlsm").Activate
    Range("AH6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("NEW SUMMARY.xlsm").Activate
    Range("G11").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("INPUT SCREEN.xlsm").Activate
    Range("AI6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("NEW SUMMARY.xlsm").Activate
    Range("G13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("INPUT SCREEN.xlsm").Activate
    Range("AJ6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 33
    ActiveWindow.ScrollColumn = 34
    ActiveWindow.ScrollColumn = 35
    ActiveWindow.ScrollColumn = 36
    Windows("NEW SUMMARY.xlsm").Activate
    Range("G16").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("INPUT SCREEN.xlsm").Activate
    Range("AK6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("NEW SUMMARY.xlsm").Activate
    Range("G18").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("INPUT SCREEN.xlsm").Activate
    Range("AL6").Select
    ActiveSheet.Paste
    Windows("NEW SUMMARY.xlsm").Activate
    Range("G19").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("INPUT SCREEN.xlsm").Activate
    Range("AM6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("NEW SUMMARY.xlsm").Activate
    Range("G22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("INPUT SCREEN.xlsm").Activate
    Range("AN6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("NEW SUMMARY.xlsm").Activate
    Range("G23").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("INPUT SCREEN.xlsm").Activate
    Range("AO6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("NEW SUMMARY.xlsm").Activate
    Range("G25").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("INPUT SCREEN.xlsm").Activate
    Range("AP6").Select
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollColumn = 35
    ActiveWindow.ScrollColumn = 34
    ActiveWindow.ScrollColumn = 33
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 28
    ActiveWindow.ScrollColumn = 27
    ActiveWindow.ScrollColumn = 28
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 33
    ActiveWindow.ScrollColumn = 34
    ActiveWindow.ScrollColumn = 35
    Windows("NEW SUMMARY.xlsm").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Windows("INPUT SCREEN.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("NEW SUMMARY.xlsm").Activate
    Range("G26").Select
    Application.CutCopyMode = False
    Range("G26").Select
    Selection.Copy
    Windows("INPUT SCREEN.xlsm").Activate
    Range("AQ6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollColumn = 36
    ActiveWindow.ScrollColumn = 37
    ActiveWindow.ScrollColumn = 38
    ActiveWindow.ScrollColumn = 39
    ActiveWindow.ScrollColumn = 40
    Windows("NEW SUMMARY.xlsm").Activate
    ActiveWindow.SmallScroll Down:=6
    Range("G33").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("INPUT SCREEN.xlsm").Activate
    Range("AV6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AW6").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    ActiveWindow.Close
End Sub
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
So you start from the file "NEW SUMMARY.xlsm" and an unknown and correct "active sheet". Let's call it A
Then you open "INPUT SCREEN.xlsm" and select its Sheets("INPUT SCREEN"). Let's call it B
Then you copy from a long list af cells from A and would like to paste the values to B, on the first free position in column J

If that is correct then my proposal is the following macro:
VBA Code: 
Sub Forghrek()
Dim FromA, ToA, I As Long, NextR As Long
'
Workbooks.Open Filename:= _
        "[URL='https://xxx.sharepoint.com/teams/O365GRP-StationAccounts/Shared%20Documents/General/GLENN/INPUT%20SCREEN.xlsm']https://XXX.sharepoint.com/teams/O365GRP-StationAccounts/Shared%20Documents/General/GLENN/INPUT%20SCREEN.xlsm[/URL]"
ExecuteExcel4Macro _
        "(""[URL='https://xxx.sharepoint.com/teams/O365GRP-StationAccounts/Shared']https://XXX.sharepoint.com/teams/O365GRP-StationAccounts/Shared[/URL] Documents/General/GLENN/INPUT SCREEN.xlsm"",48)"
'
'First free row in Sheets("INPUT SCREEN") col J:
NextR = Sheets("INPUT SCREEN").Cells(Rows.Count, "J").End(xlUp).Row + 1
'
'Arrays with sources and destinations:
FromA = Array("C7", "C10", "C11", "C12", "Etc Etc")     '<<< Copy values from these cells
ToA = Array("J", "K", "L", "M", "etc etc")              '<<< Paste values to these columns
'
Workbook("NEW SUMMARY.xlsm").Activate
'
For I = 0 To UBound(FromA)
    Workbooks("INPUT SCREEN.xlsm").Sheets("INPUT SCREEN").Cells(NextR, ToA(I)).Value = Range(FromA(I)).Value
Next I
'
Workbooks("INPUT SCREEN.xlsm").Save
Workbooks("INPUT SCREEN.xlsm").Close False
End Sub
The 2 lines marked <<< need to be completed with (in the correct order) the cell address to copy from and the destination column in B, using the syntax I used for the firs 4 entries.

NextR is the first row available in B

Try...
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

J
  • Question
VBA to select partial filename and go back and forth between two files to copy paste
Replies
1
Views
168
shknbk2
S
willow1985
  • Solved
Find first blank cell in column A in a Table - Runtime error 91
Replies
4
Views
107
willow1985
willow1985
P
  • Solved
Counter in For Each loop
Replies
2
Views
199
Possum17
P
S
  • Solved
VBA does not continue to copy after blank cell
Replies
8
Views
527
jolivanes
jolivanes
A
  • Question
Macro to update list to last row on another sheet copy source from multiple sheets
Replies
0
Views
312
Azamamir
A

Forum statistics

Threads
1,215,073
Messages
6,122,975
Members
449,095
Latest member
Mr Hughes

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top