How do I copy range from one worksheet and paste into new workbook 2

[citizenzen]

Hello.

I am creating a small application that will allow me to copy specific data from a range of cells within one column of one excel worksheet to a new worksheet in a new workbook. How can I copy that specific data to the new worksheet? I am getting errors on the copy and paste portion. I am getting a Run-time error of 9, Subscript out of range. Also, is there a way to obtain the specific error?

Public Function findSystem()

Dim SysCell As Range
Dim System As String

'Excel Properties
Dim rCells As Range
Dim rRange As Range

Excel.Application.DisplayAlerts = False
Application.ScreenUpdating = False

For Each rCells In Worksheets(1).Range("A13:A49")
   
    If InStr(rCells, "SYSTEM NAME:") Then
    System = Mid(rCells, 23, 255)
       
    'worksheets to be copied    
    Workbooks("olddocument.xls").Activate
    Workbooks("olddocument.xls").Worksheets("20MAR08 Complete").Activate
    
    'Copy worksheet to workbook    
'*****ERROR BEGINS HERE*************
    Worksheets("20MAR08 Complete").Copy Before:=Workbooks("selectedData.xls").Sheets(1)

   'I want to copy the data from the 'System' variable
    
'this is wrong, so I commented it out
   ' Range(System).Copy("selectedData.xls").Sheets (1)
       
    Workbooks("selectedData.xls").Sheets(1).Activate
    Workbooks("selectedData.xls").Sheets(1).Activate.Range("A2:A10").Activate
    
    Exit For
    
    End If
    
   Next
    
On Error Resume Next
    
End Function

 

[SkipVought]

Hi,

" 'I want to copy the data from the 'System' variable..."

You cannot COPY from a variable

Copy Method
See AlsoApplies ToExampleSpecificsCopy method as it applies to the Range object.

Copies the range to the specified range or to the Clipboard.

expression.Copy(Destination)
expression Required. An expression that returns a Range object.

Destination Optional Variant. Specifies the new range to which the specified range will be copied. If this argument is omitted, Microsoft Excel copies the range to the Clipboard.

Copy method as it applies to the ChartArea, ChartObject, ChartObjects, OLEObject, OLEObjects, Point, and Series objects.

Copies the object to the Clipboard. Copies a picture of the point or series to the Clipboard.

expression.Copy
expression Required. An expression that returns one of the above objects.

Copy method as it applies to the Chart, Charts, Sheets, Worksheet, and Worksheets objects.

Copies the sheet to another location in the workbook.

expression.Copy(Before, After)
expression Required. An expression that returns one of the above objects.

Before Optional Variant. The sheet before which the copied sheet will be placed. You cannot specify Before if you specify After.

After Optional Variant. The sheet after which the copied sheet will be placed. You cannot specify After if you specify Before.

Remarks
If you don't specify either Before or After, Microsoft Excel creates a new workbook that contains the copied sheet.

Copy method as it applies to the Shape object.

Copies the object to the Clipboard.

expression.Copy
expression Required. An expression that returns a Shape object.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[Chance1234]

Why do you want to copy the workbook 36 times

Chance,

F, G + 1MSTG

 

[citizenzen]

Hi. I don't want to copy the workbook 36 times. I have a formula that searches for particular text (I am still in the testing phase) and I need to copy all strings of the found text to a Column in new workbook or a new worksheet. So, A13:A49 is the range of cells to search for the string-

If InStr(rCells, "SYSTEM NAME:") Then
System = Mid(rCells, 23, 255)...

System is the variable that contains the search results. I need to select the items from the results and paste them in a new column.

Please advise.

 

[citizenzen]

can you copy the results of a formula to a new worksheet/workbook?

 

[SkipVought]

"I need to select the items from the results and paste them in a new column."

The COPY and PASTE methods are NOT what you need.

You have text in the system variable. Just assign the value to a cell in your column...

Sheets(1).Cells(1,"A").Value = system

or whatever column, row you want.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[citizenzen]

This can be done to place the information in a new workbook too? or is this limited to a worksheet?

 

[SkipVought]

From your code, first, I would not use a loop. I'd use the Find method to locate THE CELL that contains the string you are searching for. You will not need Instr.

Once found, assign the Mid within the Found value directly to ANY range in ANY sheet in ANY workbook....

dim rFound as range
set rfound = wb1.sh1.cells.find("SYSTEM NAME:")
if not rfound is nothing then
   wb2.sh2.cells(1,1).value = mid(rfound.value,23,len(rfound.value)-22)
end if

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[citizenzen]

