MACRO HELP PLEASE

[Neil Holden]

Morning, below you will see the code for when a button is pressed it copies
the range of the cells into an external excel sheet, however the problem I
face now is column A and B need to be pasted as values and its not. Any help
on the revised code will be much appreciated.

Dim lngRow As Long, rngTemp As Range
Dim wbBook As Workbook, wsDest As Worksheet

Set rngTemp = ActiveSheet.Range("A12:Q75")

'Range("A12:Q75").Select

Selection.Copy
Set wbBook = Workbooks.Open("C:\Documents and
Settings\neil.holden\Desktop\test2.xls")
Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
lngRow = wsDest.Cells(Rows.Count, "B").End(xlUp).Row + 1
rngTemp.Copy wsDest.Range("A" & lngRow)


wbBook.Close True

 

[Patrick Molloy]

you're pasteing to the same range that you're copying from and while you're
tried to use objects, you've a few errors.

Dim lngRow As Long, rngTemp As Range
Dim wbBook As Workbook, wsDest As Worksheet

Set rngTemp = ActiveSheet.Range("A12:Q75")

Set wbBook = Workbooks.Open("C:\Documents and
Settings\neil.holden\Desktop\test2.xls")
Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet
WITH rngTemp
WsDest.Range("A1").Resize(.Rows.Count).Value = .Value
END WITH
wbBook.Close True

 

[Neil Holden]

HI Patrick, thanks for your message.

I have tried your code and now it only paste column A and nothing else.

 

[Per Jessen]

Hi Neil

Try this, notice I first paste the entire range, then pasteSpecial column
A:B:

Sub aaa()
Dim lngRow As Long, rngTemp As Range
Dim wbBook As Workbook, wsDest As Worksheet

Set rngTemp = ActiveSheet.Range("A12:Q75")
Set wbBook = Workbooks.Open _
("C:\Documents and Settings\neil.holden\Desktop\test2.xls")
Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet

lngRow = wsDest.Cells(Rows.Count, "B").End(xlUp).Row + 1
rngTemp.Copy wsDest.Range("A" & lngRow)
wsDest.Range("A1", Range("B" & lngRow)).Copy
wsDest.Range("A1").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
wbBook.Close True
End Sub

Regards,
Per

 

[Neil Holden]

hi Per, thanks for that, now i'm getting an error message Run Time error 1004
method range of object worksheet failed and highlights:

wsDest.Range("A1", Range("B" & lngRow)).Copy in yellow.

Neil.

 

[Patrick Molloy]

doesn't work as a function, but it does as a SUB

Option Explicit
Sub test()
ResolveFormula Range("D6")
End Sub

Sub ResolveFormula1(rngCell As Range)
Dim sCell As String
Dim rngPrec As Range
Dim index As Long
sCell = rngCell.Formula
index = 64
For Each rngPrec In rngCell.DirectPrecedents
index = index + 1
sCell = Replace(sCell, rngPrec.Address(False, False), Chr(index))
Next
msgbox sCell

End Sub

 

[Patrick Molloy]

please ignore this - i replied to the wrong query, doh ...

 

[Patrick Molloy]

change
WsDest.Range("A1").Resize(.Rows.Count).Value = .Value
to
WsDest.Range("A1").Resize( .Rows.Count, .Columns.Count).Value = .Value

 

[Jacob Skaria]

Replace the last line

<<rngTemp.Copy wsDest.Range("A" & lngRow)

with

wsDest.Range("A" & lngRow).Resize(rngTemp.Rows.Count, _
rngTemp.Columns.Count) = rngTemp.Value

If this post helps click Yes

 

[Neil Holden]

Patrick, that works thanks but i need it to keep adding information, not
overwrite the previous information.

Thanks alot.

 

[Neil Holden]

That works to some extent but for some reason have 76 rows of information and
its pasting 126? Also, it copies to the external excel sheet from the very
first row, i need it to start row 2 as i have heading in the external sheet.
Sorry to be a pain i'm so close!!

 

[Jacob Skaria]

From your code it is difficult to understand since you have used
Selection.Copy. What is the selection? Instead refer that as a range
object...

If this post helps click Yes

 

[Neil Holden]

This is my current code.

Dim lngRow As Long, rngTemp As Range
Dim wbBook As Workbook, wsDest As Worksheet

Set rngTemp = ActiveSheet.Range("A13:Q75")
Set wbBook = Workbooks.Open("C:\Documents and
Settings\neil.holden\Desktop\test2.xls")
Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet
With rngTemp
wsDest.Range("A2").Resize(.Rows.Count, .Columns.Count).Value = .Value
lngRow = wsDest.Cells(Rows.Count, "A").End(xlUp).Row + 1

'wsDest.Range("A" & lngRow).Resize(rngTemp.Rows.Count, _
rngTemp.Columns.Count) = rngTemp.Value



End With
wbBook.Close True

 

[Jacob Skaria]

'I dont find any issue with the code. The below code will copy rngTemp to
Desitnation worksheet Range("A2")

wsDest.Range("A2").Resize(rngTemp.Rows.Count, _
rngTemp.Columns.Count) = rngTemp.Value


'OR the below code will copy rngTemp to Desitnation worksheet Col A last
unused row.

lngRow = wsDest.Cells(Rows.Count, "A").End(xlUp).Row + 1
wsDest.Range("A" & lngRow).Resize(rngTemp.Rows.Count, _
rngTemp.Columns.Count) = rngTemp.Value


If this post helps click Yes

 

[Joel]

One problem with your code is the source and destination sizes are not the
same. Try copying from two different areas on your worksheet and you will
see that it won't work the way you are expecting.

You can copy from 1 cell to many cells (rows, columns) without any problems
or you can select as your source a large area but always chosse as the
desination eith one cell, one row, or one column. Never select the source
and destination multiple size areas that are different sizes.

 

[Patrick Molloy]

thats called scope change

so with the original information on the destination sheet, do you want it
pushed down ro to the right?


add this line:

With rngTemp
wsDest.Range("A1").Resize(.Rows.Count, .Columns.Count).Insert
xlShiftDown
wsDest.Range("A1").Resize(.Rows.Count, .Columns.Count).Value = .Value
End With