PDA

View Full Version : pasting range .offset



chungtinhlak
01-14-2009, 04:53 PM
I have to copy a number of columns from a different file to re-align them. I have a userform that initialize with the default column letter.

Private Sub UserForm_Initialize()
TextBox1.Value = "A" ' EPD number
TextBox2.Value = "C" ' revision

'and so on

i want to copy each of those column over to the worksheet with this code:


Sub workbooksetup()
Dim file path as string
dim wkbmacro as workbook, wkbcognos as workbook
dim i as long

filepath = "file (file://fil-nw-312/QA_787_Vis/Aging)path*"
Set wkbmacro = ThisWorkbook
On Error Resume Next
Application.DisplayAlerts = False
Set wkbcognos = Application.Workbooks.Open(filepath)
Application.DisplayAlerts = True


If Not Range("A1").Value = "EPD Number" Then
Range("1:7").Delete
End If

usedcell = 65536 - Application.WorksheetFunction.CountBlank(Range("A:A")) + 100
MsgBox (usedcell)
For i = 1 To 14
Range(agingcars("textbox" & i).Value & "1:" & agingcars("textbox" & i).Value & usedcell).Copy

' i got it to this point but it doesn't copy over, can someone help me.

wkbmacro.Activate
Range("A1").Offset(, i).Paste
wkbcognos.Activate
Next

lucas
01-14-2009, 11:09 PM
I think this is your problem if I understand correctly:

Range("A1").Offset(, i).Paste


did you just paste it to range A1 to actually see if it is copying to begin with?

What is your objective in the paste operation? To put it in the column from which it was copied?