pasting worsheet in a sheet

kmorgan2004

New Member
Joined
Jan 11, 2005
Messages
41
hey guys and gals,

i want to paste a workbook into a sheet however i wasnt it to be after a certain word like points.

can u help

Example:
b4=points

so i want to paste stuff into b5

thanks for the help
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

kmorgan2004

New Member
Joined
Jan 11, 2005
Messages
41
here is my code so far: ( need to change from pasting into sheet points and pasting to b5 to pasting into points sheet but looking for the next cell after the word point numbers is found)

Sub AlligatorCracking()
'
' AlligatorCracking Macro
' Macro recorded 2/21/2005 by Kevin Morgan
'
' Keyboard Shortcut: Ctrl+a
'

Dim DestBook As Workbook, SourceBook As Workbook
Dim DestCell As Range
Dim RetVal As Boolean

'INPUT IMOFRMATION

Sheets("POINTS").Select
Dim SaveAsName As String
SaveAsName = InputBox("SAVE FILE NAME AS?")
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\mor35918\Desktop\" & SaveAsName, FileFormat:= _
xlNormal, CreateBackup:=False

Dim result As String
result = InputBox("WHAT IS THE ROADWAY NAME?")
Range("C1").Value = result

result = InputBox("WHAT IS THE ROADWAY NUMBER?")
Range("C2").Value = result

result = InputBox("WHO INSPECTED THE JOB?")
Range("C3").Value = result

result = InputBox("WHAT WAS THE INSPECTION DATE?")
Range("E3").Value = result


' Turn off screen updating.
Application.ScreenUpdating = False

' Set object variables for the active book and active cell.
Set DestBook = ActiveWorkbook
Set DestCell = ActiveCell

' Show the Open dialog box.
RetVal = Application.Dialogs(xlDialogOpen).Show("*.txt")

' If Retval is false (Open dialog canceled), exit the procedure.
If RetVal = False Then Exit Sub

' Set an object variable for the workbook containing the text file.
Set SourceBook = ActiveWorkbook

' Copy the contents of the entire sheet containing the text file.
Range(Range("a1"), Range("a1").SpecialCells(xlLastCell)).Copy

' Activate the destination workbook and paste special the values
' from the text file.
DestBook.Activate
Sheets("POINTS").Select
Range("B5").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
Depending on where you are getting stuck...

I know that at the very end, there is the line "ActiveSheet.Paste"

This line is uneccessary and likely will not work as you wish.

You also declare "ActiveWorkbook" as two different variables, i.e. source and destination...if these do not change, you are better off declaring exactly what you need in there.

Also, from what I see, and I have not tested your code ...but it seems you are setting variables such as DestCell=ActiveCell. If you do not activate the destination cell (Range "B5" I think is what you want), then whichever cell was last activated will populate the variable DestCell.

If your code runs and stops, post the error that you get, and what part of the above code it hangs up on.
 

kmorgan2004

New Member
Joined
Jan 11, 2005
Messages
41
YOU ARE CORRECT ABOUT NOT NEEDING THE ACTIVE.PASTE

I THINK I HAVE IT RUNNING


THANKS FOR THE HELP
 

Watch MrExcel Video

Forum statistics

Threads
1,123,321
Messages
5,600,950
Members
414,417
Latest member
Nobu

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
Top