Macro required for copy and paste including hide and unhide rows and column

[ca_ananth]

hi Team

Need your support to get the macro for the below request.

macro to copy and paste based on the values or range given in the dimension sheet. the values to be copied from source_1 to source_2 as per the details available in the dimension tab.

thanks in advance.

not able to usexl2bb, so enclosed the images of the excel.

regards,
Ananthu

 

[ca_ananth]

hi sir,

kindly let me know if your require additional information.

please find more details:

s_Tab = Source
d_Tab = Destination
s_Arrary = Source Range
d_Array = Destination Range
unhide_rows = unhide rows range
hide_rows = hide rows range
hide_cols = hide column range
unhide_cols = unhide column range

regards,
ananthu

 

[ca_ananth]

Sub MyCopyCode()

Dim r As Long
Dim sTab As String
Dim dTab As String
Dim sRng As String
Dim dRng As String

Sheets("Dimension").Activate

Application.ScreenUpdating = False

' Loop through all rows (count specified in cell A1)
For r = 8 To (Range("A1") + 7)
' Get tab and range values
sTab = Cells(r, "A")
dTab = Cells(r, "B")
sRng = Cells(r, "C")
dRng = Cells(r, "D")
' Copy data
Sheets(sTab).Range(sRng).Copy Sheets(dTab).Range(dRng)
Next r

Application.ScreenUpdating = True

MsgBox "Macro complete!"

End Sub

hi sir....
for the above, i need the hide and unhide rows and columns... request your support for the above.

 

[Alex Blakenburg]

Show me what a 2nd line on the dimensions sheet would look like.
And is count in A1 a formula or manual entry, if a formula is it counting all the lines from row 8 on, if so can't we just use the Last Row rather than rely on count ?

 

[Alex Blakenburg]

You actually seem to have a pretty good handle on this so I am not sure what you need help with.
In the below I have just added the Rows and Columns Hide/Unhide part but you will need to explain how you want that to work since it you keep copying to the same sheet below your previous copies the Column Unhide/Hides don't make sense.

Sub CopyCode()
    Dim r As Long
    Dim sTab As String
    Dim dTab As String
    Dim sRng As String
    Dim dRng As String
    
    Dim dRowsUnhide As String
    Dim dRowsHide As String
    Dim dColsUnhide As String
    Dim dColsHide As String
    
    Dim shtDim As Worksheet
    Dim shtSrc As Worksheet, shtDest As Worksheet
    
    Set shtDim = Worksheets("Dimension")
    
    
    Application.ScreenUpdating = False
    
    ' Loop through all rows (count specified in cell A1)
    For r = 8 To (Range("A1") + 7)
        ' Get tab and range values
        With shtDim
            sTab = .Cells(r, "A")
            dTab = .Cells(r, "B")
            sRng = .Cells(r, "C")
            dRng = .Cells(r, "D")
            dRowsUnhide = .Cells(r, "E")
            dRowsHide = .Cells(r, "F")
            dColsUnhide = .Cells(r, "G")
            dColsHide = .Cells(r, "H")
        End With
        
        Set shtSrc = Worksheets(sTab)
        Set shtDest = Worksheets(dTab)
        ' Copy data
        shtSrc.Range(sRng).Copy shtDest.Range(dRng)
        
        ' Assumed do this on destination sheet
        With shtDest
            ' Rows Hide/Unhide
            .Rows(dRowsUnhide).Hidden = False
            .Rows(dRowsHide).Hidden = True
            ' Cols Hide/Unhide
            ' XXX Note how do you want to do this
            '   - doing this on a row by row basis doesn't make sense unless
            '   - each row has a different destination
            '   - ie either on a different sheet
            '       or with no overlapping rows or columns
            .Columns(dColsUnhide).Hidden = False
            .Columns(dColsHide).Hidden = True
        End With
        
    Next r
    
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"

End Sub