Ron;
I got it!!. I was able to do it in the way I need it.
I want to ask you one last question. I need to save the test file as a read
only. I have setup this statement that is working properly in the end of the
program.
'SAVE AS A READ ONLY
SetAttr "C:\test.xls", vbReadOnly
However, since the file was set up as a read only. It is difficult to
continue loading more information. I wonder if I can add another statement at
the start of the program to undo this read only attribute.
Basically, I need to do the following:
1.- Remove the read only attribute. So I will be able to save the test.xls
file
2.- Archive the information in the database (this is the program that you
just gave me).
3.- Set the read only again with: SetAttr "C:\test.xls", vbReadOnly
Could you please help with statement?
Regards.
Maperalia
:
Hi maperalia
I don't think I understand you
Can you explain
--
Regards Ron de Bruin
http://www.rondebruin.nl
Ron;
You were absolutely right. I made a mistake in the typing of sheet1. Well I
fixed it and it is working perfectly!!!!.
However, I noticed that this reads rows and archive row or read columns and
archive columns. I wonder what statement I need to add in the program to read
columns and archive rows in the Test file. Do you think it is possible to do
it?
Thank again for your support.
Maperalia.
:
Hi maperalia
"Run-time error '9':
Subscript out of range"
Then the sheet name is not Sheet1
Maybe the sheet name is different or you have a space in the sheet name
"Sheet1 " for example
--
Regards Ron de Bruin
http://www.rondebruin.nl
Ron;
First of all I want to thank for your kindness in make the detail layout of
the program's steps.. I believe that you must be a professor because you
explained it so well that is no way to get confuse and is easy to understand
it.
I changed just the name and the path of the file only. I kept the sheet's
name as a "Sheet1". However, when I run the program and I got the following
window message:
"Run-time error '9':
Subscript out of range"
Then after I click "Debug" button it is highlighting at the following
statement:
"Lr = LastRow(destWB.Worksheets("Sheet1")) + 1"
I wonder if I am missing something. Could you please tell me where my
mistake is?
Regards.
Maperalia
:
Here it is
If you have problems please post back
********************************
We use an empty row on your input sheet to create links to cells that you want to copy into the database workbook.
We use for example row 20
In cell A20 we enter =C2
In cell B20 we enter =C3
In cell C20 we enter =C4
In cell D20 we enter =F2
In cell E20 we enter =F3
In cell F20 we enter =F4
We can hide that row now if you want
We open the VBA editor with Alt-F11
We use Insert>Module to create a new module
We copy the macro and two functions in this module
'************Start code*******************
Sub copy_to_another_workbook()
Dim sourceRange As Range
Dim destrange As Range
Dim destWB As Workbook
Dim Lr As Long
Application.ScreenUpdating = False
If bIsBookOpen("test.xls") Then
Set destWB = Workbooks("test.xls")
Else
Set destWB = Workbooks.Open("c:\test.xls")
End If
Lr = LastRow(destWB.Worksheets("Sheet1")) + 1
Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A20:F20")
Set destrange = destWB.Worksheets("Sheet1").Range("A" & Lr)
sourceRange.Copy
destrange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
destWB.Close True
Application.ScreenUpdating = True
End Sub
Function bIsBookOpen(ByRef szBookName As String) As Boolean
' Rob Bovey
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
'************End code*******************
Now we must change a few things in the macro
Change "Sheet1" to the name of your input sheet
The range is correct
Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A20:F20")
In my example I use a database workbook named test.xls
Change the name to your file
Set destWB = Workbooks("test.xls")
Change the path and name to your file
Set destWB = Workbooks.Open("c:\test.xls")
Change "Sheet1" to the name of your database sheet in this code line
Set destrange = destWB.Worksheets("Sheet1").Range("A" & Lr)
Now we go back to Excel with Alt-q
Save the file
When you use Alt-F8 you will get your list with macro's
Select "copy_to_another_workbook" and press run
If the database workbook (test.xls) is not open it will open it for you.
--
Regards Ron de Bruin
http://www.rondebruin.nl
Hi maperalia
I will create a step by step for you this evening
--
Regards Ron de Bruin
http://www.rondebruin.nl
Ron;
Thanks for the web pages. However, since my knowledge in VBA is entry level.
I could not find the way to related with my question. I wonder if you can
give a help to automate the program I posted it already.
Thanks in advance.
Maperalia
:
Start here
http://www.rondebruin.nl/copy1.htm
The best thing you can do is to use a row in file A with
=c2, =c3..................... (you can hide that row if you want)
You can copy the cells from that row then with this example
http://www.rondebruin.nl/copy1.htm#workbook
--
Regards Ron de Bruin
http://www.rondebruin.nl
I wonder if you can help me to do a loop in a program to write the following
cells from file "A":
C2, C3, C4, F2, F3, and F4
Then, locate it in the following cells of file "B":
B2, C2, D2, E2, F2, and G2
Basically, the information from the cells in the file "A" will change and
then I want to archive it in the file "B" in the next empty row (B3, C3, D3,
E3, F3, and G3) and so on.
I wrote the macro that does only for the first row (see below). I was not
able to get the loop the write in the next empty row.
Could you please help me with this matter?
Thanks in advance.
Maperalia
'*****************************************************************
Sub CreateDatabase()
Workbooks.Open Filename:="C:\Excel\Creating Database\B.xls"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=[A.xls]Information!R2C3"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=[A.xls]Information!R3C3"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=[A.xls]Information!R4C3"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=[A.xls]Information!R5C3"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=[A.xls]Information!R2C6"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=[A.xls]Information!R3C6"
