ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   macro pasting and moving (https://www.excelbanter.com/excel-programming/432192-macro-pasting-moving.html)

psychlist

macro pasting and moving
 
i'm pasting data from one worksheet to another and trying to paste the next
set of data to the next row in the new worksheet and so on. the macro only
recognizes the (arrow down) button as a specific( e.g. A3) designation
instead of a (down) command. This results in pasting data over itself. ?????

Don Guillett

macro pasting and moving
 
As ALWAYS, post YOUR code for comments and suggestions.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"psychlist" wrote in message
...
i'm pasting data from one worksheet to another and trying to paste the
next
set of data to the next row in the new worksheet and so on. the macro only
recognizes the (arrow down) button as a specific( e.g. A3) designation
instead of a (down) command. This results in pasting data over itself.
?????



Ron de Bruin

macro pasting and moving
 
Hi psychlist

Start here for a possible solution
http://www.rondebruin.nl/copy1.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"psychlist" wrote in message ...
i'm pasting data from one worksheet to another and trying to paste the next
set of data to the next row in the new worksheet and so on. the macro only
recognizes the (arrow down) button as a specific( e.g. A3) designation
instead of a (down) command. This results in pasting data over itself. ?????


psychlist

macro pasting and moving
 


"Don Guillett" wrote:

As ALWAYS, post YOUR code for comments and suggestions.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"psychlist" wrote in message
...
i'm pasting data from one worksheet to another and trying to paste the
next
set of data to the next row in the new worksheet and so on. the macro only
recognizes the (arrow down) button as a specific( e.g. A3) designation
instead of a (down) command. This results in pasting data over itself.
?????


Here's The Macro


Sub Macro7()

'
' Macro7 Macro
'
' Keyboard Shortcut: Ctrl+z
'
ActiveWindow.SmallScroll Down:=12
Range("E30:G30").Select
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("ResilencyTemplate8.06.09.xlsm").Activate
Range("I30:J30").Select
Application.CutCopyMode = False
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Range("D3:E3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("ResilencyTemplate8.06.09.xlsm").Activate
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 44
Range("B54:D54").Select
Application.CutCopyMode = False
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Range("F3:H3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("ResilencyTemplate8.06.09.xlsm").Activate
Range("F54:I54").Select
Application.CutCopyMode = False
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Range("I3:K3").Select
ActiveSheet.Paste
Windows("ResilencyTemplate8.06.09.xlsm").Activate
Range("K54:M54").Select
Application.CutCopyMode = False
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Range("M3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A4").Select
Application.CutCopyMode = False

End Sub


Don Guillett

macro pasting and moving
 
You said ROWS when you are pasting to COLUMNS. Did you send the wrong code.
What do you want?????

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"psychlist" wrote in message
...


"Don Guillett" wrote:

As ALWAYS, post YOUR code for comments and suggestions.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"psychlist" wrote in message
...
i'm pasting data from one worksheet to another and trying to paste the
next
set of data to the next row in the new worksheet and so on. the macro
only
recognizes the (arrow down) button as a specific( e.g. A3) designation
instead of a (down) command. This results in pasting data over itself.
?????


Here's The Macro


Sub Macro7()

'
' Macro7 Macro
'
' Keyboard Shortcut: Ctrl+z
'
ActiveWindow.SmallScroll Down:=12
Range("E30:G30").Select
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("ResilencyTemplate8.06.09.xlsm").Activate
Range("I30:J30").Select
Application.CutCopyMode = False
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Range("D3:E3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("ResilencyTemplate8.06.09.xlsm").Activate
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 44
Range("B54:D54").Select
Application.CutCopyMode = False
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Range("F3:H3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("ResilencyTemplate8.06.09.xlsm").Activate
Range("F54:I54").Select
Application.CutCopyMode = False
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Range("I3:K3").Select
ActiveSheet.Paste
Windows("ResilencyTemplate8.06.09.xlsm").Activate
Range("K54:M54").Select
Application.CutCopyMode = False
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Range("M3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A4").Select
Application.CutCopyMode = False

End Sub



psychlist

macro pasting and moving
 
I'm attempting to paste a set of data to row A2 to m2 then paste the next set
of data to A3 to M3 and so on...

"Don Guillett" wrote:

You said ROWS when you are pasting to COLUMNS. Did you send the wrong code.
What do you want?????

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"psychlist" wrote in message
...


"Don Guillett" wrote:

As ALWAYS, post YOUR code for comments and suggestions.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"psychlist" wrote in message
...
i'm pasting data from one worksheet to another and trying to paste the
next
set of data to the next row in the new worksheet and so on. the macro
only
recognizes the (arrow down) button as a specific( e.g. A3) designation
instead of a (down) command. This results in pasting data over itself.
?????


Here's The Macro


Sub Macro7()

'
' Macro7 Macro
'
' Keyboard Shortcut: Ctrl+z
'
ActiveWindow.SmallScroll Down:=12
Range("E30:G30").Select
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("ResilencyTemplate8.06.09.xlsm").Activate
Range("I30:J30").Select
Application.CutCopyMode = False
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Range("D3:E3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("ResilencyTemplate8.06.09.xlsm").Activate
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 44
Range("B54:D54").Select
Application.CutCopyMode = False
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Range("F3:H3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("ResilencyTemplate8.06.09.xlsm").Activate
Range("F54:I54").Select
Application.CutCopyMode = False
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Range("I3:K3").Select
ActiveSheet.Paste
Windows("ResilencyTemplate8.06.09.xlsm").Activate
Range("K54:M54").Select
Application.CutCopyMode = False
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Range("M3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A4").Select
Application.CutCopyMode = False

End Sub




Rick Rothstein

macro pasting and moving
 
Given what you have now said you want to do, I think the easiest thing for
us to be able to follow is if you would chart out what is being copied where
(noting clearly the workbook names, sheet names and cell addresses). Do this
for 3 of your destination rows so we can see exactly how you are moving
through your source data to put its information into the destination. I
think if you do that, we will be able to give you code to accomplish what
you want.

--
Rick (MVP - Excel)


"psychlist" wrote in message
...
I'm attempting to paste a set of data to row A2 to m2 then paste the next
set
of data to A3 to M3 and so on...

"Don Guillett" wrote:

You said ROWS when you are pasting to COLUMNS. Did you send the wrong
code.
What do you want?????

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"psychlist" wrote in message
...


"Don Guillett" wrote:

As ALWAYS, post YOUR code for comments and suggestions.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"psychlist" wrote in message
...
i'm pasting data from one worksheet to another and trying to paste
the
next
set of data to the next row in the new worksheet and so on. the
macro
only
recognizes the (arrow down) button as a specific( e.g. A3)
designation
instead of a (down) command. This results in pasting data over
itself.
?????

Here's The Macro

Sub Macro7()
'
' Macro7 Macro
'
' Keyboard Shortcut: Ctrl+z
'
ActiveWindow.SmallScroll Down:=12
Range("E30:G30").Select
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("ResilencyTemplate8.06.09.xlsm").Activate
Range("I30:J30").Select
Application.CutCopyMode = False
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Range("D3:E3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("ResilencyTemplate8.06.09.xlsm").Activate
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 44
Range("B54:D54").Select
Application.CutCopyMode = False
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Range("F3:H3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("ResilencyTemplate8.06.09.xlsm").Activate
Range("F54:I54").Select
Application.CutCopyMode = False
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Range("I3:K3").Select
ActiveSheet.Paste
Windows("ResilencyTemplate8.06.09.xlsm").Activate
Range("K54:M54").Select
Application.CutCopyMode = False
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Range("M3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A4").Select
Application.CutCopyMode = False

End Sub





Don Guillett

macro pasting and moving
 
You need to give more info but this is the idea with the macro executed from
the source wb and sheet with the macro. No need to go back and forth. This
opens the destination file and copies to that file from the source file.

Sub copyvaluestoxlsXfile()
Db = "ResilencyAggregate.xlsx"
ds= "sheet1"
sb = ActiveWorkbook.Name
ss = ActiveSheet.Name

Workbooks.Open Filename:=(Db)
Sheets(ds).Select

'copy block to next available row
lr = Cells(Rows.Count, 1).End(xlUp).Row + 1
'MsgBox lr
With Workbooks(sb).Sheets(ss)
Cells(lr, "b").Resize(, 3).Value = .Range("E30:G30").Value
Cells(lr, "d").Resize(, 3).Value = .Range("I30:J30").Value
Cells(lr, "f").Resize(, 3).Value = .Range("b54:d54").Value
Cells(lr, "i").Resize(, 4).Value = .Range("f54:i54").Value
Cells(lr, "m").Resize(, 3).Value = .Range("k54:l54").Value
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"psychlist" wrote in message
...
I'm attempting to paste a set of data to row A2 to m2 then paste the next
set
of data to A3 to M3 and so on...

"Don Guillett" wrote:

You said ROWS when you are pasting to COLUMNS. Did you send the wrong
code.
What do you want?????

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"psychlist" wrote in message
...


"Don Guillett" wrote:

As ALWAYS, post YOUR code for comments and suggestions.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"psychlist" wrote in message
...
i'm pasting data from one worksheet to another and trying to paste
the
next
set of data to the next row in the new worksheet and so on. the
macro
only
recognizes the (arrow down) button as a specific( e.g. A3)
designation
instead of a (down) command. This results in pasting data over
itself.
?????

Here's The Macro

Sub Macro7()
'
' Macro7 Macro
'
' Keyboard Shortcut: Ctrl+z
'
ActiveWindow.SmallScroll Down:=12
Range("E30:G30").Select
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("ResilencyTemplate8.06.09.xlsm").Activate
Range("I30:J30").Select
Application.CutCopyMode = False
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Range("D3:E3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("ResilencyTemplate8.06.09.xlsm").Activate
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 44
Range("B54:D54").Select
Application.CutCopyMode = False
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Range("F3:H3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("ResilencyTemplate8.06.09.xlsm").Activate
Range("F54:I54").Select
Application.CutCopyMode = False
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Range("I3:K3").Select
ActiveSheet.Paste
Windows("ResilencyTemplate8.06.09.xlsm").Activate
Range("K54:M54").Select
Application.CutCopyMode = False
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Range("M3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A4").Select
Application.CutCopyMode = False

End Sub





Don Guillett

macro pasting and moving
 
Sub copyvaluestoxlsXfileTweaked()
Db = "ResilencyAggregate.xlsx"
ds = "Sheet1"
sb = ActiveWorkbook.Name
ss = ActiveSheet.Name

Workbooks.Open Filename:=(Db)
Sheets(ds).Select

'copy block to next available row
lr = Cells(Rows.Count, "a").End(xlUp).Row + 1
'MsgBox lr
With Workbooks(sb).Sheets(ss)
Cells(lr, "a").Resize(, 3).Value = .Range("E30:G30").Value
Cells(lr, "d").Resize(, 2).Value = .Range("I30:J30").Value
Cells(lr, "f").Resize(, 2).Value = .Range("b54:d54").Value
Cells(lr, "i").Resize(, 4).Value = .Range("f54:i54").Value
Cells(lr, "m").Resize(, 2).Value = .Range("k54:l54").Value
End With
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
You need to give more info but this is the idea with the macro executed
from the source wb and sheet with the macro. No need to go back and forth.
This opens the destination file and copies to that file from the source
file.

Sub copyvaluestoxlsXfile()
Db = "ResilencyAggregate.xlsx"
ds= "sheet1"
sb = ActiveWorkbook.Name
ss = ActiveSheet.Name

Workbooks.Open Filename:=(Db)
Sheets(ds).Select

'copy block to next available row
lr = Cells(Rows.Count, 1).End(xlUp).Row + 1
'MsgBox lr
With Workbooks(sb).Sheets(ss)
Cells(lr, "b").Resize(, 3).Value = .Range("E30:G30").Value
Cells(lr, "d").Resize(, 3).Value = .Range("I30:J30").Value
Cells(lr, "f").Resize(, 3).Value = .Range("b54:d54").Value
Cells(lr, "i").Resize(, 4).Value = .Range("f54:i54").Value
Cells(lr, "m").Resize(, 3).Value = .Range("k54:l54").Value
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"psychlist" wrote in message
...
I'm attempting to paste a set of data to row A2 to m2 then paste the next
set
of data to A3 to M3 and so on...

"Don Guillett" wrote:

You said ROWS when you are pasting to COLUMNS. Did you send the wrong
code.
What do you want?????

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"psychlist" wrote in message
...


"Don Guillett" wrote:

As ALWAYS, post YOUR code for comments and suggestions.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"psychlist" wrote in message
...
i'm pasting data from one worksheet to another and trying to paste
the
next
set of data to the next row in the new worksheet and so on. the
macro
only
recognizes the (arrow down) button as a specific( e.g. A3)
designation
instead of a (down) command. This results in pasting data over
itself.
?????

Here's The Macro

Sub Macro7()
'
' Macro7 Macro
'
' Keyboard Shortcut: Ctrl+z
'
ActiveWindow.SmallScroll Down:=12
Range("E30:G30").Select
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("ResilencyTemplate8.06.09.xlsm").Activate
Range("I30:J30").Select
Application.CutCopyMode = False
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Range("D3:E3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("ResilencyTemplate8.06.09.xlsm").Activate
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 44
Range("B54:D54").Select
Application.CutCopyMode = False
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Range("F3:H3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("ResilencyTemplate8.06.09.xlsm").Activate
Range("F54:I54").Select
Application.CutCopyMode = False
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Range("I3:K3").Select
ActiveSheet.Paste
Windows("ResilencyTemplate8.06.09.xlsm").Activate
Range("K54:M54").Select
Application.CutCopyMode = False
Selection.Copy
Windows("ResilencyAggregate.xlsx").Activate
Range("M3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A4").Select
Application.CutCopyMode = False

End Sub







All times are GMT +1. The time now is 08:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com