ben_hogan
03-11-2010, 09:47 AM
Dear Mister(s) Vbax,
I use a Range of Cells to hold information, Ie: on Cell E9 I have =D9&" "&G9, then for E10 I have =D10&" "&G10 and so on and so forth about ten rows down. The Range E10:E25 is formatted for aesthetic purposes with white font and background.
I’m trying to Copy and Paste the information from such Range into another Worksheet. I’ve tried the Paste/ Special /Values and not only the Pasting is done keeping the source formatting, but it also forms a block if information I cannot see even if I reformat the Range to default settings, meaning for background and font color. See the code I'm using at the bottom.
So, could any of you please help me with some code that will help me solve this dilemma? - Note: I would also now need to transpose the pasting.
Thank you,
Marco.-
Code:
Sub go_to()
'
' go_to Macro
'
'
Sheets("Notes Tool").Select
Range("E8:E33").Select
Selection.Copy
Sheets("NOTE JOURNAL2").Select
'Range("A1").Select
Application.SendKeys "^{END}", Wait
Application.SendKeys "^{END}", Wait
'--------------------------------------------
Dim PauseTime, Start, Finish, TotalTime
'If (MsgBox("Press Yes to pause for 5 seconds", 4)) = vbYes Then
PauseTime = 5 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
Finish = Timer ' Set end time.
'-----------------------------------------------
'Application.SendKeys "^"{END}", Wait
Application.SendKeys "{DOWN}", Wait
Application.SendKeys "{DOWN}", Wait
Application.SendKeys "{DOWN}", Wait
Application.SendKeys "{DOWN}", Wait
'Application.SendKeys "{LEFT}", Wait
'Application.SendKeys "{LEFT}", Wait
'Application.SendKeys "{LEFT}", Wait
'Application.SendKeys "{LEFT}", Wait
'Application.SendKeys "{LEFT}", Wait
'Application.SendKeys "{LEFT}", Wait
'Application.SendKeys "{LEFT}", Wait
'Application.SendKeys "{LEFT}", Wait
''Application.SendKeys "{ENTER}", Wait
'Dim PauseTime, Start, Finish, TotalTime
'PauseTime = 5 ' Set duration.
' Start = Timer ' Set start time.
' Do While Timer < Start + PauseTime
' DoEvents ' Yield to other processes.
'Application.SendKeys "{ENTER}", Wait
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Application.SendKeys "^{ENTER}", Wait
Application.SendKeys "{DOWN}", Wait
Application.SendKeys "{DOWN}", Wait
Application.SendKeys "{DOWN}", Wait
Application.SendKeys "{DOWN}", Wait
Application.SendKeys "{DOWN}", Wait
End Sub
I use a Range of Cells to hold information, Ie: on Cell E9 I have =D9&" "&G9, then for E10 I have =D10&" "&G10 and so on and so forth about ten rows down. The Range E10:E25 is formatted for aesthetic purposes with white font and background.
I’m trying to Copy and Paste the information from such Range into another Worksheet. I’ve tried the Paste/ Special /Values and not only the Pasting is done keeping the source formatting, but it also forms a block if information I cannot see even if I reformat the Range to default settings, meaning for background and font color. See the code I'm using at the bottom.
So, could any of you please help me with some code that will help me solve this dilemma? - Note: I would also now need to transpose the pasting.
Thank you,
Marco.-
Code:
Sub go_to()
'
' go_to Macro
'
'
Sheets("Notes Tool").Select
Range("E8:E33").Select
Selection.Copy
Sheets("NOTE JOURNAL2").Select
'Range("A1").Select
Application.SendKeys "^{END}", Wait
Application.SendKeys "^{END}", Wait
'--------------------------------------------
Dim PauseTime, Start, Finish, TotalTime
'If (MsgBox("Press Yes to pause for 5 seconds", 4)) = vbYes Then
PauseTime = 5 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
Finish = Timer ' Set end time.
'-----------------------------------------------
'Application.SendKeys "^"{END}", Wait
Application.SendKeys "{DOWN}", Wait
Application.SendKeys "{DOWN}", Wait
Application.SendKeys "{DOWN}", Wait
Application.SendKeys "{DOWN}", Wait
'Application.SendKeys "{LEFT}", Wait
'Application.SendKeys "{LEFT}", Wait
'Application.SendKeys "{LEFT}", Wait
'Application.SendKeys "{LEFT}", Wait
'Application.SendKeys "{LEFT}", Wait
'Application.SendKeys "{LEFT}", Wait
'Application.SendKeys "{LEFT}", Wait
'Application.SendKeys "{LEFT}", Wait
''Application.SendKeys "{ENTER}", Wait
'Dim PauseTime, Start, Finish, TotalTime
'PauseTime = 5 ' Set duration.
' Start = Timer ' Set start time.
' Do While Timer < Start + PauseTime
' DoEvents ' Yield to other processes.
'Application.SendKeys "{ENTER}", Wait
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Application.SendKeys "^{ENTER}", Wait
Application.SendKeys "{DOWN}", Wait
Application.SendKeys "{DOWN}", Wait
Application.SendKeys "{DOWN}", Wait
Application.SendKeys "{DOWN}", Wait
Application.SendKeys "{DOWN}", Wait
End Sub