Open workbook ,copy &paste, save as

excelthong

Active Member
Joined
Jul 13, 2006
Messages
313
Hi forumate.
1587345492559.png

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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,751
Hi excelthong. U can trial this untested code. It looks like it might work although I hate using copy/paste. HTH. Dave
Code:
Option Explicit
Sub AutoOpenCopyPaste()
'automation on open source data, copy to formula sheet
Dim wb1 As Workbook, wb2 As Workbook, Y As Integer
Dim sh1 As Worksheet, sh2 As Worksheet, Z As Integer
Dim Cnt As Integer, Lastrow As Double

Y = Sheets("admin").Range("H3").Value
Z = Sheets("admin").Range("H4").Value
For Cnt = Y To Z
Set wb1 = Workbooks.Open(CStr(Sheets("admin").Range("A" & 2)))
'copy Database1
Set wb2 = Workbooks.Open(CStr(Sheets("admin").Range("B" & Cnt)))
Lastrow = wb2.Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
wb2.Sheets("sheet1").Range("A1:N" & Lastrow).Copy
wb1.Sheets("sheet1").Cells(1, "A").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Workbooks(wb2.Name).Close SaveChanges:=False
'copy Database2
Set wb2 = Workbooks.Open(CStr(Sheets("admin").Range("C" & Cnt)))
Lastrow = wb2.Sheets("sheet1").Range("AA" & Rows.Count).End(xlUp).Row
wb2.Sheets("sheet1").Range("AA1:AI" & Lastrow).Copy
wb1.Sheets("sheet1").Cells(1, "AA1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Workbooks(wb2.Name).Close SaveChanges:=False

Call Purchashing 'Run macro
'save wb
wb1.SaveAs Filename:=CStr(Sheets("admin").Range("D" & Cnt))
Workbooks(wb1.Name).Close SaveChanges:=False
Next Cnt
End Sub
 

excelthong

Active Member
Joined
Jul 13, 2006
Messages
313
Hi @NdNoviceHlp

Thank you for helping, really appreciate your help.
VBA Code:
Set wb1 = Workbooks.Open(CStr(Sheets("admin").Range("A" & 2)))
ActiveWindow.WindowState = xlMinimized

i have added a line to minimize the Extract_3.11 workbook so that i can continue to run the code, else it wont detect sheet "admin"
then the first part of the code run smoothly

VBA Code:
'copy Database2
Set wb2 = Workbooks.Open(CStr(Sheets("admin").Range("C" & Cnt)))
'Lastrow = wb2.Range("AA" & Rows.Count).End(xlUp).Row
Columns("F:N").Copy
wb1.Sheets("sheet1").Cells(1, "AA1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Workbooks(wb2.Name).Close SaveChanges:=False

but when come to 'copy database2
i have been stucked with
VBA Code:
Set wb2 = Workbooks.Open(CStr(Sheets("admin").Range("C" & Cnt)))

it seems like the code cant detect sheets"admin", the last active sheet is Extract_3.11
so at debug mode, i need to manually click on the "admin sheet", then the code will run again

and the database2 has slight changed, the sheet name is not "sheet1" but is same as file name, which is
open E:\2020.01.20Process.xlsm (cell C2) > select sheet"2020.01.20Process">copy column F:N > paste to Extract_3.11>sheet1 >column AA:AI

i tried to disable 'Lastrow = wb2.Range("AA" & Rows.Count).End(xlUp).Row
and just using Columns("F:N").Copy,
however i cant paste the column to Extract_3.11 (it cant detect Extract_3.11)

i know my coding is very raw, hope you can help on this
thank you very much again !
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,751
Trial #2. Dave
Code:
Sub AutoOpenCopyPaste()
'automation on open source data, copy to formula sheet
Dim wb1 As Workbook, wb2 As Workbook, Y As Integer
Dim sh1 As Worksheet, sh2 As Worksheet, Z As Integer
Dim Cnt As Integer, Lastrow As Double

Y = Sheets("admin").Range("H3").Value
Z = Sheets("admin").Range("H4").Value
On Error GoTo ErFix
Application.ScreenUpdating = False
For Cnt = Y To Z
Set wb1 = Workbooks.Open(CStr(Sheets("admin").Range("A" & 2)))
'copy Database1
Set wb2 = Workbooks.Open(CStr(Sheets("admin").Range("B" & Cnt)))
Lastrow = wb2.Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
wb2.Sheets("sheet1").Range("A1:N" & Lastrow).Copy
wb1.Sheets("sheet1").Cells(1, "A").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Workbooks(wb2.Name).Close SaveChanges:=False
'copy Database2
Set wb2 = Workbooks.Open(CStr(Sheets("admin").Range("C" & Cnt)))
Lastrow = wb2.Sheets("2020.01.20Process").Range("AA" & Rows.Count).End(xlUp).Row
wb2.Sheets("2020.01.20Process").Range("AA1:AI" & Lastrow).Copy
wb1.Sheets("sheet1").Cells(1, "AA1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Workbooks(wb2.Name).Close SaveChanges:=False

Call Purchashing 'Run macro
'save wb
wb1.SaveAs Filename:=CStr(Sheets("admin").Range("D" & Cnt))
Workbooks(wb1.Name).Close SaveChanges:=False
Next Cnt
ErFix:
Application.ScreenUpdating = True
End Sub
 

excelthong

Active Member
Joined
Jul 13, 2006
Messages
313

ADVERTISEMENT

Hi Dave
thank you for your response
VBA Code:
On Error GoTo ErFix
Application.ScreenUpdating = False
For Cnt = Y To Z
Set wb1 = Workbooks.Open(CStr(Sheets("admin").Range("A" & 2)))
ActiveWindow.WindowState = xlMinimized

i still need to minimized the Extract_3.11 using "ActiveWindow.WindowState = xlMinimized "before i can run the codes

and the code stop running at
VBA Code:
Set wb2 = Workbooks.Open(CStr(Sheets("admin").Range("C" & Cnt)))
it will go back to On Error GoTo ErFix

and i realize another problem that the filename of database2 is variable not permanently "2020.01.20Process"
so Sheets("2020.01.20Process") may not work when go to next cell C3

thank you again
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,751
Trial placing...
Code:
DoEvents
before where the code stops. Did U really change the sheet name to "2020.01.20Process" instead of the file name in "C" ie. "2020.01.20 - Process"? You really shouldn't need to minimize the active window after opening the wb? Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,126,983
Messages
5,621,966
Members
415,869
Latest member
LWSkinner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top