Copy Selected range to another sheet with define range within cell

[francozzy]

Hi,

i'm newbie on vba excel

I would like to copy some range on selected sheet to another sheet, and defined the range with value from another sheet

i'll give the illustration below :

SheetA
[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]400[/TD]
[TD="align: center"]700[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]1300[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]800[/TD]
[TD="align: center"]1100[/TD]
[TD="align: center"]1400[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"]600[/TD]
[TD="align: center"]900[/TD]
[TD="align: center"]1200[/TD]
[TD="align: center"]1500[/TD]
[/TR]
</tbody>[/TABLE]

SheetB
[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]A1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]D2[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]


I want to copy selected range from SheetA to new Worksheet SheetC,
with specific value of Range that has been define at SheetB

So what i have to do ?

thank you

 

[My Aswer Is This]

Not sure why you think each line of code needs This Workbook.

But it does not hurt anything.

 

[francozzy]

Not sure why you think each line of code needs This Workbook.

But it does not hurt anything.

yup, i put code with "This Workbook" coz i have plan to cross copy between to file.

Btw how about if i trying to copy at the first column or the last column
let say today is 1 May 2018, so it's mean i have to show 30 April 2018 till 3 May 2018
and every month i put the data, and the data will put into different sheet for each month

Btw can i put my client list on the first column ?

so the case will like below :

So how solve to view based on date include the client name at table above ?

thank you

 

[My Aswer Is This]

I just try to help without asking too many questions. But why do you always want to copy yesterday today and tomorrow?

Your actually then seeing the same data more then once.
Or do you have three columns of data for every day. And you just came up with this way of doing things.
Next question would be so you only want Aprils data on Aprils sheet and May's data on May's sheet.

And if you want client name in column A of every sheet then would we always want to say sheet March column A equals Sheets Feb column A

Or could we say sheet May column A equals sheets Master Column A

What we would then need to do is put a complete date in row (1) and not just a number for the day.

And to help in more detail I would need to see how you modified my script.

 

[francozzy]

I just try to help without asking too many questions. But why do you always want to copy yesterday today and tomorrow?

Your actually then seeing the same data more then once.
Or do you have three columns of data for every day. And you just came up with this way of doing things.
Next question would be so you only want Aprils data on Aprils sheet and May's data on May's sheet.

And if you want client name in column A of every sheet then would we always want to say sheet March column A equals Sheets Feb column A

Or could we say sheet May column A equals sheets Master Column A

What we would then need to do is put a complete date in row (1) and not just a number for the day.

And to help in more detail I would need to see how you modified my script.

Ok. thank you for the question.

The reason that i need to show data from yesterday and 2 next day from XLS Master,
it's because i want to monitoring data and share it to my colleagues, without showing the fulldata from master data.

For the "client column" will change every months,
and some of client will show up again for few months until their order finish to deliver.
so, the client columns will be different for each months

i hope my explaination enough for visualizing with my requirement

Thank you

 

[My Aswer Is This]

So if you want a client column to be entered on each monthly sheet where is the script supposed to get this data from?

 

[francozzy]

So if you want a client column to be entered on each monthly sheet where is the script supposed to get this data from?

The real things is the "client column" always attach to every sheet inside master data,
as you now that every sheet represent for every months of of our progress.

that's why i want to show up "the client column" into the copying sheet

 

[My Aswer Is This]

Hello: I was able to help you with your original question. But your needs have now become more complicated then I'm able to help you with. For example I asked where are we going to get the Client member information for the new sheet and you said from the Client column. Well then I would have to now ask where is the client column.

See I would need a answer like this:

Get the client information from sheet named: "Membership" Column C
Or from the previous months sheet column A

Or something like that.

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[francozzy]

Hello: I was able to help you with your original question. But your needs have now become more complicated then I'm able to help you with. For example I asked where are we going to get the Client member information for the new sheet and you said from the Client column. Well then I would have to now ask where is the client column.

See I would need a answer like this:

Get the client information from sheet named: "Membership" Column C
Or from the previous months sheet column A

Or something like that.

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

It's okay.

i'm really grateful that you give some clues and guidance.

Currently, i have able copy from master data sheet into the new sheet using your code with little modification
as you below at below, i've manage to create two function to help me execute some of code
and i prepare put some of dynamic value as parameter that have input from value of specific cell from specific sheet

Function calculateLastRow(mySheetName As String, numRow As Long, numCol As Long)
    With ThisWorkbook.Sheets(mySheetName)
        calculateLastRow = .Cells(.Rows.Count, numCol).End(xlUp).Row
    End With
End Function

Function calculateLastCol(mySheetName As String, iCol As Long, xCol As Long)
    With ThisWorkbook.Sheets(mySheetName)
        calculateLastCol = .Cells(iCol, xCol).End(xlToLeft).Column
    End With
End Function

    Dim srcSheet As String
    Dim destSheet As String
    
    srcSheet = "Sheet1"
    destSheet = "Sheet5"
    
    Sheets(destSheet).Cells.Clear
    With Sheets(destSheet)
        .Columns.UseStandardWidth = True
            .Rows.UseStandardHeight = True
        .Rows(1).RowHeight = 15
        .Columns(1).ColumnWidth = 8.5
    End With
    
    ThisWorkbook.Sheets(srcSheet).Activate
    
    Dim NumRowCopy As Integer
    Dim NumColCopy As Integer
    
    Dim LastColumn As Long
    Dim SearchString As String
    Dim SearchRange As Range
    Dim LastRow As Long
    Dim StartCellCopy As Integer
    
    ans = Day(Date)
    StartCellCopy = 4
    SearchString = ans
        
    
    NumRowCopy = 2
    NumColCopy = 11
    
     ThisWorkbook.Sheets(1).Activate
    
    LastColumn = calculateLastCol(srcSheet, 3, Columns.Count)
    
    Set SearchRange = ThisWorkbook.Sheets(1).Range(ThisWorkbook.Sheets(1).Cells(StartCellCopy, NumColCopy + 1), _
        ThisWorkbook.Sheets(1).Cells(StartCellCopy, LastColumn)).Find(Format(SearchString, "dd"), _
        LookIn:=(xlValues), lookat:=xlWhole)
        
    If SearchRange Is Nothing _
        Then MsgBox "That day  " & SearchString & "  Cannot be found" & vbNewLine & " I will Stop the script" _
        : Exit Sub
    
    LastRow = calculateLastRow(srcSheet, ThisWorkbook.Sheets(1).Rows.Count, SearchRange.Column)   
    LastColumn = calculateLastCol(srcSheet, 1, Columns.Count)
       
    SearchRange.Offset(-1, 0).Resize(LastRow, 8).Copy
    Sheets(destSheet).Cells(3, LastColumn + NumColCopy).PasteSpecial Paste:=xlPasteFormats
    Sheets(destSheet).Cells(3, LastColumn + NumColCopy).PasteSpecial Paste:=xlPasteValues