How do I automatically insert into next blank row..

G

ghrek

Active Member
Joined
Jul 29, 2005
Messages
427
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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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

willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
280
willow1985
willow1985
D
  • Solved
extract to another workbook and save using desktop path
2
Replies
12
Views
191
jolivanes
jolivanes
K
  • Question Question
Increasing row before the macro runs again
Replies
1
Views
211
kbk
K
N
  • Question Question
Adding to an existing MACRO
Replies
2
Views
103
Northern NY Design
N
C
  • Solved
Please help me clean up my VBA script, I'm having trouble with redundancies
Replies
4
Views
441
CapRavOr
C

Forum statistics

Threads
1,223,886
Messages
6,175,191
Members
452,616
Latest member
intern444

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