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