MACRO HELP PLEASE

N

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
 
P

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
 
N

Neil Holden

HI Patrick, thanks for your message.

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

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
 
N

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.
 
P

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
 
P

Patrick Molloy

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

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
 
N

Neil Holden

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

Thanks alot.
 
N

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!!
 
J

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
 
N

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
 
J

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
 
J

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.
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

MACRO HELP CONTINUED 3
MACRO help 9
EXCEL CONNECTED WITH OUTLOOK 1
Coding issue with Excel 2003 1
Macro Help 1
My Macro is slower than the turtle 1
need help on refining find record macro 2
Combine 3 Macros 4

Top