excelthong
Active Member
- Joined
- Jul 13, 2006
- Messages
- 313
Hi forumate.
this table is shown in workbook "admin" >sheet "admin"
i tried to
1. open E:\Extract_3.11.xlsm (cell A2)
2. open E:\2020.01.20 - DB2.xlsx (cell B2) > copy column A:N > paste to Extract_3.11 >sheet1>column A:N
3. open E:\2020.01.20 - Process.xlsm (cell C2) > copy column AA:AI > paste to Extract_3.11>sheet1 >column AA:AI
4. Run function "Purchashing"
5. Save the workbook Extract_3.11 ->E:\2020.01.20 - 3.10Process.xlsm (cell D2)
and repeat the steps from Row 2 to Row 10 ( cell H3 to H4)
however by defining
Set wb2 = Workbooks("Extract_3.11")
i can't run this code
wb2.Sheets("Sheet1").Select
Columns("A:A").Select
can some expert help me on this issue?
perhaps can help me on the "save as" issue too
thank you
this table is shown in workbook "admin" >sheet "admin"
i tried to
1. open E:\Extract_3.11.xlsm (cell A2)
2. open E:\2020.01.20 - DB2.xlsx (cell B2) > copy column A:N > paste to Extract_3.11 >sheet1>column A:N
3. open E:\2020.01.20 - Process.xlsm (cell C2) > copy column AA:AI > paste to Extract_3.11>sheet1 >column AA:AI
4. Run function "Purchashing"
5. Save the workbook Extract_3.11 ->E:\2020.01.20 - 3.10Process.xlsm (cell D2)
and repeat the steps from Row 2 to Row 10 ( cell H3 to H4)
however by defining
Set wb2 = Workbooks("Extract_3.11")
i can't run this code
wb2.Sheets("Sheet1").Select
Columns("A:A").Select
can some expert help me on this issue?
perhaps can help me on the "save as" issue too
thank you
VBA Code:
Sub AutoOpenCopyPaste()
'automation on open source data, copy to formula sheet
Dim wb1 As Workbook, wb2 As Workbook
Dim sh1 As Worksheet, sh2 As Worksheet
'
Set wb1 = Workbooks("Admin")
y = wb1.Sheets("admin").Range("H3").Value
Z = wb1.Sheets("admin").Range("H4").Value
Workbooks.Open (Range("A" & y))
Set wb2 = Workbooks("Extract_3.11")
ActiveWindow.WindowState = xlMinimized
'copy Database1
Workbooks.Open (Range("B" & y))
Columns("A:N").Copy
ActiveWindow.WindowState = xlMinimized
wb2.Sheets("Sheet1").Select
Columns("A:A").Select
Range("A1").Activate
ActiveSheet.Paste
'copy Database2
wb1.Sheets("admin").Select
Workbooks.Open (Range("C" & y))
Sheets("Sheet1").Columns("AA:AI").Copy
ActiveWindow.WindowState = xlMinimized
wb2.Sheets("Sheet1").Select
Columns("AA:AA").Select
Range("AA1").Activate
ActiveSheet.Paste
Purchashing 'Run macro
wb1.Sheets("admin").Select
wb1.Sheets("admin").Range("J3").Value = y + 1
End Sub