Copy Columns from one workbook and paste into specific cells in another workbook

[UzmaSKhan116]

Hi,

i am searching for solution but not getting exactly what i am looking for, i want to copy specific columns from one workbook to specific cells in another workbook, and also want to rename workbook and sheet automatically by getting today's name as workbook name?

copying column from one workbook to another workbook works fine , but i want to paste at specific cells on basis of my own criteria.


any suggestions please ?

 

[Yongle]

Will the code reside in the original workbook?
Is the other workbook already open OR do you want the VBA to open it
Is the user navigating to the other workbook OR is this to be automated in VBA?
What is "today's name"?
What is the new sheet name?
Is the user required to select both copy from and paste to ranges when requested by the VBA?

 

[UzmaSKhan116]

Will the code reside in the original workbook?
Is the other workbook already open OR do you want the VBA to open it
Is the user navigating to the other workbook OR is this to be automated in VBA?
What is "today's name"?
What is the new sheet name?
Is the user required to select both copy from and paste to ranges when requested by the VBA?

Yes it resides in original workbook,i want to transfer data by command button on user-form, i want to copy data from 2 different sheets (master sheet , daily-sheet) into a new workbook which i want to open when i click button .
so destination workbook has 2 sheets (Report 1, Report 2).
In report 2 i am transferring all data of master-sheet (up till that point code is working perfectly fine).
but i am facing problem here : (when i am trying to copy multiple columns[B:C:E] from daily sheet into specific cells of report2 [M21,P21,Q21]),

I need help here please.

today's name (sorry it was typo , i meant today's date)

i don't want to take input from user i want to transfer data in both destination sheets from source sheets automatically.

but after transferring data i was just thinking if its possible that workbook name get renamed automatically by taking some stringname + Today's date as a new workbook name.

i will really appreciate your time and help

Thanks

 

[UzmaSKhan116]

Hi,

i am searching for solution but not getting exactly what i am looking for, i want to copy specific columns from one workbook to specific cells in another workbook, and also want to rename workbook and sheet automatically by getting today's name as workbook name?

copying column from one workbook to another workbook works fine , but i want to paste at specific cells on basis of my own criteria.


any suggestions please ?

i am trying like this
Workbooks.Open FileName:="C:\Folder\Report2.xlsm"




'''''''copying data


Workbooks("Original Workbook.xlsm").Activate
Sheets("Sheet1").Select '''Copying data
lr = Range("A" & Rows.Count).End(xlUp).Row
Range("B3:B20" & lr).Copy
Range("B3:B20" & lr).Copy
Range("E3:E20" & lr).Copy


''''''''''pasting data
Workbooks("Workbook destination.xlsm").Activate
Sheets("Sheet1").Select '''''pasting data
Range("M21:M38").PasteSpecial Paste:=xlPasteAll
Range("P21:P38").PasteSpecial Paste:=xlPasteAll
Range("R21:R38").PasteSpecial Paste:=xlPasteAll
Workbooks("Original Workbook.xlsm").Close


for 1 column it works fine but not for multiple ranges in paste cells

 

[UzmaSKhan116]

Workbooks.Open FileName:="C:\Folder\Workbook_destination.xlsm"




'''''''copying data


Workbooks("Original_Workbook.xlsm").Activate
Sheets("Sheet1").Select '''Copying data
lr = Range("A" & Rows.Count).End(xlUp).Row
Range("B3:B20" & lr).Copy
Range("B3:B20" & lr).Copy
Range("E3:E20" & lr).Copy


''''''''''pasting data
Workbooks("Workbook_destination.xlsm").Activate
Sheets("Sheet1").Select '''''pasting data
Range("M21:M38").PasteSpecial Paste:=xlPasteAll
Range("P21:P38").PasteSpecial Paste:=xlPasteAll
Range("R21:R38").PasteSpecial Paste:=xlPasteAll
Workbooks("Original_Workbook.xlsm").Close

 

[Yongle]

Code assumes
- an existing workbook is being opened
- "Daily-Sheet" is always the same sheet
- data in Daily-Sheet starts in row 2 and last value in column B can be used to determine ranges for columns B,C and E

Set mRng = m2.Range("B2", m2.Range("B" & Rows.Count).End(xlUp))
NB: [COLOR=#ff0000]values copied to M and N are column offsets from this range[/COLOR]

You already have working code that does most of what you want so you will probably only want to add Report 2 and Create New Workbook
- if you are having any problem integrating then post your current working code (all of it)

Option Explicit
Sub CreateTodayWorkbook()
    Application.ScreenUpdating = False
'[COLOR=#008080]variables[/COLOR]
    Const templatePath = "[COLOR=#ff0000]C:\pathToDailyTemplate[/COLOR]"
    Const templateName = "[COLOR=#ff0000]DailyTemplate.xlsx[/COLOR]"
    Const dailyPath = "[COLOR=#ff0000]C:\pathToSave[/COLOR]"
    Const dailyName = "[COLOR=#ff0000]some string name[/COLOR]"
    Dim wb2 As Workbook, m1 As Worksheet, m2 As Worksheet, mRng As Range
    Dim a As Variant, c As Long, todayStr As String
'[COLOR=#008080]master workbook[/COLOR]
    With ThisWorkbook
        Set m1 = .Sheets("[COLOR=#ff0000]Master[/COLOR]"): Set m2 = .Sheets("[COLOR=#ff0000]Daily-Sheet[/COLOR]")
    End With
'[COLOR=#008080]open other workbook[/COLOR]
    Set wb2 = Workbooks.Open(templatePath & "\" & templateName)
'[COLOR=#008080]Report 1[/COLOR]
    m1.UsedRange.Copy wb2.Sheets("[COLOR=#ff0000]Report 1[/COLOR]").Cells(1)
'[COLOR=#008080]Report 2[/COLOR]
    Set mRng = m2.Range("B2", m2.Range("B" & Rows.Count).End(xlUp))
    c = 13                                      'column "M"
    For Each a In Array(0, 1, 3)
        mRng.Offset(, a).Copy wb2.Sheets("[COLOR=#ff0000]Report 2[/COLOR]").Cells(21, c)
        c = c + 1
    Next a
'[COLOR=#008080]create new workbook[/COLOR]
    todayStr = Format(Date, " YY-MM-YY")
    wb2.SaveAs (dailyPath & "\" & dailyName & todayStr)
    wb2.Close False
End Sub

Call with

Private Sub CommandButton1_Click()
    Call CreateTodayWorkbook
End Sub

 

[UzmaSKhan116]

Thanks a lot , so nice of you , that what exactly what i was looking for, its working fine now finally Thanks

 

[Yongle]

thanks fotr the feedback