hmmm....i tried this and I get errors. I can't figure out how to correctly set my current workbook. I get type mismatch error when I set the name of the current workbook and it seems as if nothing can be found in the workbooks.

here's what i did (I have more strings to search for)

Public Function findSystem()

Dim unitService As Range
Dim rCells As Range

'Excel Properties
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim wb1 As Workbook
Dim wb2 As Workbook

'Workbook 1 : larryexcelversionwaimsv2_TG.xls
'Destination Workbook :selectedHeadendData.xls

Set wb1 = ThisWorkbook.ActiveSheet
Set wb2 = Workbooks.Open("selectedHeadendData.xls")

ws1 = wb1.Sheets("20MAR08 Complete")
ws2 = wb2.Sheets("Sheet1")

Set rCells = wb1.ws1.Cells.Find("SYSTEM NAME:")


If Not rCells Is Nothing Then
    wb2.ws2.Cells(1, 2).Value = Mid(rCells.Value, 23, Len(rCells.Value) - 22)
End If


Set unitService = wb1.ws1.Cells.Find("SERVICE:")

If Not unitService Is Nothing Then
    wb2.ws2.Cells(2, 2).Value = Mid(unitService.Value, 20, Len(unitService.Value) - 22)
End If
        
On Error Resume Next
End Function

 

[citizenzen]

what is the -22 for?

 

[SkipVought]

Whats the 255 for? Do you have 255 characters? How about calculating how many are remaining in the referenced string?

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[citizenzen]

the string size varies,but I still don't understand why nothing is being copied to the new workbooks. am i declaring my workbooks incorrectly?

 

[PHV]

Anyway, replace this:
Set wb1 = ThisWorkbook.ActiveSheet
with this:
Set wb1 = ThisWorkbook
or this:
Set wb1 = ActiveWorkbook

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

'[b]do you want a WORKBOOK or a WORKSHEET (ActiveSheet)????[/b]
    Set wb1 = ThisWorkbook.ActiveSheet
'[b]You need the FULL PATH[/b]
    Set wb2 = Workbooks.Open("selectedHeadendData.xls")

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[citizenzen]

i have the workbook. but i also thought i needed to define the worksheet. the source worksheet contains the main data, is this not the case? as of now, while troubleshooting, I am getting an error with:

Set rCells = wb1.ws1.Cells.Find("SYSTEM NAME:")

I defined the worksheet as:

Set ws_source = wb_source.Worksheets("20MAR08 Complete")

 

[PHV]

Public Function findSystem()
Dim unitService As Range
Dim rCells As Range
'Excel Objects
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim wb1 As Workbook
Dim wb2 As Workbook

'Workbook 1 : larryexcelversionwaimsv2_TG.xls
'Destination Workbook :selectedHeadendData.xls

Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open("selectedHeadendData.xls")
Set ws1 = wb1.Sheets("20MAR08 Complete")
Set ws2 = wb2.Sheets("Sheet1")

Set rCells = ws1.Cells.Find("SYSTEM NAME:")
If Not rCells Is Nothing Then
    ws2.Cells(1, 2).Value = Mid(rCells.Value, 23)
End If
Set unitService = ws1.Cells.Find("SERVICE:")
If Not unitService Is Nothing Then
    ws2.Cells(2, 2).Value = Mid(unitService.Value, 20)
End If
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Set rCells = wb1.ws1.Cells.Find("SYSTEM NAME:")

If you have already set ws1 to a sheet in wb1, then you do not need wb1, as that object is implicit in ws1...

Set rCells = [s]wb1.[/s]ws1.Cells.Find("SYSTEM NAME:")

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[citizenzen]

i hate to keep bugging you, but i still get an error on the worksheets. am i not supposed to define the worksheet? the worksheets are an aspect of the workbook, so why am i getting an error on my definition?

'Set ws_source = wb_source.Sheets("20MAR08 Complete")
Set ws_source = wb_source.Worksheets(1)
Set ws_dest = wb_dest.Sheets("Sheet1")

Set rCells = ws_source.Cells.Find("SYSTEM NAME:")

If Not rCells Is Nothing Then
'ERROR (5)- INVALID Procedure Call or Argument
    ws_dest.Cells(1, 2) = Mid(rCells.Value, 23, Len(rCells.Value) - 40)
End If

 

[PHV]

Why not simply this ?
ws_dest.Cells(1, 2) = Mid(rCells.Value, 23)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

You are not posting ALL of your relevant code.

You have not proveded code wher you have defined your variables.

How are you defining your variables?

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}